Analysis of MySQL data type length _ MySQL

Analysis of the length of MySQL data type analysis of the length of MySQL data type

MySQL has several data types, including CHAR (Length), VARCHAR (Length), TINYINT (Length), SMALLINT (Length), and MEDIUMINT (Length), INT (Length), BIGINT (Length), FLOAT (Length, Decimals), DOUBLE (Length, Decimals), and DECIMAL (Length, Decimals ).

However, the length of these data types is not always the data size. Specifically:
(1) the length of CHAR and VARCAHR refers to the length of the character. for example, CHAR [3] can only contain the string "123". if data is inserted "1234", it is intercepted from a high position, to "123 ". The same applies to VARCAHR.

(2) the length of TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT is not related to the data size! Length indicates the display width. for example:

[Cpp]View plaincopy

1. Mysql> create table test (id int (3) zerofill );
2. Query OK, 0 rows affected (0.09 sec)
4. Mysql> insert into test (id) values (1), (1234 );
5. Query OK, 2 rows affected (0.06 sec)
6. Records: 2 Duplicates: 0 Warnings: 0
8. Mysql> select * from test;
9. + ------ +
10. | Id |
11. + ------ +
12. | 001 |
13. | 1, 1234 |
14. + ------ +
15. 2 rows in set (0.00 sec)

It can be seen that the display width of the id is 3, and the left side of the insufficient value is filled with 0. if the data length exceeds, it is output as is. If there is no zerofill, no display width is displayed, and no leading zero is displayed.

(3) FLOAT, DOUBLE, and DECIMAL lengths refer to all digits (including digits after the DECIMAL point). For example, DECIMAL () indicates four digits, and one digit after the DECIMAL point, if 1234 is inserted, the queried data is 999.9. The procedure is as follows:

[Cpp]View plaincopy

1. Mysql> alter table test add realnum decimal (4, 1 );
2. Query OK, 2 rows affected (0.03 sec)
3. Records: 2 Duplicates: 0 Warnings: 0
5. Mysql> insert into test (id, realnum) values (2,1234 );
6. Query OK, 1 row affected, 1 warning (0.05 sec)
8. Mysql> select * from test;
9. + ------ + --------- +
10. | Id | realnum |
11. + ------ + --------- +
12. | 001 | NULL |
13. | 1, 1234 | NULL |
14. |002 | 999.9 |
15. + ------ + --------- +
16. 3 rows in set (0.02 sec)

Appendix common MySQL data types)

Type	Big and Small	Description
CAHR (Length)	Length bytes	Fixed length field, with a length of 0 ~ 255 characters
VARCHAR (Length)	String length + 1 byte or String length + 2 bytes	Variable Length field, with a length of 0 ~ 65 535 characters
TINYTEXT	String length + 1 byte	String, up to 255 characters
TEXT	String length + 2 bytes	String, up to 65 535 characters
MEDIUMINT	String length + 3 bytes	String, up to 16 777 215 characters
LONGTEXT	String length + 4 bytes	String, up to 4 294 967 295 characters
TINYINT (Length)	1 byte	Range:-128 ~ 127, or 0 ~ 255 (unsigned)
SMALLINT (Length)	2 bytes	Range:-32 768 ~ 32 767, or 0 ~ 65 535 (unsigned)
MEDIUMINT (Length)	3 bytes	Range:-8 388 608 ~ 8 388 607, or 0 ~ 16 777 215 (unsigned)
INT (Length)	4 bytes	Value range:-2 147 483 648 ~ 2 147 483 647, or 0 ~ 4 294 967 295 (unsigned)
BIGINT (Length)	8 bytes	Range:-9 223 372 036 854 775 808 ~ 9 223 372 036 854 775 807, or 0 ~ 18 446 744 073 709 551 615 (unsigned)
FLOAT (Length, Decimals)	4 bytes	A smaller number with a floating decimal point
DOUBLE (Length, Decimals)	8 bytes	A large number with a floating decimal point
DECIMAL (Length, Decimals)	Length + 1 byte or Length + 2 byte	The DOUBLE value stored as a string. a fixed decimal point is allowed.
DATE	3 bytes	YYYY-MM-DD format
DATETIME	8 bytes	YYYY-MM-DD in HH: MM: SS format
TIMESTAMP	4 bytes	The format is YYYYMMDDHHMMSS. the acceptable range is terminated on January 1, 2037.
TIME	3 bytes	HH: MM: SS format
ENUM	1 or 2 bytes	Abbreviation of Enumeration (Enumeration), which means that each column can have one of multiple possible values
SET	1, 2, 3, 4, or 8 bytes	Like ENUM, each column can have multiple possible values.