最新MySQL8实用系列（二）MySQL数据类型

建表时，需要根据实际需求选择不同的数据类型，不同的数据类型会影响数据库和SQL语句的效率和速度。所以如何选择最优的数据类型，以及了解不同数据类型针对何种数据是必要的。

环境：MySQL 8.0.32

主要使用的数据类型有以下几类：

• 整数类型：bool、tinyint、smallint、mediumint、int、bigint
• 浮点类型：float、double、decimal
• 字符类型：char、varchar、blob、text
• 日期类型：Date、Datetime、Time、TimeStamp

一、整数类型

使用：int(n) [unsigned]

类型	字节数	有符号值范围	无符号值范围
tinyint	1	[-2^7, 2^7-1]	[0, 2^8-1]
smallint	2	[-2^15, 2^15-1]	[0, 2^16-1]
mediumint	4	[-2^23, 2^23-1]	[0, 2^24-1]
int	4	[-2^31, 2^31-1]	[0, 2^32-1]
bigint	8	[-2^63, 2^63-1]	[0, 2^64-1]

整数类型默认是有符号的，无符号需在类型后加上unsigned。n为宽度，省略时宽度为对应类型无符号最大值的十进制的长度。

建议：通常根据数据范围选择类型，且数据不含小数。主键一般使用int即可，如果超出范围可考虑分表。tinyint一般用于可枚举有限可能值的属性，如性别等。

二、浮点类型

类型	字节数	有符号值范围	无符号值范围
float	4	(-3.402823466E+38, -1.175494351E-38), 0, (1.175494351E-38, 3.402823466351E+38)	0, (1.175494351E-38, 3.402823466E+38)
double	8	(-1.7976931348623157E+308, -2.2250738585072014E-308), 0, (2.2250738585072014E-308, 1.797693134 8623157E+308)	0，(2.2250738585072014E-308, 1.7976931348623157E+308)
decimal(M, D)	M+2	依赖M和D的值	依赖M和D的值

float表示单精度浮点数，小数点后有效位数7位。double表示双精度浮点数，小数点后有效位数16位。不指定精度时，按照实际显示，超出位数部分会自动四舍六入五成双。MySQL8官方手册明确不推荐浮点型使用M和D。

decimal为定点型，M精度，表示该值的总共长度，取值范围1~65，D标度，表示小数点后面的长度，取值范围0~30，且不大于M。不指定精度时，默认decimal(10, 0)。超出标度部分会四舍五入，且触发警告。

建议：浮点型float、double存在精度的损失，默认自动根据需要显示的数据长度进行截断，所以需注意精度的范围，谨慎选择。单精度相较双精度消耗空间更小，速度更快，但是准确度相比较低。

对于要求精确的数据，建议使用定点型decimal，其不存在精度的损失且消耗空间更小，但需设置超过数据要求范围的精度和标度。

三、字符类型

类型	范围	存储所需字节	说明
char(m)	[0,m] ， m 的范围 [0, 2^8-1]	m	定长字符串
varchar(m)	[0,m]，m的范围[0, 2^16-1]	m	0-65535 字节
blob	0-65535(2^16-1)字节	L+2	二进制形式的长文本数据
text	0-65535(2^16-1)字节	L+2	长文本数据

L表示存储的数据本身占用的字节，L 以外所需的额外字节为存放该值的长度所需的字节数。m存储的是字符，与编码无关。

建议：char是拿空间换取时间。存储数据的时候，默认后面会用空格填充到指定长度，而在检索的时候去掉后面的空格。如果存放的数据为固定长度时建议使用char类型，如手机号码、身份证号码等。

varchar是拿时间换取空间，它会使用最小的符合需求的长度，根据实际内容保存数据。最多能存储的数据长度约16383。

如果需要存储更大的数据，需要使用blob或text。blob以二进制形式存储，text以文本形式存储。不建议使用，其浪费空间且强制生成硬盘临时表，如果必须使用则拆分到只有主键和blob\text的单独的表。        

        

四、日期类型

类型	字节大小	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	时间值或持续时间
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:5	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07 ，格林尼治时间 2038 年 1 月 19 日凌晨03:14:07	YYYYMMDD HHMMSS	时间戳

一目了然，根据实际需要选择。

五、选择建议总结

• 一般情况下选择可以正确存储数据的最小数据类型，越小的数据类型通常更快，占用磁盘，内存和CPU缓存更小。
• 优先选择整数类型，简单的数据类型的操作通常需要更少的CPU周期，例如：整型比字符操作代价要小得多，更高效，查询更快，因为字符集和校对规则(排序规则)使字符比整型比较更加复杂。
• 尽量定义列为NOT NULL，除非真的需要NULL类型的值，有NULL的列值会使得索引、索引统计和值比较更加复杂，且含NULL复合索引无效，很难进行查询优化。
• 浮点类型的建议统一选择decimal，但需根据需求设置精度和标度。
• 记录时间的建议选择int或bigint类型，将时间转换为时间戳格式，如将时间转换为秒、毫秒， 进行存储，方便走索引。
• 尽量不使用text/blob类型，其浪费空间且强制生成硬盘临时表，如果必须使用则拆分到只有主键和blob\text的单独的表。        

以上就是MySQL数据类型的介绍和选择的建议。如有问题和建议，可私信或评论，非常感谢。