MySQL的字段数量以及长度限制

一、InnoDB行格式

行格式	紧凑的存储特性	增强的可变长度列存储	大型索引键前缀支持	压缩支持	支持的表空间类型
REDUNDANT	N	N	N	N	system, file-per-table, general
COMPACT	Y	N	N	N	system, file-per-table, general
DYNAMIC	Y	Y	N	N	system, file-per-table, general
COMPRESSED	Y	Y	Y	Y	file-per-table, general

开发中常用的是DYNAMIC行格式，这里着重对它进行介绍，其他格式说明请查阅官网。

1.1 COMPACT存储特性

行DYNAMIC格式提供与行格式相同的存储特性COMPACT，但增加了对长可变长度列的增强存储功能，并支持大索引键前缀

每个索引记录包含一个 5 字节的标头，并且可能会有一部分的可变长度，用于将连续的记录链接在一起，并用于行级锁定;
记录头的可变长度部分包含一个用于指向NULL列的位向量。若索引中可以为 NULL的列数为N，则位向量占用N/8个字节数。（例如，如果有 9 到 16 列可以是，则位向量使用两个字节。）除了此向量中的位之外，别的为NULL的列并不占用空间。标头的可变长度部分还包含可变长度列的长度。每个长度占用一个或两个字节，具体取决于列的最大长度。如果索引中的所有列都是NOT NULL并且具有固定长度，那么记录头就没有可变长度部分;
对于每个非NULL可变长度字段，记录头包含一或两个字节的列长度。仅当部分列存储在溢出页外部或最大长度超过 255 字节且实际长度超过 127 字节时，才需要两个字节。对于外部存储的列，2字节长度表示内部存储部分的长度加上指向外部存储部分的20字节指针。内部部分是768字节，所以长度是768+20。20 字节的指针存储列的真实长度;
记录头之后是非NULL列的数据内容;
聚集索引中的记录包含所有用户定义列的字段。此外，还有一个 6 字节的事务 ID 字段和一个 7 字节的滚动指针字段;
如果没有为表定义主键，则每个聚簇索引记录还包含一个 6 字节的行 ID 字段;
每个二级索引记录包含了由聚集索引键定义的所有主键列，这些主键列不在二级索引中。如果任何主键列是可变长度的，则每个二级索引的记录头都有一个可变长度部分来记录它们的长度，即使二级索引是在固定长度列上定义的;
在内部，对于非可变长度字符集，固定长度字符列如CHAR(10)，是以固定长度格式存储的。并且不会从 VARCHAR类型的列中截断尾随空格;
在内部，对于诸如utf8mb3和utf8mb4之类的可变长度字符集， InnoDB尝试通过修剪尾随空格来将CHAR(N)存储为N字节。如果CHAR(N)列的字节长度超过N字节，则将尾随空格修剪为该列字节长度的最小值。CHAR(N)列的最大长度是最大字符字节长度 × N;
至少为 CHAR(N) 保留 N 个字节。在许多情况下，保留最小空间 N 可以使列就地更新，不会导致索引页碎片。相比之下，当使用 REDUNDANT 行格式时，CHAR(N) 列占用的长度是最大字符字节长度 × N;
大于或等于 768 字节的固定长度列被编码为可变长度字段，可以跨页存储。例如，如果字符集的最大字节长度大于 3，则 CHAR(255) 列可以超过 768 个字节，如utf8mb4;

1.2 DYNAMIC存储特性

