了解并熟悉 MySQL 中的数据类型,对建表和数据库优化都非常重要。 MySQL 实现了 SQL 定义的类型,也相应的增加了 tiny, small, big 的类型。 MySQL 的数据类型主要分成三个部分:
Numeric Type 数值型
Date and Time Type 日期和时间
String Type 字符型
更多的内容可以在官网 查到。
整型
MySQL 数据类型
大小
范围(有符号)
TINYINT(m)
1 字节
范围 (-128~127)
SMALLINT(m)
2 个字节
范围 (-32768~32767)
MEDIUMINT(m)
3 个字节
范围 (-8388608~8388607)
INT(m)
4 个字节
范围 2^31-1(-2147483648~2147483647)
BIGINT(m)
8 个字节
范围 2^63-1(+-9.22*10 的 18 次方)
取值范围如果加了 unsigned (无符号),则最大值翻倍,如 TINYINT unsigned 的取值范围为 (0~256)。
INT(m) 里的 m 是表示 SELECT 查询结果集中的显示宽度,并不影响实际的取值范围,没有影响到显示的宽度,不知道这个 m 有什么用。
浮点型 (float 和 double)
MySQL 数据类型
大小
含义
float(m,d)
4 字节
单精度浮点型 8 位精度 (4 字节) m 总个数,d 小数位
double(m,d)
8 字节
双精度浮点型 16 位精度 (8 字节) m 总个数,d 小数位
设一个字段定义为 float(5,3),如果插入一个数 123.45678, 实际数据库里存的是 123.457,但总个数还以实际为准,即 6 位。
定点数
浮点型在数据库中存放的是近似值,而定点类型在数据库中存放的是精确值。
decimal(m,d) 参数 m<65 是总个数,d<30 且 d
字符串 (char,varchar,text)
MySQL 数据类型
大小
含义
char(n)
0-255 字节
固定长度,最多 255 个字符
varchar(n)
0-65535 字节
固定长度,最多 65535 个字符
tinytext
0-255 字节
可变长度,最多 255 个字符
text
0-65535 字节
可变长度,最多 65535 个字符
mediumtext
0-16777 215 字节
可变长度,最多 2 的 24 次方 -1 个字符
longtext
0-4294967295 字节
可变长度,最多 2 的 32 次方 -1 个字符
char 和 varchar 比较:
char(n) 若存入字符数小于 n,则以空格补于其后,查询之时再将空格去掉。所以 char 类型存储的字符串末尾不能有空格,varchar 不限于此。
char(n) 固定长度,char(4) 不管是存入几个字符,都将占用 4 个字节,varchar 是存入的实际字符数 +1 个字节(n<=255)或 2 个字节 (n>255),所以 varchar(4), 存入 3 个字符将占用 4 个字节。
char 类型的字符串检索速度要比 varchar 类型的快。
varchar 和 text 比较:
varchar 可指定 n,text 不能指定,内部存储 varchar 是存入的实际字符数 +1 个字节(n<=255)或 2 个字节 (n>255),text 是实际字符数 +2 个字节。
text 类型不能有默认值。
varchar 可直接创建索引,text 创建索引要指定前多少个字符。varchar 查询速度快于 text, 在都创建索引的情况下,text 的索引似乎不起作用。
char(n) 和 varchar (n)中括号中 n 代表字符的个数,并不代表字节个数,所以当使用中文的时候 (UTF8) 意味着可以插入 m 个中文,但是实际会占用 m*3个字节。
同时 char 和 varchar 最大的区别就在于 char 不管实际 value 都会占用 n 个字符的空间,而 varchar 只会占用实际字符应该占用的空间 +1,并且实际空间 +1<=n
超过 char 和 varchar 的 n 设置后,字符串会被截断
char 的上限为 255 字节,varchar 的上限 65535 字节,text 的上限为 65535
char 在存储的时候会截断尾部的空格,varchar 和 text 不会
varchar 会使用 1-3 个字节来存储长度,text 不会
Value
CHAR(4)
Storage Required
VARCHAR(4)
Storage Required
’’
’ ‘
4 bytes
’’
1 byte
‘ab’
‘ab ‘
4 bytes
‘ab’
3 bytes
‘abcd’
‘abcd’
4 bytes
‘abcd’
5 bytes
‘abcdefg’
‘abcd’
4 bytes
‘abcd’
5 bytes
在使用 MySQL 存储字符串时经常会疑惑选择哪一种数据类型。
大字符串的选择逻辑
首先从空间方面,当 varchar 大于某些值时,会自动转换成 text,大概为
大于 varchar(255)变为 tinytext
大于 varchar(500)变为 text
大于 varchar(20000)变为 mediumtext
所以对于大内容 varchar 和 text 并没有太多区别。
其次从性能方面,索引是影响性能最关键的因素,对于 text 来说,只能添加前缀索引,并且索引最大只能 1000 字节。而 varchar 即使超过 1000 字节长,也会被截断。
二进制数据 (Blob)
BLOB 和 TEXT 存储方式不同,TEXT 以文本方式存储,英文存储区分大小写,而 Blob 是以二进制方式存储,不分大小写。
BLOB 存储的数据只能整体读出。
TEXT 可以指定字符集,BLOB 不用指定字符集。
数据类型
大小
用途
TINYBLOB
0~255 字节
不超过 255 个字符二进制字符串
BLOB
0~65535 字节
二进制
MEDIUMBLOB
0-16 777 215 字节
二进制形式的中等长度文本数据
LONGBLOB
0-4 294 967 295 字节
二进制形式的极大文本数据
日期和时间类型
MySQL 数据类型
大小
范围
含义
date
3 字节
1000-01-01/9999-12-31
日期 ‘2008-12-2’
time
3 字节
‘-838:59:59’/’838:59:59’
时间 ‘12:25:36’
datetime
8 字节
1000-01-01 00:00:00/9999-12-31 23:59:59
日期时间 ‘2008-12-2 22:06:44’
timestamp
4 字节
1970-01-01 00:00:00/2037 年某时
自动存储记录修改时间
每个时间类型有一个有效值范围和一个”零”值,当指定不合法的 MySQL 不能表示的值时使用”零”值。若定义一个字段为 timestamp,这个字段里的时间数据会随其他字段修改的时候自动刷新,所以这个数据类型的字段可以存放这条记录最后被修改的时间。
Datetime 和 timestamp 的区别
区别
datetime
timestamp
空间
8 字节
4 字节
是否允许空值
允许
允许
是否可以自定义值
可以
不可以
支持时间范围
1000-01-01 00:00:00/9999-12-31 23:59:59
不能早于 1970 或者晚于 2037 年
是否与时区相关
无关
值以 UTC 格式保存,存储 milliseconds,需要存储或者取出时手动转换时区
默认值
可以在指定 datetime 字段的值的时候使用 now() 变量来自动插入系统的当前时间
默认值为 CURRENT_TIMESTAMP() ,当前系统时间
结论
类型适合用来记录数据的原始创建时间,无论如何更改记录中其他字段, datetime 都不会改变,除非手动改变
数据库会自动修改其值,任何修改记录都会被更新,如果需要不设置自动更新,通过设置 DEFAULT CURRENT_TIMESTAMP 可实现。timestamp 类型适合用来记录数据最后修改时间。
数据类型的属性
MySQL 关键字
含义
NULL
数据列可包含 NULL 值
NOT NULL
数据列不允许包含 NULL 值
DEFAULT
默认值
PRIMARY KEY
主键
AUTO_INCREMENT
自动递增,适用于整数类型
UNSIGNED
无符号
CHARACTER SET name
指定一个字符集