MySQL Data Types
Defining the type of data fields in MySQL is crucial for optimizing your database.
MySQL supports various types, which can be broadly categorized into three groups: 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 is supported by MyISAM, MEMORY, InnoDB, and BDB tables.
As an extension to the SQL standard, MySQL also supports integer types TINYINT, MEDIUMINT, and BIGINT. The following table shows the storage requirements and ranges 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) | Very 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 M and D values | Depends on M and D values | Decimal value |
Date and Time Types
Date and time types that represent temporal values are DATETIME, DATE, TIMESTAMP, TIME, and YEAR.
Each time type has a valid value range and a "zero" value, which is used when an invalid value that MySQL cannot represent is specified.
The TIMESTAMP type has a proprietary auto-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' 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 the 2147483647th second, Beijing time 2038-1-19 11:14:07, Greenwich Mean Time January 19, 2038, 03:14:07 AM | YYYY-MM-DD hh:mm:ss | Mixed date and time value, timestamp |
String Types
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 | Binary string with a maximum of 255 characters |
| TINYTEXT | 0-255 bytes | Short text string |
| BLOB | 0-65,535 bytes | Long binary data |
| TEXT | 0-65,535 bytes | Long text data |
| MEDIUMBLOB | 0-16,777,215 bytes | Medium-length binary data |
| MEDIUMTEXT | 0-16,777,215 bytes | Medium-length text data |
| LONGBLOB | 0-4,294,967,295 bytes | Very large binary data |
| LONGTEXT | 0-4,294,967,295 bytes | Very large text data |
Note: In char(n) and varchar(n), the n represents the number of characters, not bytes. For example, CHAR(30) can store 30 characters.
CHAR and VARCHAR types are similar, but they differ in how they are stored and retrieved. They also differ in their maximum lengths and whether trailing spaces are retained. No case conversion occurs during storage or retrieval.
BINARY and VARBINARY are similar to CHAR and VARCHAR, except that they contain binary strings rather than non-binary strings. This means they do not have a 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 four BLOB types: TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. They differ in the storage ranges they can accommodate.
There are four TEXT types: TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. Corresponding to the four BLOB types, they have different maximum storage lengths and can be chosen based on the actual situation.