1. Data types in MySQL
type  Type example 

Integer type  TINYINT, SMALLINT, MEDIUMINT, int (or INTEGER), BIGINT 
Floating point type  FLOAT,DOUBLE 
Fixed point number type  DECIMAL 
Bit type  BIT 
Date time type  YEAR,TIME,DATE,DATETIME,TIMESTAMP 
Text string type  CHAR,VARCHAR,TINYTEXT,TEXT,MEDIUMTEXT,LONGTEXT 
Enumeration type  ENUM 
Collection type  SET 
Binary string type  BINARY,VARBINARY,TINYBLOB,BLOB,MEDIUMBLOB,LONGBLOB 
JSON type  JSON object, JSON array 
Spatial data type  Single value type: GEOMETRY, POINT, LINESTRING, POLYGON; Collection type: MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION 
Properties of common data types are as follows:
MySQL keyword  meaning 

NULL  Data columns can contain NULL values 
NOT NULL  Data columns are not allowed to contain NULL values 
DEFAULT  Default value 
PRIMARY KEY  Primary key 
AUTO_INCREMENT  Auto increment for integer type 
UNSIGNED  Unsigned 
CHARACTER SET name  Specify a character set 
2. Integer type
2.1 type introduction
There are five types of integers, including TINYINT, SMALLINT, MEDIUMINT, INT (INTEGER) and BIGINT.
Their differences are shown in the following table:
Integer type  byte  Value range of signed number  Unsigned number value range 

TINYINT  1  128~127  0~255 
SMALLINT  2  32768~32767  0~65535 
MEDIUMINT  3  8388608~8388607  0~16777215 
INT,INTEGER  4  2147483648~2147483647  0~4294967295 
BIGINT  8  9223372036854775808~9223372036854775807  0~18446744073709551615 
2.2 optional attributes
There are three optional attributes of integer type:
2.2.1 M

M: Indicates the display width. The value range of M is (0, 255). For example, int(5): when the data width is less than 5 bits, you need to fill the width with characters in front of the number. This function needs to be used with "ZEROFILL", which means to fill the width with "0", otherwise the specified display width is invalid.

If the display width is set, the inserted data width exceeds the display width limit. Will it be truncated or failed to insert?

Answer: it will not have any impact on the inserted data. It is still saved according to the actual width of the type, that is, the display width is independent of the range of values that the type can store. Starting from MySQL 8.0.17, the display width attribute is not recommended for integer data types.

For integer data types, you can specify the required display width when defining the table structure. If not specified, the system specifies the default width value for each type.
give an example:
CREATE TABLE test_int1 ( x TINYINT, y SMALLINT, z MEDIUMINT, m INT, n BIGINT );
View the table structure (the explicit range in MySQL 5.7 is as follows, and there is no explicit range in MySQL 8)
mysql> desc test_int1; +++++++  Field  Type  Null  Key  Default  Extra  +++++++  x  tinyint(4)  YES   NULL    y  smallint(6)  YES   NULL    z  mediumint(9)  YES   NULL    m  int(11)  YES   NULL    n  bigint(20)  YES   NULL   +++++++ 5 rows in set (0.00 sec)
The value range of signed and unsigned numbers of TINYINT is  128 ~ 127 and 0 ~ 255 respectively. Since the negative sign accounts for one digit, the default display width of TINYINT is 4. Similarly, the default display width of other integer types is the same as the width of the minimum value of the signed number.
give an example:
CREATE TABLE test_int2( f1 INT, f2 INT(5), f3 INT(5) ZEROFILL ) DESC test_int2; INSERT INTO test_int2(f1,f2,f3) VALUES(1,123,123); INSERT INTO test_int2(f1,f2) VALUES(123456,123456); INSERT INTO test_int2(f1,f2,f3) VALUES(123456,123456,123456);
mysql> SELECT * FROM test_int2; ++++  f1  f2  f3  ++++  1  123  00123   123456  123456  NULL   123456  123456  123456  ++++ 3 rows in set (0.00 sec)
2.2.2 UNSIGNED

UNSIGNED: UNSIGNED type (non negative). All integer types have an optional attribute, UNSIGNED. The minimum value of UNSIGNED integer type is 0. Therefore, if you need to save non negative integer values in MySQL database, you can set the integer type to UNSIGNED type.

