技术分享 | MySQL TEXT 字段的限制

本篇文章给大家分享《技术分享 | MySQL TEXT 字段的限制》，覆盖了数据库的常见基础知识，其实一个语言的全部知识点一篇文章是不可能说完的，但希望通过这些问题，让读者对自己的掌握程度有一定的认识(B 数)，从而弥补自己的不足，更好的掌握它。

作者：kay
擅长 Oracle、MySQL、PostgresSQL 等多种数据库领域
擅长 Oracle、MySQL 性能优化、数据库架构设计、数据库故障修复、数据迁移以及恢复
热衷于研究 MySQL 数据库内核源码、分享技术文章，并拥有 Oracle OCP 认证
就职于江苏国泰新点软件有限公司，中央研究院-DBA 技术团队成员

一、背景说明

项目中有一个数据交换的场景，由于使用了很多个

ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help.

该表有 242 个字段，都是 varchar 类型，只是长度上有所区别。

二、MySQL 的限制

说明：本文仅讨论 MySQL 中，单条记录最大长度的限制，其他的暂且搁置。

无论是 MySQL 还是 Oracle，或者是 SQL Server，其实都有这么两层存在，一个是 Server 层，另一个是 存储引擎层。

其实也很好理解，可以类比一下我们 Windows 操作系统，比如常说的把 D 盘格式化成 NTFS 或者 FAT32，这个 NTFS 或者 FAT32 就可以理解成数据库中的存储引擎。

那为什么以前在用 SQL Server 或者 Oracle 的时候几乎没什么接触存储引擎这个概念呢？因为这两家都是闭源数据库，底层怎么实现的你也不知道，装好了就用呗！但是 MySQL 不一样，开源的东西，人人都可以看源码。只要你实现了那些接口，你就可以接入到 MySQL 中，作为一个存储引擎供 MySQL 的 Server 层使用。

说完这个概念，下面我们再说一下这个最大长度的限制。

官方文档相关说明 - Limits on Table Column Count and Row Size
https://dev.mysql.com/doc/ref...

2.1 MySQL Server 的长度限制

The internal representation of a MySQL table has a maximum row size limit of 65,535 bytes.

MySQL Server 层的限制比较宽，你的一条记录不要超过 65535 个字节即可。

有的人就问了，怎么可能啊？我明明可以往 MySQL 里面放好几百兆的附件啊，咳咳...这个后面会提到。

2.2 InnoDB 的长度限制

InnoDB 作为现在官方唯一还在继续开发和支持的存储引擎（下一个版本 MySQL 8.0 中就默认看不到原先的 MyISAM 了），其长度限制比较严格，其大致的算法如下

一条记录的长度，不能超过 innodb_page_size 大小的一半（实际上还要小一点，因为要扣除一些页中元数据信息）

即默认MySQL官方推荐的 16K 的页大小，单条记录的长度不能超过 8126Byte。

为什么会有这种限制呢，其实也很好理解，InnoDB 是 以B+树来组织数据 的，假设允许一行数据填满整个页，那么 InnoDB 中的数据结构将会从 B+树退化为单链表，所以 InnoDB 规定了一个页至少包含两行数据。

那这就好理解了，项目中给出的建表语句的字段中，有好几十个 varhcar(1000) 或者 varchar(2000)，累加起来已经远远超过了 8126 的限制。

2.3 字段个数的限制

同样，除了长度，对每个表有多少个列的个数也是有限制的，这里简单说一下：

1.MySQL Server 层 规定一个表的字段个数最大为 4096；
2.InnoDB 层 规定一个表的字段个数最大为 1017；

[官方文档相关说明 - Limits on InnoDB Tables]
https://dev.mysql.com/doc/ref...

至于为什么有这个限制，其实可以不用深究，因为是代码中写死的。

至于原因，个人猜测和 MySQL 的定位有关系，MySQL 一直定位于 OLTP 业务，OLTP 业务的特点就是短平快，字段数过多或者长度太长，都会影响 OLTP 业务的 TPS（所以那些拿 MySQL 存附件的同时，还在抱怨 MySQL 性能差的同学，是不是该反思一下了？）

三、TEXT 类型的字段

回到我们项目中的问题，既然那么多 varchar 超过了限制，那按照提示，我们直接把所有字段改成

$5 + ceil(x/8) + 6 + 6 + 7 + x * 41 

最终我们可以计算出符合该公式的 x 的解为 197。

那是不是就可以插入 197 个的 text 呢？ 我们又做了一个测试，发现还是失败的（What's The F**K？）。

最终通过源码我们找到了问题的答案（以当前最新的 5.7.20 为例）:

/* Filename:./storage/innobase/dict/dict0dict.cc 第 2504 行 */
if (rec_max_size >= page_rec_max) {   /* 居然是 >= */
    ib::error_or_warn(strict)
        name
        name
        

通过代码我们可以发现，不能刚好等于最大值，所以在当前 MySQL 版本（5.7.x）中，极端情况下，可以存储 196 个 TEXT 字段。

同时我们也进行了测试，的确可以创建有且仅含有 196 个 TEXT 字段的表。

我们可以构造一下

create table c_196( f1 text,
f2 text,
f3 text,
......
f196 text
);
-- 197 个字段的的类似，多增加 f197 text 字段

mysql> source c_197.sql
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.

mysql> source c_196.sql
Query OK, 0 rows affected (0.01 sec)

mysql> select count(*) from information_schema.columns where table_name='c_196' and data_type='text';
+----------+
| count(*) |
+----------+
|      196 |  -- 共 196 个字段
+----------+
1 row in set (0.00 sec)

备注：能这样保存 196 个 TEXT 字段，并不代表我们推荐这样做，如果业务上达到了这种限制，建议业务上做一定的拆分。

3.3 关于 innodb_strict_mode

细心的同学可能会想，如果所有 TEXT 字段都是以溢出页（overflow page）的方式存储，本地记录都是以指针（20 个字节）进行存储，那不是可以存储更多的字段呢？

确实是的，但是 MySQL 现在开启了严格模式（innodb_strict_mode=on），由于 MySQL 层面无法保证所有数据都是存储在溢出页（业务才能决定），所以在严格模式下，宁愿牺牲字段个数的上限，也要确保所有的 insert 或者 update 能成功执行。

那我们关闭严格模式，看一下，究竟能存储多少个 TEXT 字段呢？继续套公式

$5 + ceil(x/8) + 6 + 6 + 7 + x * 20 

最终我们可以计算出符合该公式的 x 的解为 402。但是事实真的如此么？

同样我们也可以构造一下

create table c_402( f1 text,
f2 text,
f3 text,
......
f402 text
);

mysql> source c_402.sql
Query OK, 0 rows affected, 1 warning (0.02 sec)  
-- 虽然成功了，但是有一个警告

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 139
Message: 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.
1 row in set (0.00 sec)

mysql> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| c_402         |
+---------------+
1 row in set (0.00 sec)

mysql> select count(*) from information_schema.columns where table_name='c_402' and data_type='text';
+----------+
| count(*) |
+----------+
|      402 |
+----------+
1 row in set (0.00 sec)

看到上面的执行结果，虽然 create table 执行成功了，通过

mysql> source c_1017.sql
Query OK, 0 rows affected, 1 warning (0.04 sec) -- 一如既往的Warnings

mysql> select count(*) from information_schema.columns where table_name='c_1017' and data_type='text';
+----------+
| count(*) |
+----------+
|     1017 |
+----------+
1 row in set (0.00 sec)

mysql> source c_1018.sql
ERROR 1117 (HY000): Too many columns

但是这样做了以后，虽然建立了 1017 个 text 列，如果业务上进行 insert 或者 update 的时候，mysql 无法保证能执行。

所以项目上建议还是保持默认值，将 innodb_strict_mode 设置为 on（公司的 bin 包中已经默认开启）

四、总结

很多同学看到这里，可能会想，MySQL 弱暴啦，怎么这么多限制啊，你看 Oracle 多强啊......

其实，针对项目中这种超多字段，同时又只能用 MySQL 的场景下，我们可以使用 MySQL 5.7 中最新推出的 JSON 类型的字段，这样 N 多数据只算在一个 JSON 字段哦，同时还有丰富的 JSON 函数予以支持，业务上使用起来其实还是比较方便的（5.6 等版本可以存在 blob 中，只是需要业务自己做 json_encode/json_decode 等操作）。

这里更要强调的是，MySQL 作为一个绝大部分互联网公司都在广泛使用的 OLTP 型数据库（微信支付的交易库就运行在 MySQL 社区版之上），这些成功案例已经证明了 MySQL 是一个优秀的工业级数据库。

当然除了他自身在不断进步以外，同样需要我们从业务上进行良好的表结构设计，编写规范的 SQL 语句以及采用合适的集群的架构，才能发挥出 MySQL 自身的潜力，而不是一味的和 Oracle 进行对比，拿 Oracle 的优点和 MySQL 的缺点进行比较，这样无法做到客观和公正。

终于介绍完啦！小伙伴们，这篇关于《技术分享 | MySQL TEXT 字段的限制》的介绍应该让你收获多多了吧！欢迎大家收藏或分享给更多需要学习的朋友吧~golang学习网公众号也会发布数据库相关知识，快来关注吧！