MySQL supports a variety of data types, which can be roughly divided into numeric values, date/time, and character types.
Numeric type
MySQL supports all standard SQL numeric data types, including strict numeric data types (INTEGER, SMALLINT, Decimal, and numeric), and approximate numeric data types (FLOAT, real, and double PRECISION). In addition to these, tinyint, mediumint and bigint are supported. The occupied bytes and ranges of these types are as follows.
type |
size |
Range (signed) |
Range (unsigned) |
Use |
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 233 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.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+3 08) |
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, is m+2 otherwise d+2 |
Values that depend on M and D |
Values that depend on M and D |
Decimal value |
Date and Time type
The
includes datetime, DATE, TIMESTAMP, Time, and year, each with a valid range of values and a value of "0", using the "0" value when specifying an illegal MySQL value that cannot be represented.
type |
size (bytes) |
Range |
format |
Use |
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 |
Blend date and time values |
TIMESTAMP |
8 |
1970-01-01 00:00:00/2037, sometime |
YYYYMMDD HHMMSS |
Mixed date and time values, timestamp |
Character type
The
String type refers to Char, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, enum, and set.
type |
size |
purpose |
CHAR |
0-255-byte |
fixed-length string |
VARCHAR |
0-255-byte |
variable-length string |
tinyblob |
0-255-byte |
no more than 255-character binary string |
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 |
binary Form medium-length text data |
mediumtext |
0-16 777 215 bytes |
medium-length text data |
logngblob |
0-4 294 967 295 bytes |
binary form of large text data |
longtext |
0-4 294 967 295 bytes |
Extreme Text data |
Char differs from varchar: save and process, trailing spaces are retained. Char is suitable for fields with definite length, while varchar is the opposite, char is generally faster than varchar, but it wastes space and reduces storage efficiency.
MySQL Learning note five: data types