MySQL Data Types
In MySQL, defining the type of data fields is very important for the optimization of your database.
MySQL supports multiple 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 strict 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 stores bit-field values and supports MyISAM, MEMORY, InnoDB, and BDB tables.
As an extension of 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 | Size | Range (signed) | Range (unsigned) | Usage |
|---|---|---|---|---|
| TINYINT | 1 Bytes | (-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 value |
| 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 value |
| DECIMAL | For DECIMAL(M,D), if M>D, it is M+2; otherwise, D+2 | Depends on the values of M and D | Depends on the values of M and D | Decimal value |
Date and Time Types
The date and time types for representing time values are DATETIME, DATE, TIMESTAMP, TIME, and YEAR.
Each time type has a valid range of values and a "zero" value, which is used when an invalid value that MySQL cannot represent is specified.
The TIMESTAMP type has a proprietary automatic update feature, which will be described later.
| Type | Size ( bytes) |
Range | Format | Usage |
|---|---|---|---|---|
| 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' to '9999-12-31 23:59:59' | YYYY-MM-DD hh:mm:ss | Mixed date and time value |
| TIMESTAMP | 4 |
'1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC The end time is the2147483647seconds, Beijing time2038-1-19 11:14:07, Greenwich Mean Time 03:14:07 on January 19, 2038 |
YYYY-MM-DD hh:mm:ss | Mixed date and time value, timestamp |
String Types
String types refer to 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 | Usage |
|---|---|---|
| CHAR | 0-255 bytes | Fixed-length string |
| VARCHAR | 0-65535 bytes | Variable-length string |
| TINYBLOB | 0-255 bytes | Binary string of no more than 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 | Very large text data |
Note: In char(n) and varchar(n), the n in parentheses represents the number of characters, not the number of bytes. For example, CHAR(30) can store 30 characters.
CHAR and VARCHAR are similar, but they differ in how they are stored and retrieved. They also differ in maximum length and whether trailing spaces are retained. No case conversion is performed 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 rather than character strings. This means they have no character set, and sorting and comparison are based on the numeric values of the bytes in the column values.
BLOB is a binary large object that can hold a variable amount of data. There are 4 BLOB types: TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. They differ in the storage range they can accommodate.
There are 4 TEXT types: TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. Corresponding to these 4 BLOB types, the maximum storable length differs; you can choose according to the actual situation.
Enumeration and Set Types
- ENUM: Enumeration type, used to store a single value, where you can select one from a predefined set.
- SET: Set type, used to store multiple values, where you can select multiple from a predefined set.
Spatial Data Types
GEOMETRY, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION: Used to store spatial data (geographic information, geometric shapes, etc.).
Other Extensions