The types of data fields defined in MySQL are very important for optimizing your database.
MySQL supports many types, which can be roughly divided into three categories: numeric, date/time, and string (character) types.
Numeric types
MySQL supports all standard SQL numeric data types.
These types include strictly NUMERIC data types (INTEGER, SMALLINT, DECIMAL, and NUMERIC), as well as approximate NUMERIC data types (FLOAT, REAL, and DOUBLE PRECISION).
The keyword INT is a synonym for INTEGER, and the keyword DEC is a synonym for DECIMAL.
The BIT data type holds BIT field values and supports MyISAM, MEMORY, InnoDB, and BDB tables.
As an extension to the SQL standard, MySQL also supports the integer types TINYINT, MEDIUMINT, and BIGINT. The table below shows the storage and range required for each integer type.
| type | The size of the | Range (signed) | Scope (unsigned) | use |
|---|---|---|---|---|
| TINYINT | 1 byte | (128127) | (0255). | A small integer value |
| SMALLINT | 2 bytes | (-32 768，32 767) | ,65 (0 535). | Large integer value |
| MEDIUMINT | 3 bytes | (-8 388 608，8 388 607) | (0，16 777 215) | Large integer value |
| INT or an INTEGER | 4 bytes | (-2,147,483,648, 2,147,483,647) | (0，4 294 967 295) | Large integer value |
| BIGINT | 8 bytes | (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | Maximum integer value |
| FLOAT | 4 bytes | (-3.402 823 466 E+38, -1.494 369 E+38), 0, (1.494 369 E+38, 3.402 823 466 369 E+38) | 0, (1.175 494 369 E-38, 3.402 823 466 E+38) | Single-precision floating point value |
| DOUBLE | 8 bytes | (-1.797 693 134 862 391 7e +308, -2.797 858 858 391 4 e-308), 0, (2.797 858 858 391 4 e-308, 1.797 693 134 862 315 7 E+308) | 0, (1.797 693 134 862 315 7 E+308) | Double – precision floating – point value |
| DECIMAL | For DECIMAL(M,D), M+2 if M>D otherwise D+2 | Depends on the values of M and D | Depends on the values of M and D | Small numerical |
Date and time types
The DATE and TIME types representing TIME values are DATETIME, DATE, TIMESTAMP, TIME, and YEAR.
Each time type has a valid value range and a “zero” value, which is used when specifying invalid values that MySQL cannot represent.
The TIMESTAMP type has a proprietary auto-update feature, which is described later.
| type | Size (bytes) | The scope of | format | use |
|---|---|---|---|---|
| DATE | 3 | The 1000-01-01/1000-01-01 | YYYY-MM-DD | Date value |
| TIME | 3 | ‘- 838:59:59’/’ 838:59:59 ‘ | HH:MM:SS | Time value or duration |
| YEAR | 1 | 1901/2155 | YYYY | Year value |
| DATETIME | 8 | The 1000-01-01 00:00:00/1000-01-01 23:59:59 | YYYY-MM-DD HH:MM:SS | Mixes date and time values |
| TIMESTAMP | 4 | 1970-01-01 00:00:00/2038 end time is no2147483647Second, Beijing timeThe 2038-1-19 11:14:07At 03:14:07 GMT on January 19, 2038 | YYYYMMDD HHMMSS | Mix date and time values, time stamps |
String type
The string types are CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET. This section describes how these types work and how to use them in queries.
| type | The size of the | use |
|---|---|---|
| CHAR | 0-255 bytes | Fixed length string |
| VARCHAR | 0-65535 bytes | Variable length string |
| TINYBLOB | 0-255 bytes | A binary string of up to 255 characters |
| TINYTEXT | 0-255 bytes | Short text string |
| BLOB | 0-65 535 bytes | Long text data in binary form |
| TEXT | 0-65 535 bytes | Long text data |
| MEDIUMBLOB | 0-16 777 215 bytes | Medium length text data in binary form |
| MEDIUMTEXT | 0-16 777 215 bytes | Medium length text data |
| LONGBLOB | 0-4 294 967 295 bytes | Very large text data in binary form |
| LONGTEXT | 0-4 294 967 295 bytes | Maximal text data |
Note: the n in parentheses for char(n) and varchar(n) represents the number of characters, not the number of bytes. For example, char(30) can store 30 characters.
The CHAR and VARCHAR types are similar, but they are saved and retrieved differently. They also differ in terms of maximum length and whether trailing Spaces are reserved. There is no case conversion during storage or retrieval.
BINARY and VARBINARY are similar to CHAR and VARCHAR except that they contain BINARY strings rather than non-binary strings. That is, they contain byte strings instead of character strings. This means that they have no character set and sort and compare numeric values based on column value bytes.
A BLOB is a large binary object that can hold a variable amount of data. There are four types of BLOB: TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. They differ in the range of storage that can be accommodated.
There are four types of TEXT: TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. The maximum storage length of the four BLOB types varies according to the actual situation.