MySQL field type
MySQL supports multiple types and can be broadly divided into three categories: numeric, date/time, and string (character) types.
Numeric type
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 +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 |
String
The string type refers to Char, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, enum, and set. This section describes how these types work and how they are used in queries.
Char and varchar:
1.char (n) If the number of characters in the deposit is less than n, then the space is appended to it, and then the space is removed when queried. So the char type stores a string with no spaces at the end, and varchar is not limited to this.
2.char (n) fixed length, char (4) regardless of whether it is deposited in several characters, will occupy 4 bytes, varchar is the actual number of characters stored in + 1 bytes (n<=255) or 2 bytes (n>255), so varchar (4), A deposit of 3 characters will take up 4 bytes.
The string retrieval speed of the 3.char type is faster than the varchar type.
varchar and text:
1.varchar can specify that the N,text cannot be specified, the internal storage varchar is the actual number of characters stored + 1 bytes (n<=255) or 2 bytes (n>255), the text is the actual number of characters + 2 bytes. The
2.text type cannot have a default value.
3.varchar creates an index directly, and text creates an index to specify the first number of characters. varchar queries are faster than text, and the index of text does not seem to work in the case of all indexes being created.
mediumblob
type |
size |
purpose |
CHAR |
0-255-byte |
fixed-length string |
VARCHAR |
0- 65535 bytes |
variable length string |
tinyblob |
0-255 bytes |
No more than 255 characters of binary string | /tr>
tinytext |
0-255 bytes |
short text string |
BLOB |
0-65 535 bytes /td> |
Long text data in binary form |
text |
0-65 535 bytes |
long text data |
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 |
binary form of large text data /td> |
longtext |
0-4 294 967 295 bytes |
great text data |
Date Time Type
The date and time types that represent time values are datetime, date, TIMESTAMP, hour, and year.
type |
size |
Range |
format |
Use |
DATE |
3 bytes |
1000-01-01/9999-12-31 |
Yyyy-mm-dd |
Date value |
Time |
3 bytes |
' -838:59:59 '/' 838:59:59 ' |
HH:MM:SS |
Time Value or duration |
Year |
1 bytes |
1901/2155 |
YYYY |
Year value |
Datetime |
8 bytes |
1000-01-01 00:00:00/9999-12-31 23:59:59 |
YYYY-MM-DD HH:MM:SS |
Blend date and time values |
TIMESTAMP |
4 bytes |
1970-01-01 00:00:00/2038 The end time is No. 2147483647 seconds, Beijing time 2038-1-19 11:14:07, GMT January 19, 2038 03:14:07 |
YYYYMMDD HHMMSS |
Mixed date and time values, timestamp |
Binary
Boolean: Bit
Bit represents a binary bit
Bit (8) represents 8 binary Bits
Gender can be defined as 0, 1, without using male or female strings
Data logic Removal
The state of a car parked in a garage.
All data based on both states can be stored using 0.
MySQL field type