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