The default display width of int type is int(11), and the default display width of unsigned int type is int(10).
CREATE TABLE test_int3( f1 INT UNSIGNED ); mysql> desc test_int3; +++++++  Field  Type  Null  Key  Default  Extra  +++++++  f1  int(10) unsigned  YES   NULL   +++++++ 1 row in set (0.00 sec)
2.2.3 ZEROFILL

ZEROFILL: 0 filling, (if a column is ZEROFILL, MySQL will automatically add the UNSIGNED attribute to the current column). If ZEROFILL is specified, it only indicates that there are not enough M bits, fill it with 0 on the left. If it exceeds M bits, it can be filled as long as it does not exceed the data storage range.

Originally, in int(M), the value of M has nothing to do with the storage space occupied by int(M). int(3), int(4) and int(8) all occupy 4 bytes of storage space on the disk. In other words, int(M) must be used with UNSIGNED ZEROFILL to make sense. If the integer value exceeds m bits, it is stored according to the actual number of bits. It just doesn't need to be filled with the character 0.
2.3 applicable scenarios
 TINYINT: generally used to enumerate data, such as scenes where the value range set by the system is very small and fixed.
 SMALLINT: it can be used for a small range of statistics, such as counting the inventory quantity of fixed assets in factories.
 MEDIUMINT: used for the calculation of larger integers, such as the daily passenger flow of the station.
 INT, INTEGER: the value range is large enough. Generally, the overrun problem is not considered and is used most. Such as item number.
 BIGINT: only when you deal with very large integers. For example, the trading volume of double 11, the number of clicks on large portal websites, the position of derivative products of securities companies, etc.
2.4 how to choose?

When evaluating which integer type to use, you need to consider the balance between storage space and reliability: on the one hand, using integer types with less bytes can save storage space; On the other hand, in order to save storage space, if the value range of integer type used is too small, once it exceeds the value range, it may cause system errors and affect reliability.

For example, the data type used for the item number is INT. The reason is that there are many kinds of goods circulating in customers' stores. Moreover, old goods are taken off the shelves and new goods are put on the shelves every day. In this way, we continue to iterate and accumulate over time.

If SMALLINT type is used, although the number of bytes occupied is less than INT type integers, there is no guarantee that the data will not exceed the range 65535. On the contrary, using INT can ensure that there is a large enough value range without worrying about the reliability of data exceeding the range.

You should note that in practice, the cost of system failure far exceeds the cost of adding storage space for several fields. Therefore, I suggest you first ensure that the data will not exceed the value range, and then consider how to save storage space.
3. Floating point type
3.1 type introduction
Floating point number and fixedpoint number types can handle decimals. You can treat integers as a special case of decimals. Therefore, the usage scenarios of floatingpoint numbers and fixedpoint numbers are much more than integers. The types of floating point numbers supported by MySQL are FLOAT, DOUBLE and REAL.
 FLOAT represents single precision floatingpoint number;
 DOUBLE indicates DOUBLE precision floatingpoint number;
 DOUBLE is the default. If you set the SQL mode to enable "REAL_AS_FLOAT", MySQL considers REAL as FLOAT. If you want to enable "REAL_AS_FLOAT", you can implement it through the following SQL statement:
SET sql_mode = "REAL_AS_FLOAT";
 Question 1: what is the difference between FLOAT and DOUBLE data types?
The number of bytes occupied by FLOAT is small and the value range is small; DOUBLE occupies a large number of bytes and has a large value range.
 Question 2: why is the unsigned number value range of floatingpoint number type only equivalent to half of the signed number value range, that is, only equivalent to the part where the signed number value range is greater than or equal to zero?
MySQL stores floatingpoint numbers in the following formats: symbol (S), mantissa (M), and order code (E). Therefore, MySQL's floatingpoint number stores the part representing the symbol whether there is a symbol or not. Therefore, the socalled unsigned number value range is actually the part where the signed number value range is greater than or equal to zero.
3.2 data accuracy description
For floatingpoint type, 4 bytes are used for single precision value and 8 bytes are used for double precision value in MySQL.
 MySQL allows the use of nonstandard syntax (which may not be supported by other databases, so it is best not to use if data migration is involved): FLOAT(M,D) or DOUBLE(M,D). Here, M is called precision and D is called scale. In (M,D), M = integer + decimal place, d = decimal place. D<=M<=255，0<=D<=30.
