May 15, 2021 MySQL
Defining the type of data fields in MySQL is important for optimizing your database.
MySQL supports a variety of types and can be broadly divided into three categories: numeric, date/time, and string (character) 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 synonym for INTEGER, and the keyword DEC is synonym for DECIMAL.
BIT data types hold bit field values and support MyISAM, MEMORY, InnoDB, and BDB tables.
As an extension of the SQL standard, MySQL also supports integer types TINYINT, MEDIUMINT, and BIGINT. T he following table shows the storage and extent of each integer type that is required.
| Type | Size | Range (symbol) | Range (unsigned) | Use |
|---|---|---|---|---|
| TINYINT | 1 byte | (-128，127) | (0，255) | Small integer values |
| SMALLINT | 2 bytes | (-32 768，32 767) | (0，65 535) | Large integer values |
| MEDIUMINT | 3 bytes | (-8 388 608，8 388 607) | (0，16 777 215) | Large integer values |
| INT or INTEGER | 4 bytes | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | Large integer values |
| BIGINT | 8 bytes | (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | Extreme integer values |
| 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 accuracy
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) |
Doubles
Floating point value |
| DECIMAL | For DECIMAL (M,D), if M.gt;D, it is M.2 otherwise it is D-2 | Depends on the values of M and D | Depends on the values of M and D | The small number |
The 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, which is used when specifying values that cannot be represented by the illegal MySQL.
The TIMESTAMP type has proprietary auto-update features, which are described later.
| Type |
Size
(bytes) |
Range | Format | Use |
|---|---|---|---|---|
| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | The date value |
| TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | The time value or duration |
| YEAR | 1 | 1901/2155 | YYYY | The year value |
| Datetime | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | Mix date and time values |
| TIMESTAMP | 4 |
1970-01-01 00:00:00/2038
The end time is 2147483647 seconds, Beijing time 2038-1-19 11:14:07, GMT 2038-1-19 03:14:07 |
YYYYMMDD HHMMSS | Mix date and time values, timestamps |
String types refer to CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET. T his section describes how these types work and how they are used in queries.
| Type | Size | Use |
|---|---|---|
| CHAR | 0-255 bytes | A fixed-length string |
| VARCHAR | 0-65535 bytes | Longer string |
| TINYBLOB | 0-255 bytes | A 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 | Extreme text data in binary form |
| LONGTEXT | 0-4 294 967 295 bytes | Great text data |
Tip: CHAR and VARCHAR are similar in type, but they are saved and retrieved differently. T hey also vary in length and whether tail spaces are retained. Case conversion is not performed during storage or retrieval.
The BINARY and VARBINARY classes are similar to CHAR and VARCHAR, but they contain binary strings instead of non-binary strings. T hat is, they contain byte strings instead of character strings. This means that they do not have character sets, and that they 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. T here are 4 BLOB types: TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. T hey just can accommodate values of different maximum lengths.
There are 4 TEXT types: TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. T hese correspond to 4 BLOB types and have the same maximum length and storage requirements.