English | 简体中文 | 繁體中文 | Русский язык | Français | Español | Português | Deutsch | 日本語 | 한국어 | Italiano | بالعربية
Defining the data field types in MySQL is very important for optimizing your database.
MySQL supports a variety of types, which can be roughly divided into three categories: numerical, date/Time and string (character) types.
MySQL supports all standard SQL numerical data types.
These types include strictly numerical data types (INTEGER, SMALLINT, DECIMAL, and NUMERIC), as well as approximate numerical 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 saves bit field values and supports MyISAM, MEMORY, InnoDB, and BDB tables.
As an extension of the SQL standard, MySQL also supports integer types TINYINT, MEDIUMINT, and BIGINT. The following table shows the storage and range required for each integer type.
| Type | Size | Range (signed) | Range (unsigned) | Purpose |
|---|---|---|---|---|
| TINYINT | 1 byte | (-128,127) | (0,255) | Small integer value |
| SMALLINT | 2 bytes | (-32 768,32 767) | (0,65 535) | Large integer value |
| MEDIUMINT | 3 bytes | (-8 388 608,8 388 607) | (0,16 777 215) | Large integer value |
| INT or 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) | Extremely large integer value |
| FLOAT | 4 bytes | (-3.402 823 466 E+38,-1.175 494 351 E-38) 0, (1.175 494 351 E-38,3.402 823 466 351 E+38) | 0, (1.175 494 351 E-38,3.402 823 466 E+38) | Single precision Floating-point numbers |
| DOUBLE | 8 bytes | (-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308) 0, (2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 0, (2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | Double precision Floating-point numbers |
| DECIMAL | For DECIMAL(M,D), if M>D, it is M+2Otherwise, it is D+2 | Depends on the value of M and D | Depends on the value of M and D | Decimal numbers |
Date and time types that represent time values are DATETIME, DATE, TIMESTAMP, TIME, and YEAR.
Each time type has a valid value range and a "zero" value. The "zero" value is used when specifying an illegal value that MySQL cannot represent.
TIMESTAMP type has a dedicated automatic update feature, which will be described later.
| Type | Size (bytes) | Range | Format | Purpose |
|---|---|---|---|---|
| DATE | 3 | 1000-01-01/9999-12-31 | 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 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | Mixed date and time values |
| TIMESTAMP | 4 | 1970-01-01 00:00:00/2038 The end time is the 2147483647 Second, Beijing Time 2038-1-19 11:14:07, Greenwich Mean Time 2038Year1Month19Day at midnight 03:14:07 | YYYYMMDD HHMMSS | Mixed date and time values, timestamp |
String types include CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET. This section describes how these types work and how to use them in queries.
| Type | Size | Purpose |
|---|---|---|
| CHAR | 0-255 bytes | Fixed-length string |
| VARCHAR | 0-65535 bytes | Variable-length string |
| TINYBLOB | 0-255 bytes | Not exceeding 255 Binary string of a character |
| TINYTEXT | 0-255 bytes | Short text string |
| BLOB | 0-65 535 bytes | Binary form of long text data |
| TEXT | 0-65 535 bytes | Long text data |
| MEDIUMBLOB | 0-16 777 215 bytes | Binary form of medium-length text data |
| MEDIUMTEXT | 0-16 777 215 bytes | Medium-length text data |
| LONGBLOB | 0-4 294 967 295 bytes | Extremely large text data in binary form |
| LONGTEXT | 0-4 294 967 295 bytes | Extremely large text data |
Note: char(n) and varchar(n) The n in the brackets represents the number of characters, not the number of bytes, for example, CHAR(30) can be stored 30 characters.
CHAR and VARCHAR types are similar, but they save and retrieve data in different ways. They also differ in terms of maximum length and whether trailing spaces are preserved. No case conversion is performed during storage or retrieval.
BINARY and VARBINARY are similar to CHAR and VARCHAR, but they contain binary strings instead of non-binary strings. That is, they contain byte strings instead of character strings. This means they do not have a character set, and sorting and comparison are based on the numerical value of the byte values of the column values.
BLOB is a binary large object that can hold a variable amount of data. There are 4 There are different types of BLOB: TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. They differ in the storage range they can accommodate.
There are 4 There are TEXT types: TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. Corresponding to this 4 There are different types of BLOB, with different maximum lengths that can be stored, and you can choose according to the actual situation.