For example, a column defined as FLOAT(5,2) can be displayed as 999.99999.99. If it exceeds this range, an error will be reported.
 When FLOAT and DOUBLE types are not specified (M,D), they will be displayed according to the actual accuracy (determined by the actual hardware and operating system) by default.
 Note: for floatingpoint type, UNSIGNED can also be added, but the data range will not be changed. For example, FLOAT(3,2) UNSIGNED can only represent the range of 09.99.
 Whether the precision (M,D) is explicitly set or not, the MySQL processing scheme here is as follows:
 If the integer part exceeds the range during storage, MySQL will report an error and such a value is not allowed to be stored
 If the decimal point is out of range during storage, it can be divided into the following situations:
 If the integer part does not exceed the range after rounding, only a warning will be given, but it can be rounded successfully and saved after deleting the extra decimal places. For example, insert 999.009 in the FLOAT(5,2) column, and the approximate result is 999.01.
 If the integer part exceeds the range after rounding, MySQL will report an error and refuse to process. If 999.995 and 999.995 are inserted into the FLOAT(5,2) column, an error will be reported.
 Since MySQL 8.0.17, the usage of FLOAT(M,D) and DOUBLE(M,D) has been clearly not recommended in the official documents and may be removed in the future. In addition, UNSIGNED for floatingpoint FLOAT and DOUBLE is not recommended and may be removed in the future.
give an example
CREATE TABLE test_double1( f1 FLOAT, f2 FLOAT(5,2), f3 DOUBLE, f4 DOUBLE(5,2) ); DESC test_double1; INSERT INTO test_double1 VALUES(123.456,123.456,123.4567,123.45); #Out of range value for column 'f2' at row 1 INSERT INTO test_double1 VALUES(123.456,1234.456,123.4567,123.45); SELECT * FROM test_double1;
3.3 precision error description
One drawback of floatingpoint type is inaccuracy. Let me focus on explaining why MySQL's floatingpoint numbers are not accurate enough. For example, we design a table with the field f1, and the insertion values are 0.47, 0.44 and 0.19 respectively. The expected operation result is: 0.47 + 0.44 + 0.19 = 1.1. Query after using sum:
CREATE TABLE test_double2( f1 DOUBLE ); INSERT INTO test_double2 VALUES(0.47),(0.44),(0.19);
mysql> SELECT SUM(f1) > FROM test_double2; ++  SUM(f1)  ++  1.0999999999999999  ++ 1 row in set (0.00 sec)
mysql> SELECT SUM(f1) = 1.1,1.1 = 1.1 > FROM test_double2; +++  SUM(f1) = 1.1  1.1 = 1.1  +++  0  1  +++ 1 row in set (0.00 sec)
① The query result is 1.099999999999999. Did you see? Although the error is small, there is an error. You can also try to change the data type to FLOAT, and then run the summation query to get 1.099999940395355. Obviously, the error is greater.
② So why is there such an error? The problem lies in the way MySQL stores floatingpoint data.
③ MySQL uses 4 bytes to store FLOAT type data and 8 bytes to store DOUBLE type data. No matter which, it is stored in binary mode. For example, 9.625, expressed in binary, is 1001.101, or expressed as 1.001101 × 2^3. If the mantissa is not 0 or 5 (such as 9.624), you can't express it accurately in a binary number. Further, it has to be rounded within the allowable range.
④ In programming, if floatingpoint numbers are used, we should pay special attention to the error problem, because floatingpoint numbers are inaccurate, so we should avoid using "=" to judge whether two numbers are equal. At the same time, in some projects that require high accuracy, do not use floatingpoint numbers, otherwise it will lead to wrong results and even irreparable losses. So, does MySQL have precise data types? Of course, this is the fixedpoint number type: DECIMAL.
4. Fixed point number type
4.1 type introduction
① There is only one type of fixedpoint number in MySQL: DECIMAL.
data type  Number of bytes  meaning 

DECIMAL(M,D),DEC,NUMERIC  M+2 bytes  The effective range is determined by M and D 
Use DECIMAL(M,D) to represent highprecision decimals. Where m is called precision and D is called scale. 0<=M<=65，0<=D<=30，D<M. For example, defining the type of DECIMAL (5,2) indicates that the value range of this column is 999.99 ~ 999.99.
② The maximum value range of DECIMAL(M,D) is the same as that of DOUBLE type, but the valid data range is determined by M and D.
The storage space of DECIMAL is not fixed. It is determined by the precision value M. The total storage space occupied is M+2 bytes. In other words, in some scenarios with low accuracy requirements, the range of values expressed by floatingpoint numbers can be larger than fixedpoint numbers occupying the same byte length.
③ Fixed point numbers are stored in MySQL in the form of strings, which determines that they must be accurate.
④ When DECIMAL type does not specify precision and scale, it defaults to DECIMAL(10,0). When the precision of the data exceeds the precision range of the fixedpoint number type, MySQL will also perform rounding processing.
⑤ Floating point number vs fixed point number
 The advantage of floatingpoint number over fixedpoint number is that when the length is certain, the floatingpoint type has a wide range of values, but it is not accurate and applicable
It is suitable for scientific computing scenarios that need a wide range of values and can tolerate small errors (such as computational chemistry, molecular modeling, fluid dynamics
Mechanics, etc.)  The value range of fixedpoint number type is relatively small, but it is accurate and has no error. It is suitable for scenes requiring high accuracy (such as
And amount calculation scenario)
give an example
CREATE TABLE test_decimal1( f1 DECIMAL, f2 DECIMAL(5,2) ); DESC test_decimal1; INSERT INTO test_decimal1(f1,f2) VALUES(123.123,123.456); #Out of range value for column 'f2' at row 1 INSERT INTO test_decimal1(f2) VALUES(1234.34);
mysql> SELECT * FROM test_decimal1; +++  f1  f2  +++  123  123.46  +++ 1 row in set (0.00 sec)
give an example
 We run the following statement to test_ The data type of field "f1" in double2 table is modified to DECIMAL(5,2):
ALTER TABLE test_double2 MODIFY f1 DECIMAL(5,2);
 Then, we run the summation statement again:
mysql> SELECT SUM(f1) > FROM test_double2; ++  SUM(f1)  ++  1.10  ++ 1 row in set (0.00 sec)
mysql> SELECT SUM(f1) = 1.1 > FROM test_double2; ++  SUM(f1) = 1.1  ++  1  ++ 1 row in set (0.00 sec)
4.2 experience in development
"Due to the accuracy of DECIMAL data type, in our project, except for a few integer types (such as commodity number), other values are DECIMAL. The reason is that the retail industry of this project requires accuracy and can't be worse for a penny.
” from a project manager
5. BIT type: BIT
BIT type stores binary values, similar to 010110.
Binary string type  length  Length range  Occupied space 

BIT(M)  M  1 <= M <= 64  About (M + 7)/8 bytes 
BIT type. If (M) is not specified, the default is 1 BIT. This 1BIT indicates that only 1BIT binary value can be stored. Here (M) is the number of bits representing binary. The minimum value of bits is 1 and the maximum value is 64.
CREATE TABLE test_bit1( f1 BIT, f2 BIT(5), f3 BIT(64) ); INSERT INTO test_bit1(f1) VALUES(1); #Data too long for column 'f1' at row 1 INSERT INTO test_bit1(f1) VALUES(2); INSERT INTO test_bit1(f2) VALUES(23);
Note: when inserting data into a field of BIT type, be sure to ensure that the inserted data is within the range supported by BIT type.
When using the SELECT command to query bit fields, you can use the BIN() or HEX() functions to read.
mysql> SELECT * FROM test_bit1; ++++  f1  f2  f3  ++++  0x01  NULL  NULL   NULL  0x17  NULL  ++++ 2 rows in set (0.00 sec)
mysql> SELECT BIN(f2),HEX(f2) > FROM test_bit1; +++  BIN(f2)  HEX(f2)  +++  NULL  NULL   10111  17  +++ 2 rows in set (0.00 sec)
mysql> SELECT f2 + 0 > FROM test_bit1; ++  f2 + 0  ++  NULL   23  ++ 2 rows in set (0.00 sec)
It can be seen that when using b+0 to query data, you can directly query the value of the stored decimal data.
6. Date and time type