前言
MySQL(5.6)的数据类型非常多,大致可以分为四大类:numeric types(数值类型)、date and time types(日期和时间类型)、string (character and byte) types(字符串/字符/字节类型)、spatial types(空间类型)。
四大类共有32种,如何选取合适的数据类型,也是一个非常重要的问题。当表中数据量比较小的时候,不能凸显数据类型的重要性,一旦数据量大了之后,不合适的数据类型会带来非常大的麻烦。
本篇主要讲解数值类型、日期时间类型和字符串类型三种。
numeric types
numeric types
(数值类型)又可以分为整数、定点数和浮点数,对应的类型分别如下
整数
对于整数数据类型,都可以表示成type[(M)] [UNSIGNED] [ZEROFILL]
的形式,M
表示最大显示宽度。最大显示宽度为255。显示宽度与类型可以存储的值的范围无关。
UNSIGNED
、ZEROFILL
是两个额外的属性,分别表示:是否有符号,是否填充0。
对于浮点和定点数据类型,M
是可以存储的总位数。 如果为数字列指定ZEROFILL
,MySQL会自动将UNSIGNED
属性添加到该列。
数据类型 | 大小 | 范围 | 解释 |
---|---|---|---|
tinyint | 1 byte | 有符号类型范围:[-128, 127] 无符号类型范围:[0, 255] | 非常小的整数 |
smallint | 2 byte | 有符号类型范围:[-32768, 32767] 无符号类型范围:[0, 65535] | 小整数值 |
mediumint | 3 byte | 有符号类型范围:[-8388608, 8388607] 无符号类型范围:[0, 16777215] | 中等整数值 |
int / integer | 4 byte | 有符号类型范围:[-2147483648, 2147483647] 无符号类型范围:[0, 4294967295] | 整数值 |
bigint | 8 byte | 有符号类型范围:[-9223372036854775808, 9223372036854775807] 无符号类型范围:[0, 18446744073709551615] | 极大整数值 |
定点数
对于定点数据类型,都可以表示成type[(M[, D])] [UNSIGNED] [ZEROFILL]
的形式,看起来有点绕,实际上是存在三种写法:type
、type(M)
、type(M, D)
。其中M
是可以存储的总位数,D
是小数点后的位数(小数位数)。小数点和(对于负数)符号不计入M
。如果D
为0,则值没有小数点或小数部分。
数据类型 | 解释 |
---|---|
decimal / dec / numeric / fixed | decimal 的最大位数(M )为65。支持的小数位数(D )的最大值为30。如果省略 D ,则默认值为0。如果省略M ,则默认值为10。使用 decimal 列的所有基本计算(+,-,*,/)均以65位精度完成。fixed 可用于与其他数据库系统兼容。 |
浮点数
浮点数包括float
和double
两种类型
数据类型 | 解释 |
---|---|
float[(M,D)] | 单精度浮点数。M 是总位数,D 是小数点后的位数。如果省略M 和D ,则将值存储到硬件允许的极限。单精度浮点数的精度约为小数点后7位FLOAT(M,D) 是非标准的MySQL扩展 |
float(p) | 浮点数。 p 表示位精度,但是MySQL仅使用此值来确定对结果数据类型使用FLOAT 还是DOUBLE 。如果p 从0到24,则数据类型为FLOAT ,没有M 或D 值。如果p 为25到53,则数据类型将变为DOUBLE ,且没有M 或D 值。FLOAT(p) 语法以实现ODBC兼容性 |
double[(M,D)] / double precision[(M,D)] / real[(M,D)] | 双精度浮点数。M 是总位数,D 是小数点后的位数。如果省略M 和D ,则将值存储到硬件允许的极限。双精度浮点数精确到大约15个小数位DOUBLE(M,D) 是非标准的MySQL扩展如果启用 REAL_AS_FLOAT SQL模式,则REAL 是FLOAT 的同义词,而不是DOUBLE |
date and time types
用于表示时间值的日期和时间数据类型为DATE
,TIME
,DATETIME
,TIMESTAMP
和YEAR
。
类型 | 解释 | 零值 |
---|---|---|
date | 日期类型,支持的范围:[‘1000-01-01’, ‘9999-12-31’] MySQL以 YYYY-MM-DD 格式显示date 值,但允许使用字符串或数字将值分配给date 列 | '0000-00-00' |
datetime[(fsp)] | 日期和时间组合,支持的范围:[‘1000-01-01 00:00:00.000000’, ‘9999-12-31 23:59:59.999999’] MySQL以 YYYY-MM-DD hh:mm:ss[.fraction] 的格式显示datetime 值,但允许使用字符串或数字将值分配给datetime 列fsp 表示小数位数,范围:[0, 6],如果不指定,默认为0,表示没有小数支持自动初始化和更新 | '0000-00-00 00:00:00' |
timestamp[(fsp)] | 时间戳类型,支持范围:('1970-01-01 00:00:01.000000’UTC, '2038-01-19 03:14:07.999999’UTC]fsp 表示小数位数,范围:[0, 6],如果不指定,默认为0,表示没有小数 | '0000-00-00 00:00:00' |
time[(fsp)] | 时间类型,支持范围:[’-838:59:59.000000’, ‘838:59:59.000000’] MySQL以 hh:mm:ss[.fraction] 的格式显示time 值,但允许使用字符串或数字将值分配给time 列fsp 表示小数位数,范围:[0, 6],如果不指定,默认为0,表示没有小数 | '00:00:00' |
year[(2|4)] | 2位或4位格式的年份。默认值为4位数字格式。 YEAR(2)或YEAR(4)的显示格式不同,但是值的范围相同。 在4位数格式中,值显示为:[1901, 2155],或者0000。 在2位数格式中,值显示为70至69,代表从1970到2069的年份。70-99表示1970-1999;00-69表示2000-2069 MySQL以 YYYY 或YY 格式显示YEAR 值,但允许分配使用字符串或数字将值添加到YEAR 列。 | 0000 |
对于timestamp
类型,服务器处理timestamp
定义的方式取决于explicit_defaults_for_timestamp
系统变量的值。
- 如果启用了
explicit_defaults_for_timestamp
,则不会将DEFAULT CURRENT_TIMESTAMP
或ON UPDATE CURRENT_TIMESTAMP
属性自动分配给任何TIMESTAMP
列。它们必须明确包含在列定义中。另外,任何未明确声明为NOT NULL
的TIMESTAMP
都允许NULL
值。 - 如果禁用
explicit_defaults_for_timestamp
,则服务器按以下方式处理TIMESTAMP
:- 没有特别指定的情况下,
insert
、update
操作时,表中的第一个timestamp
列的值会被自动更新成当前时间(如果没有指定值,或者指定NULL
),其他timestamp
列如果设置了NOT NULL
,而传入NULL
时,也会被更新成当前时间 - 可以使用
DEFAULT CURRENT_TIMESTAMP
和ON UPDATE CURRENT_TIMESTAMP
列定义子句来指定自动初始化和更新到当前日期和时间。默认情况下,第一个TIMESTAMP
列具有这些属性。但是,可以将表中的任何TIMESTAMP
列定义为具有这些属性。
- 没有特别指定的情况下,
从MySQL 5.6.6开始,explicit_defaults_for_timestamp
可用。在5.6.6之前,服务器将处理TIMESTAMP
,如禁用explicit_defaults_for_timestamp
情况所述。
查看explicit_defaults_for_timestamp
是否启用
show variables like 'explicit_defaults_for_timestamp';
string (character and byte) types
用于表示字符串(字符和字节)类型的数据类型有: CHAR
、VARCHAR
、 BINARY
、 VARBINARY
、 BLOB
、TEXT
、ENUM
、SET
。.
对于字符串列(CHAR
,VARCHAR
和TEXT
类型)的定义,MySQL以字符单位解释长度规范。对于二进制字符串列(BINARY
,VARBINARY
和BLOB
类型)的定义,MySQL以字节单位解释长度规范
[NATIONAL] CHAR[(M)]
- 固定长度的字符串,在存储时总是用空格填充到指定的长度。
M
代表以字符为单位的列长。M
的范围是0到255。如果省略M
,则长度是1 - 除非启用了
PAD_CHAR_TO_FULL_LENGTH
SQL模式,否则在检索CHAR
值时将删除尾部空格 NATIONAL CHAR
(或其等效的简短格式NCHAR
)是定义CHAR
列应使用一些预定义字符集的标准SQL方法。 MySQL使用utf8
作为此预定义字符集
- 固定长度的字符串,在存储时总是用空格填充到指定的长度。
[NATIONAL] VARCHAR(M)
- 可变长度的字符串。
M
表示最大列长度,以字符为单位。M
的范围是0到65535 VARCHAR
的有效最大长度取决于最大行大小(65535字节,这个值在所有列之间共享)和所使用的字符集。例如,utf8
字符每个字符最多需要三个字节,因此使用utf8
字符集的VARCHAR
列可以声明为最多21844个字符- MySQL存储
varchar
类型的数据时,需要1个字节或者2个字节作为数据的前缀,如果值的长度不超过255个字节,使用1个字节,如果有可能超过255个字节,则使用2个字节 - MySQL不会从
VARCHAR
值中删除尾部空格 NATIONAL VARCHAR
是定义VARCHAR
列应使用某些预定义字符集的标准SQL方法。 MySQL使用utf8
作为此预定义字符集。NVARCHAR
是NATIONAL VARCHAR
的简写
- 可变长度的字符串。
最佳实践
数值类型
- 应该尽量使用可以正确存储数据的最小数据类型,更小的数据类型通常更快,因为它们占用更少的磁盘、内存和CPU缓存,并且处理时需要的CPU周期更少,但是要确保没有低估需要存储的值的范围,如果无法确认哪个数据类型,就选择你认为不会超过范围的最小类型
- 尽量使用简单的数据类型,简单数据类型的操作通常需要更少的CPU周期,例如,
- 整型比字符操作代价更低,因为字符集和校对规则是字符比较比整型比较更复杂,
- 使用mysql自建类型,而不是字符串来存储日期和时间
- 可以考虑用整型存储IP地址
日期和时间类型
日期类型的选取,最麻烦的地方在于datetime
和timestamp
这两个类型的选取。所以必须完全了解这两种类型的区别,再结合实际情况进行取舍。
datetime
和timestamp
区别
- 存储:
datetime
占8个字节,timestamp
占4个字节 - 范围:
datetime
表示区间[1000-01-01 00:00:00, 9999-12-31 23:59:59],timestamp
表示区间[1970-01-01 , 2038-01-19] - 时区:
datetime
值不随时区变化而变化,timestamp
一旦时区变动,会自动修改timestamp
的值 - 效率:
timestamp
更轻量,索引起来更快
了解了这几点区别之后,相信比较容易就能做出取舍了。
字符串类型
字符串类型的选取,基本上也就纠结于char
和varchar
,所以需要了解两者的具体区别
- 长度:
char
最长255个字符,varchar
最长约21844个字符 - 检索:
char
检索时会自动去除尾部空格,varchar
不会 - 效率:
char
写效率比varchar
高,以空间换时间
参考
- https://dev.mysql.com/doc/refman/5.6/en/data-types.html