建表时,需要根据实际需求选择不同的数据类型,不同的数据类型会影响数据库和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数据类型的介绍和选择的建议。如有问题和建议,可私信或评论,非常感谢。