1.Integer Types(精确值)
类型 | 范围M |
BIT[(M )]
| 1 <= M <= 64,比如 b'111' and b'10000000' |
TINYINT[(M )] [UNSIGNED] | -128 ~ 127(有符号整数),0 ~ 255(无符号整数) |
SMALLINT[(M )] [UNSIGNED] | -32768 ~ 32767(有符号整数) ,0 ~ 65535(无符号整数) |
MEDIUMINT[(M )] [UNSIGNED] | -8388608 ~ 8388607(有符号整数) ,0 ~ 16777215(无符号整数) |
INT[(M )] [UNSIGNED] | -2147483648 ~ 2147483647(有符号整数) ,0 ~ 4294967295(无符号整数) |
BIGINT[(M )] [UNSIGNED] | -263 ~ 263-1(有符号整数) ,0 ~ 264-1(无符号整数) |
2.Fixed-Point Types(精确值)
类型 | 精度M | 小数点D |
DECIMAL[(M [,D ])] [UNSIGNED]
| 0≤M≤65 | 最大可能值为 30,但不应大于M -2。 |
NUMERIC[(M [,D ])] [UNSIGNED]
| 0≤M≤65 | 最大可能值为 30,但不应大于M -2。 |
3.Floating-Point Types(近似值)
浮点类型遵循IEEE 754二进制浮点算术标准。P是IEEE 754格式中的小数(fraction)的二进制位数。
类型 | 位数P | 范围 |
FLOAT(p )
| 0≤P≤24 | ±3.402823466E+38,小数点后大约7个十进制小数位 |
FLOAT(p )
| 25≤P≤53 | ±1.7976931348623157E+308,小数点后大约15个十进制小数位 |
4.Character String Data Types
类型 | 字符M |
[NATIONAL] CHAR[(M )] [CHARACTER SET charset_name ] [COLLATE collation_name ]
| 0 <= M <= 255 |
[NATIONAL] VARCHAR(M ) [CHARACTER SET charset_name ] [COLLATE collation_name ] | 0 <= M <= 65535 |
TINYTEXT [CHARACTER SET charset_name ] [COLLATE collation_name ]
| 28-1
|
TEXT[(M )] [CHARACTER SET charset_name ] [COLLATE collation_name ]
| 216-1
|
MEDIUMTEXT [CHARACTER SET charset_name ] [COLLATE collation_name ]
| 224-1
|
LONGTEXT [CHARACTER SET charset_name ] [COLLATE collation_name ]
| 232-1
|
ENUM('value1 ','value2 ',...) [CHARACTER SET charset_name ] [COLLATE collation_name ]
| 65535个枚举值 |
SET('value1 ','value2 ',...) [CHARACTER SET charset_name ] [COLLATE collation_name ]
| 64个成员值 |
5.Binary String Data Types
类型 | 字节M |
BINARY[(M )]
| 0 <= M <= 255 |
VARBINARY(M )
| 0 <= M <= 65535 |
TINYBLOB | 28-1 |
BLOB[(M )] | 216-1 |
MEDIUMBLOB | 224-1 |
LONGBLOB | 232-1 |
6.Date and Time Data Types
fsp
值必须在 0 到 6 的范围内,表示微秒的精度。
类型 | 形式 |
YEAR[(4)]
| 0000 |
DATE | '0000-00-00' |
TIME[(fsp )]
| '00:00:00' |
DATETIME[(fsp )]
| '0000-00-00 00:00:00' |
TIMESTAMP[(fsp )]
| '0000-00-00 00:00:00' |
7.Boolean Data Types
类型 | 详情 |
BOOL , BOOLEAN | TINYINT(1) 的同义词,0 值被认为是假的,非 0 值被认为是真的。 |
8.JSON Data types
//json数组
["abc", 10, null, true, false]
//json对象
{"k1": "value", "k2": 10}
原创文章,作者:huoxiaoqiang,如若转载,请注明出处:https://www.huoxiaoqiang.com/basic/sql/8536.html