MySQL一张表可以添加多少text字段?

当用户从 oracle 迁移到 MySQL 时，可能由于原表字段太多建表不成功，这里讨论一个问题：一个 InnoDB 表最多能建多少个 text 字段。我们后续的讨论基于创建表的语句形如：<code>create table t(f1 text, f2 text, …, fN text)engine=innodb;</code>。「默认配置」在默认配置下，上面的建表语句，N 取值范围为[1, 1017]。为什么是 1017 这个“奇怪”的数字？实际上单表的最大列数目是 1024-1，但是由于 InnoDB 会增加三个系统内部字段（主键 ID、事务ID、回滚指针），因此需要减3。而用于记录系统字典表也受 1023 的限制，又需要再增加三个该表的系统字段，因此每个表的最大字段数是 1023 - 3 * 2。「插入异常」上述描述说明的是表能够创建成功的最大字段数。但是这样的表是“插入不安全”的。我们知道 text 的长度上限是 64k。而往上表中插入一行，每个字段长度为 7，就会报错：<code>Row size too large (&gt; 8126)</code>。一个 page 是 16k，空 page 扣掉页信息占用空间是 16252，需要除以 2，原因是每个 page 至少要包含两个记录。也就是说，虽然可以创建一个包含 1017 个 text 字段的表，但是很容易碰到插入失败。「如何保证插入安全」上面的表结构，在保证插入安全的情况下，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。<h5 style="text-align: left;">ROW_FORMAT</h5>在 off-page 存储时，本地占用790个字节，是基于默认的 ROW_FORMAT，即为 COMPACT，此时插入安全的 N 上限为 10。而在 InnoDB 新格式 Barracuda 支持下，Dynamic 格式的 off-page 存储时，在 local 保存的上限不再是 768，而是 20 个字节。这样每个字段在数据页里面占用的最大值是 40byte，再需要一个额外的字节存储实际的本地长度，因此每个 text 最大占用 41 字节。实际上很容易测试在严格模式下，建表的最大 N 为 196。以下为 N=197 时计算过程：<ul><li style="text-align: left;">每行记录预留header 5个字节。</li><li style="text-align: left;">每个bit保存是否允许null，需要 upper(197/8)=25个字节。</li><li style="text-align: left;">三个系统保留字段 6+6+7=19.</li></ul>因此总占用空间：5+25+19+41*197=8126！也就是说，当 N=197 时，刚好长度为 8126，而代码中实现是<code>if(rec_max_size &gt;= page_rec_max) reutrn(error)</code>。就这么不巧！<h5 style="text-align: left;">作为补充</h5>有经验的读者可以联想到，如果我们的表中自己定义一个 int 型主键呢？此时系统不需要额外增加主键，因此整个表结构比之前少 2 字节。也就是说，建表语句修改为: <code>create table t(id int primary key, f1 text, f2 text, …, fN text)engine=innodb;</code>。则此时的 N 上限能达到 197。

DreamCoders/CoderGuide

内容风险标识

评论 (0)

DreamCoders/CoderGuide .gitee-modal { width: 500px !important; }

内容风险标识