7.2 integer

ZEROFILL -- when it is less than the specified number of digits, fill it with 0

CREATE TABLE mytal(
    f1,INT(5),
	f2,INT(5) ZEROFILL
)
#ZEROFILL is an unsigned number with the constraint unsigned by default
#8.0. Display width is not recommended after 17

In actual development, the cost of system failure is much higher than that of adding several field storage space.

7.3 floating point numbers

• The difference between FOLAT and DOUBLE

  FLOAT occupies less bytes (4 bytes) and low precision. DOUBLE occupies large bytes (8 bytes) and high precision.

• FLOAT (m.d) m is the number of digits and d is the number of decimal digits. For example, FOLAT(5,2) means [- 999.99999.99]

• If the decimal places exceed the range, they are rounded.

  CREATE TABLE mytal(f1 FLOAT(5,2))
  INSERT INTO mytal VALUES(123.456)
  #The result was added as 123.46
  

• If the integer bit exceeds the range, an error will be reported.

  CREATE TABLE mytal(f1 FLOAT(5,2))
  INSERT INTO mytal VALUES(1234.45)
  #Adding failed with an error.
  

• An error will also be reported if the integer digit exceeds the range due to the rounding of decimal digits.

  CREATE TABLE mytal(f1 FLOAT(5,2))
  INSERT INTO mytal VALUES(999.999)
  #Adding failed with an error.
  
  

• Accuracy situation

  CREATE TABLE mytal(f1 DOUBLE)
  INSERT INTO mytal VALUES(0.19),(0.47),(0.44)
  SELECT SUM(f1) FROM mytal
  #The result is 1.0999
  SELECT SUM(f1)=1.1
  FROM mytal;
  #The result is 0, indicating inequality.
  

7.4 fixed point number DECLMAL

• Floating point precision is not accurate. Use the fixed-point number DECIMAL(M,N), occupying byte M+2. Default precision (10,0)

• Under the same byte, there is no DOUBLE to store more content

• String storage is used in the underlying

• Accuracy problem

  CREATE TABLE mytal(f1 DECIMAL)
  INSERT INTO mytal VALUES(0.19),(0.47),(0.44)
  SELECT SUM(f1) FROM mytal
  #The result is 1.1
  
  SELECT SUM(f1)=1.1
  FROM mytal;
  #The result is 1, indicating equality.
  

• If the data needs to be accurate, use the fixed-point number. Floating point numbers are used when the accuracy requirement is not so high

7.5 bit type (understand)

CREATE TABLE mytal(
    f1 BIT,
    f2 BIT(5),
    f3 BIT(64)
)

7.6 date and time type

• YEAR uses four digit storage (4)

• The range of YEAR is 1970-2155

• In the case of two

  • 0-69 refers to 2000-2069

  • 70-99 refers to 1970-1999

  CREATE TABLE mytal(
      f1 YEAR,
      f2 YEAR(2)
  )
  INSERT INTO mytal('1999','70')
  #The results were 1999 and 1970
  INSERT INTO mytal('1999','69')
  #The results were 1999 and 2069  
  

• The DATE format 'YYYY-MM-DD' or 'YY-MM-DD' uses two bits. The rules are the same as above.

• DATETIME and DATESTAMP

  • DATETIME requires eight bytes. Format: YYYY-MM-DD HH:MM:SS or YY-MM-DD HH:MM:SS. The rules are the same as above.
  • TIMESTAMP only needs 4 bytes, but the time range can only be from 1970-01-01 00:00:01 to 2038-01-19 03:14:07
  • Adaptive TIMESTAMP involving operations between time zones?
  • TIMESTAMP is stored in milliseconds. When reading, the corresponding milliseconds will be added according to the current time zone (storage: East 8 zone, reading: East 9 zone. Result + 1h)

• DATETIME is used most in development.

7.7 string

• CHAR and VARCHAR
  • CHAR (m) occupies m bytes. If it is less than m, it will be filled to M. by default, it is only 1 byte
  • VARCHAR (M) occupies M+1 bytes. VARCHAR must be defined with width.

Where is CHAR or VARCHAR better

type	characteristic	Spatially	Time	Applicable scenario
CHAR(M)	Fixed length	Waste of storage space	efficient	Small storage, high speed requirements
VARCHAR(M)	Variable length	Save storage space	Low efficiency	Non CHAR cases

Experience in development:

Text text type can store relatively large text segments, and the search speed is slightly slow. Therefore, if it is not a particularly large content, it is recommended to use CHAR and VARCHAR instead. Also, the text type does not need to add the default value, and it is useless to add it. Moreover, the deletion of text and blob data can easily lead to "holes" and more file fragments. Therefore, it is not recommended to include text type fields in frequently used tables. It is recommended to separate them and use a single table.

7.8 enumeration and SET

CREATE TABLE mytal(
	season 			
    ENUM('spring','summer','autumn','winter','UNKROW')
)
#Only one of the enumerations can be added. An error will be reported as follows
INSERT INTO mytal VALUES('spring','summer')
#In addition, you can add NULL or use indexes.
INSERT INTO mytal VALUES(1)
INSERT INTO mytal VALUES('2')
INSERT INTO mytal VALUES(NULL)

CREATE TABLE mytal(
	season 				
    SET('spring','summer','autumn','winter','UNKROW')
)
#You can fill in elements in multiple sets
INSERT INTO mytal VALUES('spring','summer')

7.9 summary and selection suggestions

When defining a data type, if it is determined to be an integer, use INT; If it is a decimal, the fixed-point number type DECIMAL(M,D) must be used; If it is a date and time, use DATETIME.

The advantage of this is to first ensure that your system does not make mistakes because of data type definition. However, everything has two sides. Good reliability does not mean efficiency. For example, although TEXT is easy to use, it is not as efficient as CHAR(M) and VARCHAR(M).

For string selection, it is recommended to refer to the following specifications in Alibaba's Java Development Manual:

MySQL database in Alibaba's Java Development Manual:

• If any field is non negative, it must be UNSIGNED
• [mandatory] DECIMAL type is DECIMAL, and FLOAT and DOUBLE are prohibited.
  • Note: both FLOAT and DOUBLE have the problem of accuracy loss during storage. It is likely that incorrect results will be obtained when comparing values. If the stored data range exceeds the DECIMAL range, it is recommended to split the data into integers and decimals and store them separately.
• [mandatory] if the stored strings are almost equal in length, the CHAR fixed length string type is used.
• [mandatory] VARCHAR is a variable length string, with no pre allocated storage space and a length of no more than 5000. If the storage length is greater than this value, define the field type as TEXT, separate a table and correspond with the primary key to avoid affecting the index efficiency of other fields.