MySQL一张表最多添加多少个text字段?

1背景

当用户从 oracle 迁移到 MySQL 时，可能由于原表字段太多建表不成功，这里讨论一个问题：一个 InnoDB 表最多能建多少个 text 字段。

我们后续的讨论基于创建表的语句形如：create table t(f1 text, f2 text, …, fN text)engine=innodb;。

1.1「默认配置」

在默认配置下，上面的建表语句，N 取值范围为[1, 1017]。

为什么是 1017 这个“奇怪”的数字？

实际上单表的最大列数目是 1024-1，但是由于 InnoDB 会增加三个系统内部字段（主键 ID、事务ID、回滚指针），因此需要减3。而用于记录系统字典表也受 1023 的限制，又需要再增加三个该表的系统字段，因此每个表的最大字段数是 1023 - 3 * 2。

1.2「插入异常」

上述描述说明的是表能够创建成功的最大字段数。但是这样的表是“插入不安全”的。我们知道 text 的长度上限是 64k。而往上表中插入一行，每个字段长度为 7，就会报错：Row size too large (> 8126)。

一个 page 是 16k，空 page 扣掉页信息占用空间是 16252，需要除以 2，原因是每个 page 至少要包含两个记录。

也就是说，虽然可以创建一个包含 1017 个 text 字段的表，但是很容易碰到插入失败。

1.3「如何保证插入安全」

上面的表结构，在保证插入安全的情况下，N 的最大值是多少？

text 在存储的时候，当超过 768 字节的时候，剩余部分会保存在另外的页面（off-page），因此每个字段占用的最大空间为 768 + 20 + 2 = 788. 20 字节存储最短剩余部分的位置（SPACEID+PAGEID+OFFSET）。2 字节存储本地实际长度。

因此 N 最大值为 lower(8126 / 790) = 10。

如果我们想在创建的表的时候，保证创建的表中的 text 字段都能安全的达到 64k 上限（而不是等插入的时候才发现），那么需要将默认为 OFF 的 innodb_strict_mode 设置为 ON，这样在建表时会先做判断。

但是，在设置为严格模式后，上述建表语句的最大 N 却并非 10。

2ROW_FORMAT

在 off-page 存储时，本地占用790个字节，是基于默认的 ROW_FORMAT，即为 COMPACT，此时插入安全的 N 上限为 10。

而在 InnoDB 新格式 Barracuda 支持下，Dynamic 格式的 off-page 存储时，在 local 保存的上限不再是 768，而是 20 个字节。这样每个字段在数据页里面占用的最大值是 40byte，再需要一个额外的字节存储实际的本地长度，因此每个 text 最大占用 41 字节。

实际上很容易测试在严格模式下，建表的最大 N 为 196。以下为 N=197 时计算过程：

• 每行记录预留header 5个字节。
• 每个bit保存是否允许null，需要 upper(197/8)=25个字节。
• 三个系统保留字段 6+6+7=19.

因此总占用空间：5+25+19+41*197=8126！

也就是说，当 N=197 时，刚好长度为 8126，而代码中实现是if(rec_max_size >= page_rec_max) reutrn(error)。

就这么不巧！

3作为补充

有经验的读者可以联想到，如果我们的表中自己定义一个 int 型主键呢？此时系统不需要额外增加主键，因此整个表结构比之前少 2 字节。

也就是说，建表语句修改为: create table t(id int primary key, f1 text, f2 text, …, fN text)engine=innodb;。

则此时的 N 上限能达到 197。