mysql 字段类型总结---字段类型总表_MySQL
程序员文章站
2022-05-24 11:14:07
...
原网址：http://kimbriggs.com/computers/computer-notes/mysql-notes/mysql-data-types-50.file
| Type {storage} | Name | Range | Attributes | Default |
|---|---|---|---|---|
|
Numeric {1 byte} |
TINYINT[(M)] | -128 TO 127 [0 to 255 if UNSIGNED] |
AUTO_INCREMENT UNSIGNED, ZEROFILL, SERIAL DEFAULT VALUE |
NULL [0 if NOT NULL] |
|
Numeric {2 bytes} |
SMALLINT[(M)] | -32,768 to 32,767 [0 to 65,535] |
AUTO_INCREMENT, UNSIGNED, ZEROFILL, SERIAL DEFAULT VALUE |
NULL [0 if NOT NULL] |
|
Numeric {3 bytes} |
MEDIUMINT[(M)] | -8,388,608 to 8,388,607 [0 to 16,777,215] |
AUTO_INCREMENT, UNSIGNED, ZEROFILL, SERIAL DEFAULT VALUE |
NULL [0 if NOT NULL] |
|
Numeric {4 bytes} |
INT[(M)] | -/+2.147E+9 [0 to 4.294E+9] |
AUTO_INCREMENT, UNSIGNED, ZEROFILL, SERIAL DEFAULT VALUE |
NULL [0 if NOT NULL] |
|
Numeric {8 bytes} |
BIGINT[(M)] | -/+9.223E+18 [0 to 18.45E+18] |
AUTO_INCREMENT, UNSIGNED, ZEROFILL, SERIAL DEFAULT VALUE |
NULL [0 if NOT NULL] |
|
Numeric {4 or 8} |
FLOAT(p) | p=0-24 --> "FLOAT" p=25-53 --> "DOUBLE" |
UNSIGNED, ZEROFILL | NULL [0 if NOT NULL] |
|
Numeric {4 bytes} |
FLOAT[(M,D)] | Min=+/-1.175E-38 Max=+/-3.403E+38 |
UNSIGNED, ZEROFILL | NULL [0 if NOT NULL] |
|
Numeric {8 bytes} |
DOUBLE[(M,D)] | Min=+/-2.225E-308 Max=+/-1.798E+308 |
UNSIGNED, ZEROFILL | NULL [0 if NOT NULL] |
|
Numeric {M+2} |
DECIMAL[(M,[D])] Stored as string |
Max Range = DOUBLE range Fixed point vs. DOUBLE float |
UNSIGNED, ZEROFILL | NULL [0 if NOT NULL] |
|
Bit {8 bytes} |
BIT[(M)] | Binary. Display by [add zero or converting with BIN()]. M=1-64 | Prior to 5.03 TINYINT(1) Synonym |
NULL [0 if NOT NULL] |
|
String {M char's} |
CHAR[(M)] | M=0-255 Characters, FIXED. Right padded with spaces. |
BINARY, CHARACTER SET | NULL ["" if NOT NULL] |
|
String {M char's1} |
VARCHAR(M) | M=0-65,535 Characters M=0-255 BINARY, CHARACTER SET |
NULL | ["" if NOT NULL] |
|
String {#char's1} |
TINYTEXT2 | 0-255 Characters | BINARY, CHARACTER SET | NULL ["" if NOT NULL] |
|
String {#char's1} |
TEXT2 | 0-65,535 Char's | BINARY, CHARACTER SET | NULL ["" if NOT NULL] |
|
String {#char's1} |
MEDIUMTEXT2 | 0-16,777,215 Char's | BINARY, CHARACTER SET | NULL ["" if NOT NULL] |
|
String {#char's1} |
LONGTEXT2 | 0-4,294,967,295 Char's | BINARY, CHARACTER SET | NULL ["" if NOT NULL] |
|
String {M bytes} |
BINARY[(M)] | M=0-255 bytes, FIXED. | Global Only (case sensitive) |
NULL ["" if NOT NULL] |
|
String {M bytes} |
VARBINARY(M) | 0-65,535 bytes M=0-255 Global Only |
(case sensitive) NULL | ["" if NOT NULL] |
|
String {#bytes1} |
TINYBLOB | 0-255 bytes | Global Only (case sensitive) |
NULL ["" if NOT NULL] |
|
String {#bytes1} |
BLOB | 0-65,535 bytes | Global Only (case sensitive) |
NULL ["" if NOT NULL] |
|
String {#bytes1} |
MEDIUMBLOB | 0-16,777,215 bytes | Global Only (case sensitive) |
NULL ["" if NOT NULL] |
|
String {#bytes1} |
LONGBLOB | 0-4,294,967,295 bytes | Global Only (case sensitive) |
NULL ["" if NOT NULL] |
|
String {1-2 bytes} |
ENUM2 ("A1","A2",...) |
Column is exactly 1 of 1-65,535 values | CHARACTER SET | NULL [1st value if NOT NULL] |
|
String {1-8 bytes} |
SET2 ("A1","A2",...) |
Column is 0 or more values in list of 1-64 members | CHARACTER SET | NULL ["" if NOT NULL] |
|
Date & Time {3 bytes} |
DATE | "1000-01-01" - "9999-12-31" | Global Only (YYYY-MM-DD) |
NULL ["0000-00-00" if NOT NULL] |
|
Date & Time {8 bytes} |
DATETIME | "1000-01-01 00:00:00" - "9999-12-31 23:59:59" |
Global Only (YYYY-MM-DD hh:mm:ss) |
NULL ["0000-00-00 00:00:00" if NOT NULL] |
|
Date & Time {3 bytes} |
TIME | "-838:59:59" - "838:59:59" | Global Only (hh:mm:ss) |
NULL ["00:00:00" if NOT NULL] |
|
Date & Time {4 bytes} |
TIMESTAMP | 19700101000000 - 2037+ |
Global Only (YYYYMMDDhhmmss) |
Current Date & Time |
|
Date & Time {1 bytes} |
YEAR | 1900 - 2155 | Global Only (YYYY) |
NULL ["0000" if NOT NULL] |
Notes: