MySQL字段长度、取值范围、存储开销（5.6/5.7/8.x的主要类型，区分显示宽度/有无符号/定点浮点、不同时间类型）

最新推荐文章于 2024-06-23 10:35:03 发布

王大雄_

最新推荐文章于 2024-06-23 10:35:03 发布

阅读量1w

点赞数 19

分类专栏： MySQL ━ 基本原理和函数推荐文章标签： mysql 字段长度 length 取值范围存储

本文链接：https://blog.csdn.net/zgdwxp/article/details/90675967

版权

待更新同时被 3 个专栏收录

23 篇文章 0 订阅

订阅专栏

MySQL ━ 基本原理和函数

19 篇文章 14 订阅

订阅专栏

1 前言

本文介绍了各类型的定义语法、存储原理、取值范围。
建议您收藏，日常评估存储开销时可以速查。

本文默认约定：

字符集：UTF8。
GBK等字符集的多字节字符长度略有不同，但原理类似。
MySQL版本：5.6/5.7/8.x。
部分新特性如时间小数部分的额外长度，是5.6.4后的，之前版本不考虑即可。
InnoDB引擎。

2 类型说明

MySQL中字段声明的长度，大部分指的是字符数，比如varchar(15)、bigint(18)、decimal(8,4)等。
而计算存储开销时，需要换算成字节(Byte)和位(Bit)¹。

2.1 字符串

以字符为单位，定义字段长度，适用于CHAR，VARCHAR和TEXT。²

如果列使用CHARACTER SET binary修饰，会变为二进制类型，如CHAR变为 BINARY，VARCHAR变为VARBINARY和TEXT变为BLOB。

2.1.1 CHAR和VARCHAR

两个类型类似，存储多个字符、可设置最大存储的字符数，存储开销与长度、字符集有关³。

先用两个小例子，快速了解下两个类型：

CHAR(4)，最多存储4个字符，不足4个尾部用空格填满。存储字节数 = 数据值的字节和 + 补位空格数
VARCHAR(4)，最多存储4个字符，有几个字符存储几个。存储字节数 = 数据值的字节和 + 1字节（长度标识）

下面是具体的特性介绍和对比⁴：

特性	`CHAR`	`VARCHAR`
长度	定长，固定字符数最大255个字符数据长度不足声明值时，在尾部自动填充空格	长度可变，可设置最大存储字符数最大不超过行大小（默认65535字节，注意是字节，下面会讲原因）
前缀	无	1~2字节，看列长度是否可能超过255字节比如`VARCHAR(100)`，字符集为`UTF8`，则字节最大可能为300字节，所以会使用2个字节标识长度
有否尾部空格	长度不足默认用空格填满检索和获取时会自动去除	不会自动填充空格输入值就包含空格，则会存储，检索和获取数据都会体现
超长处理	超长部分如果是空格自动截断如果是字符，严格模式下会报错	超长部分如果是空格自动截断，并生成警告如果是字符，严格模式下会报错
存储开销	数据值的字节和 + 补位空格数	数据值的字节和 + 长度标识字节数

如果开启PAD_CHAR_TO_FULL_LENGTH模式，检索时尾部空格不会去除

CHAR超过255字符会报错，提示使用TEXT或BLOB：

ERROR 1074 (42000): Column length too big for column ''long_char'' 	(max = 255); use BLOB or TEXT instead

行大小默认65535字节，是所有列共享的，所以VARCHAR的最大值受此限制

在COMPACT、DYNAMIC行格式下，行大小除了数据列长度，还包括可空列标识，即NULL标识位。
如果有一个列允许为空，则需要1 bit来标识，每8 bits的标识会组成一个字段，该字段会存放在每行最开始的位置。
假设一张表中存在N个可空字段，NULL标识位需要 $\lceil{N/8}\rceil$ （向上取整）个字节。此时整行可用于数据存储的空间只有 $\lceil{N/8}\rceil$ 个字节。
需要了解更多行大小内容，可参考官方文档⁵。

VARCHAR的长度计算比较复杂，这里列出其中的要点，详细的计算过程和案例，请参考《MySQL中VARCHAR最大长度是多少？》：

长度标识位表示的是字节数。大于255后使用两字节，是因为按照可能的数据大小，分为0 - 255(2⁸)、256 - 65535(2¹⁶)，刚好对应1字节和2字节
是根据字段声明的字符长度，计算可能的字节数，再决定长度标志的字节数。如VARCHAR(100)，字符集为UTF8，可能的字节数为300，长度标识则为2字节。

长度标志位只是存储开销，不影响长度约束。长度约束的是数据的字符数，允许的最大字符数与字符集有关。
如列指定字符集为UTF8、每个字符最大可占用3个字节，行最大长度为65535字节，那么该列可设置的最大列大小为65535 ÷ 3 ≈ 21844（向下取整）

同理如果是UTF8MB4、单字符最大占4个字节，可设置最大列大小为65535 ÷ 4 ≈ 16383

2.1.2 TEXT

TEXT包括四种类型：TINYTEXT，TEXT， MEDIUMTEXT和LONGTEXT。
这里只简单介绍TEXT，对其他三个有兴趣，可以参考官方文档²。

TEXT最大长度为65535（2¹⁶ − 1）个字符。如果是多字节字符，则有效最大长度会更少。存储时会增加2字节的前缀、标识长度。

TEXT可以声明为TEXT(M)，M指该列最多可存储多少个字符；此时TEXT基本可看做VARCHAR，存储特征基本类似，但也有一点区别：

TEXT列仅占用9 ~ 12字节的行大小，因为其内容是存储在数据页外的⁵。
TEXT列不允许设默认值
TEXT列的索引必须设置索引前缀长度，而VARCHAR可以不设。
TEXT无法使用临时表。因为TEXT列可能很大，临时表空间会膨胀的非常快，所以MYSQL的MEMORY引擎不支持这类大的数据类型。
如果临时表列包括了TEXT类型，MySQL会直接用磁盘上的表、而不是内存中的表。
磁盘比内存的I/O效率低很多，这就意味着性能急剧降低。
所以访问类似表时，尽可能在要返回这个数据时，才查询这个列。
- 避免使用SELECT *，它会选择所有列，而是在已经确定结果集范围后、左联获取对应的TEXT字段；
- 可以将TEXT单独拆出一个表，这样读写时减少与该列发生关系的可能，性能也会提升。

TEXT的大小，除了上面说的类型限制，实际上还有物理因素限制：在客户端和服务器之间传输的最大值，由可用内存量和通信缓冲区的大小确定。

可通过更改max_allowed_packet变量，来更改消息缓冲区的大小，但是服务器和客户端都要修改。

2.2 整数类型⁶

整数是定长类型，长度为字节数
常见的INT(4)只是设置了显示宽度，不影响存储长度。要了解显示宽度细节，可参考《MySQL显示宽度与字段长度》

类型	容量(Bytes)	最小(signed)	最大(signed)	unsigned范围
`TINYINT`	1	-128	127	0~255
`SMALLINT`	2	-32768	32767	0~65,535
`MEDIUMINT`	3	-8388608	8388607	0~16777215
`INT`	4	-2147483648	2147483647	0~4294967295
`BIGINT`	8	-2⁶³ = 9223372036854775808	2⁶³-1 = 9223372036854775807	0~18446744073709551615

数值类型默认是有符号的：

如果数据只有正整数，那么声明为无符号(unsigned)，容量可以翻一倍。
如果列指定了ZEROFILL，MySQL会将该列自动标识为UNSIGNED

再次提醒：常见的INT(4)只是设置了显示宽度，不影响存储长度。要了解显示宽度细节，可参考《MySQL显示宽度与字段长度》

2.3 定点类型和浮点类型

从精确度考虑，建议使用定点类型。且MySQL 8.0.17开始，将逐步去除浮点型的支持。
下面先介绍定点类型。

2.3.1 定点类型（DECIMAL为例）

DECIMAL(M,D)

M为最大位数（含小数部分，又称精度）。它的范围是1到65。
D是小数点右边的位数（又称刻度）。它的范围为0到30，且不得大于M

如果D省略，则默认值为0。如果M省略，则默认值为10。

DECIMAL以二进制存储，因此实际存储长度、与表达的数据范围是需要换算的：

每9个十进制数字打包为4个字节，不足9个按下表计算

4个字节恰好是INT的长度，而上面讲过，有符号的INT最大表达2*10^10左右的十进制数字，所以每9个打包成4个字节就可以理解了。
整数和小数部分的存储分开计算
例1：DECIMAL(18,9)列在小数点的任一侧都有9位数字，因此整数部分和小数部分每个都需要4个字节。
例2：DECIMAL(20,6)列有十四个整数数字和六个小数位数。整数位中的9位需要4个字节，其余5位需要3个字节。六个小数位需要3个字节

参考官方文档：《DECIMAL Data Type Characteristics》

如果遇到该类型的字段超长错误，可参考实例分析：https://learn.blog.csdn.net/article/details/100988201

2.3.2 浮点类型

浮点类型表示近似数据值，包括单精度FLOAT和双精度DOUBLE。

类型	精度	取值范围	存储开销
`FLOAT`	0 - 23	-3.402823466E+38 ~ -1.175494351E-38 0 1.175494351E-38 ~ 3.402823466E+38	4
`DOUBLE`	24 - 53	-1.7976931348623157E+308 ~ -2.2250738585072014E-308 0 2.2250738585072014E-308 ~ 1.7976931348623157E+308	8

上面的取值范围是基于IEEE标准的理论限制。实际范围可能会略小，具体取决于硬件或操作系统
关于IEEE标准的分析，可参考《浮点型数据（float, double）存储IEEE标准解析和应用》

支持两种语法：

标准语法：FLOAT(P)，P表示精度，但实际上该值决定了存储开销。P在0_{23之间占4个字节（单精度），在24}53之间则占8个字节（双精度）。也就意味着虽然用FLOAT声明，但实际上精度如果大于23，则实际变成了DOUBLE。
非标准语法：FLOAT(M,D)或REAL(M,D)或DOUBLE PRECISION(M,D)。M表示总位数，D表示小数位。
- 如FLOAT(7,4)效果为999.9999
- 将值999.00009保存到FLOAT(7,4)列中，MySQL会自动四舍五入，结果是999.0001
考虑到用准确精度定义近似数据，可能会出现一些问题。并且他们的实现还受硬件或操作系统的影响。从8.0.17开始，MySQL不支持非标准语法。

MySQL 8.0.17是对浮点影响比较大的一个版本。从该版本开始，MySQL将逐步去除浮点型的支持。除了上面提到的语法变化，还建议去除UNSIGNED支持，MySQL考虑使用简单的检查约束来替代它。

所以建议使用定点类型（DECIMAL等）替代浮点类型（FLOAT和DOUBLE）。
如果你的业务一定要近似数据值，那么也建议使用DOUBLE来声明浮点，不建议使用FLOAT或DOUBLE(P)。

可移植性会更好
MySQL所有计算以双精度进行，使用FLOAT可能还有一些意向不到的问题（如https://dev.mysql.com/doc/refman/8.0/en/no-matching-rows.html）。

浮点型的常见问题，可参考https://dev.mysql.com/doc/refman/8.0/en/problems-with-float.html

2.4 布尔值

MySQL字段类型声明时，可以使用BOOLEAN类型，对应Java POJO字段可以是boolean。

但MySQL实际上没有内置的布尔类型，声明为布尔类型，会使用TINYINT(1)存储。

建表后执行show create table xxx;，会发现BOOLEAN字段的类型是TINYINT(1)
数据存储时TRUE=1、FALSE=0，查询结果集时可以看到显示的是0、1

尽管如此，建表SQL中仍强烈建议写为BOOLEAN，这样语义清晰、便于维护。

BOOLEAN的介绍可参考：https://www.yiibai.com/mysql/boolean.html

2.5 日期与时间

建议读完下面的类型介绍后，进一步阅读后面的《容易忽略的存储细节》章节，对时间类型的理解会很有帮助。
时间类型要用好，需配合各类函数，可参考《MySQL日期与时间函数（日期/时间格式化、增减、对比、时区等）》

主要类型介绍

	描述	显示格式	取值范围
`YEAR`	年	`YYYY`	`0000`、`1901`~`2155`
`DATE`	有日期、没有时间	`YYYY-MM-DD`	`1000-01-01` ~ `9999-12-31`
`TIME`	时分秒	`hh:mm:ss`	`-838:59:59 ~ 838:59:59`⁷
`DATETIME`	包含日期和时间	`YYYY-MM-DD hh:mm:ss`	`1000-01-01 00:00:00` ~ `9999-12-31 23:59:59`
`TIMESTAMP`	包含日期和时间	`YYYY-MM-DD hh:mm:ss`	`1970-01-01T00:00:01Z` ~ `2038-01-19T03:14:07Z`

从5.6.4开始，含TIME的类型开始支持小数秒，在此之前所有类型都不支持（使用MICROSECOND可以获取，但存储时都会丢弃）。多了数据，存储开销也有变化：

类型	小数秒 5.6.4开始	存储字节数 5.6.4之前	存储字节数 5.6.4开始
`YEAR`	不支持	1	1
`DATE`	不支持	3	3
`TIME`	`-838:59:59.000000 ~ 838:59:59.000000`	3	3+小数秒存储
`DATETIME`	`1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.999999`	8	5+小数秒存储
`TIMESTAMP`	`1970-01-01T00:00:01.000000Z ~ 2038-01-19T03:14:07.999999Z`	4	4+小数秒存储

小数秒的存储需要0到3个字节，取决于精度：

`精度（小数秒位数）`	0	1~2	3~4	5~6
`存储字节数（Byte）`	0	1	2	3

注意：小数秒会四舍五入。
如果向一个未声明小数秒精度的列存入小数秒，当小数秒≥.5时，会发生四舍五入。
比如极端边界值2019-11-19 23:59:59.999，如果存放到datetime字段中，实际存放值会变成2019-11-12 00:00:00，日期实际上多了一天。

时间类型的特例

YEAR有2位和4位两种类型，取值范围不同。日常中主要使用的是4位，上面也只介绍了这种。MySQL 8.0开始已经不再支持2位，如果想要了解，可参考《MySQL官方文档-YEAR类型》。
TIME类型小时部分最大值可以达到838而不是24，是因为TIME类型还支持表示时间差值，比如TIMEDIFF等函数运算结果。
TIMESTAMP是将当前时间转换为 UTC时间 进行存储，查询或检索时再转换为当前时区。
- 如果存储时是一个时区，查询时更换了时区，则显示值与存储时的值是不同的。
- DATETIME等类型存储绝对值，而不是UTC值，所以没有该问题。
- TIMESTAMP同样存在 UNIX 2038问题、即无法表示大于2038-01-19T03:14:07.999999的值，大部分系统和语言已解决该问题，MySQL目前没有解决该问题。（测试时注意时区问题，东八区需插入2038-01-19T11:14:08来测试）
- MySQL有一个UNIX_TIMESTAMP()函数，是获取UNIX时间戳的整数值，不会像TIMESTAMP转换成当前时区显示，且入参支持大于TIMESTAMP最大值的时间。
从5.6.4开始，DATETIME支持ON UPDATE CURRENT_TIMESTAMP，该项不再是TIMESTAMP专有。但每个表仍然只能有一个自动更新的时间列。

时间类型容易忽略的范围和存储细节

在阅读前面的章节时，不知道你有否下面的疑问：

为什么DATE是3字节、TIME是3字节，而DATETIME不是6字节呢？
为什么5.6.4开始，DATETIME可以从8字节降到5字节，而数据取值范围不变？
DATETIME和TIMESTAMP有什么区别，哪些区别导致了两者存储差了1个字节？

要解决这几个问题，要从日期和时间的存储结构入手⁸。下面我们看看字段的取值范围与存储结构。

`YEAR`和`DATE`的存储结构

YEAR和DATE因为不支持小数秒，所以在5.6.4前后，取值范围和存储结构不变，且字节序仍为LITTLE-ENDIAN（《什么是Little Endian和Big Endian》）：

YEAR：1字节整数。存储从1901开始的年数，1字节最大可表示255，因此可以表示到2155；显示时转换为YYYY；
00000000 = 0000
00000001 = 1901
11111111 = 2155
DATE：3字节整数。取值范围1000-01-01 00:00:00 ~ 9999-12-31 23:59:59，以YYYY×16×32 + MM×32 + DD计算出实际值存放，这样算最大位数就是 $\log_2^{9999\times16\times32}≈23$ ，近似3字节。

尽管存储上支持2019-11-31，但MySQL服务器会判断日期是否真实有效。对于2019-11-31，严格模式下会报错，非严格模式下会自动转成0000-00-00。
如果要启用这些非法的日期，可以配置系统变量ALLOW_INVALID_DATES。
所有时间类型都如此。

`TIME`、`DATETIME`、`TIMESTAMP`的存储结构

从5.6.4开始，三个类型的非小数部分，字节序由LITTLE-ENDIAN变更为BIG-ENDIAN（[《什么是Little Endian和Big Endian》]。
同时TIME和DATETIME的存储结构发生了较大变化。

`TIME`

TIME类型从5.6.4开始，除了增加了小数秒支持，存储结构也发生了较大变化：

5.6.4之前，以DD×24×3600 + HH×3600 + MM×60 + SS计算出实际值，存储到3字节的整数上。取值范围为-838:59:59 ~ 838:59:59。

$\log_2^{31\times24\times3600}≈22$
5.6.4开始，支持小数秒，整数部分取值范围不变。存储开销为3字节+小数秒存储，整数部分存储结构如下：
0 0 00000000 00 000000 000000
符号位 预留扩展位 HH(0~838) MM(0~59) SS(0~59)

`TIMESTAMP`

TIMESTAMP类型在5.6.4前后，只是增加了小数秒支持，存储结构基本无变化，仍是以4字节整数（可看做SIGNED INT）存储从1970-01-01T00:00:00Z（UNIX纪元）经过的秒数。
UNIX纪元看做0值。小于UNIX纪元的时间插入会报错，官方文档虽未说明，怀疑TIMESTAMP与DATETIME类似，符号位永远是1，0值是保留值、仅用于以后扩展。

`DATETIME`

DATETIME类型从5.6.4开始，除了增加了小数秒支持，存储结构也发生了较大变化，存储开销从8字节降到了5字节：

5.6.4之前，4字节整数表示日期（YYYY×10000 + MM×100 + DD），4字节整数表示时间（HH×10000 + MM×100 + SS），计算出实际值，存储到8字节的整数上。取值范围为1000-01-01 00:00:00 ~
9999-12-31 23:59:59。

10000已经大于MM*100+DD/SS了，所以下面只用这个来估算位数：
( $\log_2^{9999\times10000}≈30$ ) + ( $\log_2^{60\times10000}≈18$ )
疑问：按算法来讲，时间部分存储到3个字节就够了，那5.6之前为什么要4个字节呢？官方文档没有说明，即使时间类型底层是整数值，但DATE类型也可以使用3字节的整数、而不是4字节的INT。有了解的朋友还请留言指导下。
5.6.4开始，支持小数秒，整数部分取值范围不变，但存储结构有较大调整、存储开销降到了5字节+小数秒存储。整数部分存储结构如下：

符号位年+月
YYYY(0~9999)*13 + MM(0~12) 天
DD(0~31) 小时
HH(0~23) 分
MM(0~59) 秒
SS(0~59)
0 0 0000 0000 0000 0000 0 0000 0 0000 00 0000 00 0000
1bit 17bit 5bit 5bit 6bit 6bit

共计1+17+5+5+6+6=40bits，占用5个字节。其中符号位永远是1，0只仅是保留值、用于以后扩展。

符号位	年+月 `YYYY(0~9999)*13 + MM(0~12)`	天 `DD(0~31)`	小时 `HH(0~23)`	分 `MM(0~59)`	秒 `SS(0~59)`
`0`	`0 0000 0000 0000 0000`	`0 0000`	`0 0000`	`00 0000`	`00 0000`
1bit	17bit	5bit	5bit	6bit	6bit

特别感谢August大橙子的帮助，对该问题深入源码层面的分析。推荐阅读他的《MySQL 5.6.4 及以上，DATETIME 存储空间如何从 8 字节减小到 5 字节，却能存储同样的时间范围的？》，理解MySQL在存储上的巧妙设计

时间类型小结

日常使用中，时间类型的选择是一个比较头疼的问题，有提倡DATETIME或TIMESTAMP，有受TIMESTAMP启发、建议直接存储INT或BIGINT的。
我们从下面几个维度比较：

取值范围：DATETIME > TIMESTAMP = INT
时间精度：DATETIME = TIMESTAMP > INT
存储开销（同等精度）：DATETIME > TIMESTAMP = INT
可自动更新：DATETIME和TIMESTAMP支持ON UPDATE，INT不支持
查询性能：因为底层都是转换为整数存储，不考虑存储空间优化，几种类型性能几乎相同。

如果不考虑存储、对细微的性能感受不大、对时间范围要求较高，建议选择DATETIME；
如果考虑存储、且对时间范围要求不高，建议选择TIMESTAMP。
INT的代码处理成本较高，不建议；BIGINT虽然可以解决UNIX 2038问题，但代码处理成本高，而且相信MySQL未来会提供标准解决方案。

关于字段的取值范围、存储开销，以上就是全部内容。

如需计算字段长度，可以使用函数length、char_length、bit_length，具体可参考《MySQL length/bit_length/char_length计算字段长度/大小》
更多基础命令可参考《https://learn.blog.csdn.net/article/category/9232935》

文中有几个设计疑问，未找到资料验证，待更新补充：

DATETIME以前为什么用4字节而不是3字节存储时分秒部分；10000这个位移量是怎么设计得来的？
DATETIME在5.6.4开始，年份用13位存储，理论上存不到9999才对，为什么官方说可以支持？

以上。感谢您的阅读。

MySQL字段存储开销：https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html ↩︎
MySQL字符串类型概述：https://dev.mysql.com/doc/refman/8.0/en/string-type-overview.html ↩︎ ↩︎
MySQL Char类型：https://dev.mysql.com/doc/refman/5.6/en/char.html ↩︎
MySQL官方文档-存储要求：https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html ↩︎
MySQL官方文档-列数与行大小限制：https://dev.mysql.com/doc/refman/8.0/en/column-count-limit.html#column-count-limits ↩︎ ↩︎
MySQL官方文档-数值类型：https://dev.mysql.com/doc/refman/5.6/en/integer-types.html ↩︎
MySQL官方文档 - 时间与日期的各部分取值：https://dev.mysql.com/doc/refman/8.0/en/date-and-time-literals.html ↩︎
MySQL官方文档 - 时间与日期的存储表现：https://dev.mysql.com/doc/internals/en/date-and-time-data-type-representation.html ↩︎

王大雄_

关注

19
点赞
踩
91

收藏

觉得还不错? 一键收藏
21
评论
MySQL字段长度、取值范围、存储开销（5.6/5.7/8.x的主要类型，区分显示宽度/有无符号/定点浮点、不同时间类型）

介绍MySQL所有字段的长度定义、计算方式、存储开销，包括char、varchar、int、bigint、decimal、double、boolean、date/timestamp等。如果你疑惑于int(4)是几位，varchar应该是63还是64、double还是decimal，那么本文可以找到答案。
复制链接

扫一扫