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 fixed-point number types can handle decimals. You can treat integers as a special case of decimals. Therefore, the usage scenarios of floating-point numbers and fixed-point numbers are much more than integers. The types of floating point numbers supported by MySQL are FLOAT, DOUBLE and REAL.

• FLOAT represents single precision floating-point number;
• DOUBLE indicates DOUBLE precision floating-point 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 floating-point 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 floating-point numbers in the following formats: symbol (S), mantissa (M), and order code (E). Therefore, MySQL's floating-point number stores the part representing the symbol whether there is a symbol or not. Therefore, the so-called 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 floating-point 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 non-standard 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.99-999.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 floating-point 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 0-9.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 floating-point 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 floating-point type is inaccuracy. Let me focus on explaining why MySQL's floating-point 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 floating-point 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 floating-point numbers are used, we should pay special attention to the error problem, because floating-point 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 floating-point numbers, otherwise it will lead to wrong results and even irreparable losses. So, does MySQL have precise data types? Of course, this is the fixed-point number type: DECIMAL.

4. Fixed point number type

4.1 type introduction

① There is only one type of fixed-point 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 high-precision 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 floating-point numbers can be larger than fixed-point 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 fixed-point number type, MySQL will also perform rounding processing.

⑤ Floating point number vs fixed point number

• The advantage of floating-point number over fixed-point number is that when the length is certain, the floating-point 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 fixed-point 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 1-BIT indicates that only 1-BIT 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