Mysql-一行能存多少数据？

1024创新实验室 2021-05-10

3728

Mysql一行能存多少数据？碰到Row Size相关错误怎么解决？难道就直接百度查找解决方法就算完了吗？不，你要了解的还不是这么一点点。

PART

行限制

错误一：Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535.

错误二：Row size too large (> 8126).

大家在日常开发过程中是否经常遇到这样的错误，碰到这样的错误大家的解决方法不外乎直接百度进行修改，那么大家知道为什么Mysql会报这样的错误吗？

在分析这两个错误之前我们首先应该来区分下这两个错误各是在mysql的什么位置发生的？通过阅读Mysql官方文档我们可看到下面这两句话：

The internal representation of a MySQL table has a maximum row size limit of 65,535 bytes, even if the storage engine is capable of supporting larger rows. BLOB and TEXT columns only contribute 9 to 12 bytes toward the row size limit because their contents are stored separately from the rest of the row.
The maximum row size for an InnoDB table, which applies to data stored locally within a database page, is slightly less than half a page for 4KB, 8KB, 16KB, and 32KB innodb_page_size settings. For example, the maximum row size is slightly less than 8KB for the default 16KB InnoDB page size. For 64KB pages, the maximum row size is slightly less than 16KB.

通过这两句话我们可以看到错误一时MysqlServer层面引发的错误，而错误二是由InnoDB引擎引发的。

为什么错误二是由InnoDB引发的呢？

通过上面第二段英文内容我们可以大体看到如下信息：

1，在Mysql中InnoDB默认的页大小是16K

2，一个页中最多存放低于一半页大小的数据

3,64K页大小的最大航大小限制是16K

看到这里是不是很明显，默认16K的页大小，存储的行数据应该低于8K，也就是8*1024=8192字节（为什么是8192而不直接是8126这个咱们以后再说）

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

PART

通过现象看本质

复现错误一：

CREATE TABLE test(
     name VARCHAR(65535)
) ENGINE=InnoDB CHARACTER SET ascii;
[Err] 1118 - 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

注意VARCHAR能存储的最大字符长度，并不是全是65535，而是与字符集所能承载的最大字节数据有密切关系，测试代码采用的是ASCII字符集，每个字符占1个字节，所以此处最大是65535。

重要事情说三遍：

VARCHAR后的数字是字符的长度！

复现错误二：

CREATE TABLE test(
     name VARCHAR(10000)
) ENGINE=InnoDB CHARACTER SET ascii;
受影响的行: 0
时间: 0.021s

为什么错误二复现失败了？不是说不能大于8126个字节吗？

为了探明这个问题，我们应该想到，InnoDB是以页为基本单位与磁盘交互的，那数据在页中又是怎么来存储的呢？

我们继续看官方文档，在官方文档中我们可以了解到以下几点信息：

InnoDB支持四种行格式REDUNDANT,COMPACT,DYNAMIC,COMPRESSED
InnoDB 的默认行格式是DYNAMIC
DYNAMIC存储可变字符串使用的方式和COMPACT格式基本相同，动态行格式不会在记录的真实数据处存储字段数据的前768个字节，而是把所有数据都存储到其他页面中，只记录存储在其他页面的地址。

(https://dev.mysql.com/doc/refman/8.0/en/innodb-row-format.html)

PART

COMPACT行格式

通过官方文档我们可以看到compact行格式包含上面几类信息分别是：固定5个字节长度的记录头信息，可变字段的长度列表，空值列表，以及字段值列表。

记录头：

这一部分主要用于描述本行记录的相关信息，例如是否删除，下一个记录的位置，是否是叶子节点等等，有兴趣的同学自行查阅相关资料啊。

可变字段长度列表：

这一部分主要用于存储边长字段所占的字节数，那具体是使用一个字节还是两个字节来表示字段所占字节数，需要进行一定的换算。换算规则有点小麻烦主要依据实际存储实际存储字节数、最多存储字符数、字符集最大字节数换算。

举个栗子:

例如使用utf8编码，字段类型是varchar(20),实际存储字节数是10.

1，utf8最大字节数是3,3*20<255 使用1个字节表示

2，如果字段类型是varchar(100)，3*100>255,这个时候就需要判断下实际存储字节数是否大于127，如果大于127使用两个字节表示，小于使用一个字节表示

字段值列表:

在这一部分存储的的数据中，除了存储列本身数据外还会存储6字节长度的事务id（transaction ID）以及7字节长度的回形指针（roll pointer），另外对于未设置主键的表数据还会存储6字节长度的行id（row id）

在官方文档描述compact行格式的最后我们还看到了这样一段话:

Fixed-length columns greater than or equal to 768 bytes are encoded as variable-length fields, which can be stored off-page

对于超过768字节的字符串，超出的部分会被存储在溢出页中。

看到这里是不是就很自然的明白了上节中复现错误二的语句为什么没有复现。

一，对于compact行格式，虽然我们指定的长度超过了8126，但对于Innodb来说只会将768的字节内容存储到本行，剩下的数据会在本行生成一个20字节长度的溢出页地址用于指向。

二，对于dynamic行格式，可变长度字段是不存储真是数据的，只存储数据所在的地址。

那如何来重现呢，既然最大限制不能超过8126，而8126的限制是由数据不能超过也大小的一半而来的，那我们设置固定长度的字段使其合计行大小超过8k即可。

我们假设每个字段固定255个字符，16k数据页最多不超过8k，那咱们建（8192/255）+1个字段不就行了

CREATE TABLE test (
            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 CHARACTER SET ascii;
[Err] 1118 - Row size too large (> 8126).