DYNAMIC 行格式提供与 COMPACT 行格式相同的存储特性，但增强了可变长度的存储功能，并支持大索引键前缀;
当使用 ROW_FORMAT=DYNAMIC 创建表时，InnoDB 可以存储长的可变长度列值（ VARCHAR、VARBINARY、BLOB 、 TEXT ）完全离页，聚簇索引记录仅包含一个 20 字节的指针指向溢出页。大于或等于 768 字节的固定长度字段被编码为可变长度字段;
列是否存储在页外取决于页大小和行的总大小。当一行太长时，选择最长的列进行页外存储，直到聚集索引记录适合 B 树页。小于或等于 40 字节的 TEXT 和 BLOB 列按行存储;
DYNAMIC 行格式保持了将整行存储在索引节点中的效率（如果适合）（COMPACT 和 REDUNDANT 格式也是如此），但是 DYNAMIC 行格式避免了用大量数据字节填充 B 树节点的问题的长列。 DYNAMIC 行格式基于这样的想法，即如果长数据值的一部分存储在页外，则通常将整个值存储在页外是最有效的。使用 DYNAMIC 格式，较短的列可能会保留在 B 树节点中，从而最大限度地减少给定行所需的溢出页数;
DYNAMIC 行格式支持最多 3072 字节的索引键前缀;
使用 DYNAMIC 行格式的表可以存储在system tablespace、file-per-table tablespace和general tablespace中。要在system tablespace中存储动态表，请禁用 innodb_file_per_table 并使用常规 CREATE TABLE 或 ALTER TABLE 语句，也可以搭配 TABLESPACE [=] innodb_system 。 innodb_file_per_table 变量不适用于一般表空间，在使用 TABLESPACE [=] innodb_system 表选项在system tablespace中存储 DYNAMIC 表时也不适用;

二、数据类型存储要求

https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html

2.1 数据类型存储要求

DECIMAL（和 NUMERIC）列的值使用二进制格式表示，该格式将九个十进制（基数 10）数字打包成四个字节。每个值的整数和小数部分的分开存储。九个十进制数字的每个倍数需要四个字节，“剩余”数字需要四个字节的一部分。下表给出了多余数字所需的存储空间：

2.2 日期时间类型存储要求

MySQL5.6.4 之后创建的表允许TIME、DATETIME 和 TIMESTAMP这些类型具有小数部分，这额外需要 0 到 3 个字节，具体取决于存储值的小数秒精度：

2.3 String类型存储要求

M：非二进制字符串类型的声明列长度（以字符为单位）和二进制字符串类型的字节数；
N：给定字符串值的实际长度（以字节为单位）；

可变长度字符串类型使用一个带长度的前缀+数据存储。前缀的长度根据数据类型以及前缀的值 L（字符串的字节长度）来确定需要多少字节(1-4)，。例如，MEDIUMTEXT 值的存储需要 L 个字节，加上三个字节来存储该值的长度；
要计算用于存储特定 CHAR、VARCHAR 或 TEXT 列的字节数，必须考虑该列的字符集以及是否包含多字节字符。特别是，在使用 UTF-8 Unicode 字符集时，必须记住并非所有字符都使用相同的字节数。 utf8mb3 和 utf8mb4 字符集可能分别要求每个字符需要三个和四个字节；
VARCHAR、VARBINARY 、 BLOB、 TEXT是可变长度类型，存储要求取决于以下因素：

列值的实际长度
列的最大可能长度
列使用的字符集，因为有些字符集包含多字节字符

例如，VARCHAR(255) 列可以容纳最大长度为 255 个字符的字符串。假设该列使用latin1字符集（每个字符一个字节），实际需要存储的是字符串的长度（L），加上一个字节来记录字符串的长度。对于字符串 'abcd'，L 为 4，存储要求为 5 个字节。如果同一列改为声明使用 ucs2 双字节字符集，则存储要求为 10 个字节：'abcd' 的长度为八个字节，该列需要两个字节来存储长度，因为最大长度大于 255 （最多 510 个字节）；
可以存储在 VARCHAR 或 VARBINARY 列中的最大有效字节数受行最大为 65535 字节的限制，该行大小在所有列之间共享。对于存储多字节字符的 VARCHAR 列，有效的最大字符数更少。例如，utf8mb4 字符每个字符最多可以使用四个字节，因此使用 utf8mb4 字符集的 VARCHAR 列最多可声明为 16,383 个字符；
ENUM 对象的大小由不同枚举值的数量决定。一个字节用于具有最多 255 个可能值的枚举。两个字节用于具有 256 到 65,535 个可能值的枚举；
SET 对象的大小由不同集合成员的数量决定。如果设置大小为 N，则对象占用 (N+7)/8 个字节，四舍五入为 1、2、3、4 或 8 个字节。一个 SET 最多可以有 64 个成员；

三、表列数和行大小的限制

https://dev.mysql.com/doc/refman/8.0/en/column-count-limit.html

3.1 列数限制

MySQL 规定每个表只能有 4096 列，但对于给定的表，有效最大值可能会更少。确切的列限制取决于几个因素：

表的最大行大小限制了列的数量（可能还有列大小），因为所有列的总长度不能超过此大小；
各个列的存储要求限制了行最大固定了的列数。某些数据类型的存储要求取决于存储引擎、存储格式和字符集等因素；
存储引擎可能会施加额外的限制来限制表的列数。例如，InnoDB 的每个表有 1017 列的限制；
功能键部分被实现为隐藏的虚拟生成存储列，因此表索引中的每个功能键部分都计入表总列限制

3.2 行大小限制

表的最大行大小由几个因素决定：

MySQL 表内部有 65535 字节的最大行大小限制，即使存储引擎能够支持更大的行也是65535。 BLOB 和 TEXT 虽然是大文本，但是由于它们的内容与行的其余部分分开存储，因此它们的列仅占9 到 12 个字节；
InnoDB 表的最大行大小适用于本地存储在数据库页面中的数据，在对 4KB、8KB、16KB 和 32KB innodb_page_size 设置中略小于半页。例如，对于默认的 16KB InnoDB 页面大小，最大行大小略小于 8KB。对于 64KB 页面，最大行大小略小于 16KB；
如果包含可变长度列的行超过 InnoDB 最大行大小，则 InnoDB 选择外部页外存储的可变长度列，直到该行符合 InnoDB 行大小限制。对于页外存储的可变长度列，本地存储的数据量因行格式而异；
不同的存储格式使用不同数量的页眉和页尾数据，会影响可用于行的存储量；

3.3 行大小限制案例

1）在以下 InnoDB 和 MyISAM 示例中演示了 65,535 字节的 MySQL 最大行大小限制。无论存储引擎如何，都会强制执行该限制，即使存储引擎可能能够支持更大的行：

-- InnoDB引擎
mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
                       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
                       f VARCHAR(10000), g VARCHAR(6000)) ENGINE=InnoDB CHARACTER SET latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used
table type, not counting BLOBs, is 65535. This includes storage overhead,
check the manual. You have to change some columns to TEXT or BLOBs

-- MyISAM 引擎
mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
       f VARCHAR(10000), g VARCHAR(6000)) ENGINE=MyISAM CHARACTER SET latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used
table type, not counting BLOBs, is 65535. This includes storage overhead,
check the manual. You have to change some columns to TEXT or BLOBs

列更改为 TEXT 可避免 65535 字节的行大小限制，因为 BLOB 和 TEXT 列仅占 9 到 12 字节，并且 InnoDB 变长列的页外存储避免了 InnoDB 行大小限制：

-- MyISAM中TEXT的使用
mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
       f VARCHAR(10000), g TEXT(6000)) ENGINE=MyISAM CHARACTER SET latin1;
Query OK, 0 rows affected (0.02 sec)

-- InnoDB中TEXT的使用
mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
       f VARCHAR(10000), g TEXT(6000)) ENGINE=InnoDB CHARACTER SET latin1;
Query OK, 0 rows affected (0.02 sec)

2) 可变长度列的存储包括计入行大小的长度字节。例如，VARCHAR(255) CHARACTER SET utf8mb3 列需要两个字节来存储值的长度，因此每个值最多可以占用 767 个字节:

latin1+InnaDB:
mysql> CREATE TABLE t1
       (c1 VARCHAR(32765) NOT NULL, c2 VARCHAR(32766) NOT NULL)
       ENGINE = InnoDB CHARACTER SET latin1;
Query OK, 0 rows affected (0.02 sec)

使用的是Latin1，一个字符一个字节，因此列需要 32765+2 + 32766+2 字节 <  65535；

mysql> CREATE TABLE t2
       (c1 VARCHAR(65535) NOT NULL)
       ENGINE = InnoDB CHARACTER SET latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used
table type, not counting BLOBs, is 65535. This includes storage overhead,
check the manual. You have to change some columns to TEXT or BLOBs

65535 + 2 > 65535，因此创建失败;

mysql> CREATE TABLE t2
       (c1 VARCHAR(65533) NOT NULL)
       ENGINE = InnoDB CHARACTER SET latin1;
Query OK, 0 rows affected (0.01 sec)

65533 + 2 = 65535，创建成功

3）对于 MyISAM 表，NULL 列需要在行中额外的空间来记录它们的值是否为 NULL。每个 NULL 列多占用一位，四舍五入到最接近的字节

mysql> CREATE TABLE t3
       (c1 VARCHAR(32765) NULL, c2 VARCHAR(32766) NULL)
       ENGINE = MyISAM CHARACTER SET latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used
table type, not counting BLOBs, is 65535. This includes storage overhead,
check the manual. You have to change some columns to TEXT or BLOBs

32765 + 2 + 32766 + 2 = 65535，但是对于MyISAM还需要额外的空间来存储null值，因此创建失败

4）对于 4KB、8KB、16KB 和 32KB innodb_page_size 设置，InnoDB 将行大小（对于本地存储在数据库页面中的数据）限制为略小于数据库页面的一半，而对于 64KB 页面限制为略小于 16KB

tip: 对于64KB的一半为什么是16，目前也没在官方文档找到较为详细的解释，只有下面的解释：

mysql> CREATE TABLE t4 (
       c1 CHAR(255),c2 CHAR(255),c3 CHAR(255),
       c4 CHAR(255),c5 CHAR(255),c6 CHAR(255),
       c7 CHAR(255),c8 CHAR(255),c9 CHAR(255),
       c10 CHAR(255),c11 CHAR(255),c12 CHAR(255),
       c13 CHAR(255),c14 CHAR(255),c15 CHAR(255),
       c16 CHAR(255),c17 CHAR(255),c18 CHAR(255),
       c19 CHAR(255),c20 CHAR(255),c21 CHAR(255),
       c22 CHAR(255),c23 CHAR(255),c24 CHAR(255),
       c25 CHAR(255),c26 CHAR(255),c27 CHAR(255),
       c28 CHAR(255),c29 CHAR(255),c30 CHAR(255),
       c31 CHAR(255),c32 CHAR(255),c33 CHAR(255)
       ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET latin1;
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help.
In current row format, BLOB prefix of 0 bytes is stored inline.

以上例子使用InnoDB的默认页大小16KB，因此最大行大小会限制在8KB以下，即 8 * 1024 = 8192，由于是略小于，从报错信息中也可以发现，真正的行大小是8126
而 33 * 255 = 8415 > 8126，因此创建失败。

结语

这块内容是我在MySQL官网上翻译总结而来，内容比较枯燥，并且实际开发中也并不会注意这么多，因为大多数业务表并不会离谱道到超出字段数量及行大小限制。不过在认真阅读的时候也确实对MySQL有了更深的认识，在以后创建表的时候也会有意识无意识的想到相关的规约，这对于之后的开发或纠错或多或少都会有帮助，等真正出现该类问题时至少不会手足无措。如果你不想看官网的全英文文档，就简单看看这里的介绍，当然，更建议仔细阅读官网，其内容会更加详尽。

posted @ 2023-09-20 15:33 摸鱼小天才阅读(2977) 评论(0) 编辑收藏举报

刷新页面返回顶部

摸鱼小天才