面试官：MySQL 中 varchar(n) 中 n 最大取值为多少？

面试官：MySQL 中 varchar(n) 中 n 最大取值为多少？

大家好，我是小林。

上周发了一篇​​字节一面:MySQL 的 NULL 值是怎么存放的?​​，文章里面有提及这个问题：「varchar(n) 中 n 最大取值为多少？」

当时这部分内容写的不够严谨，所以我重写了这部分内容。

所以，这次就聊聊这个问题。

前置知识

要回答这个问题，首先我们得先知道 MySQL 存储一条记录的格式长什么样子。

以  Compact 行格式作为例子，它长这样：

可以看到，一条完整的记录分为「记录的额外信息」和「记录的真实数据」两个部分。

这里重点讲讲记录的额外信息，它包含 3 个部分：变长字段长度列表、NULL 值列表、记录头信息。

变长字段长度列表

用于存储一行记录中每个变长字段的长度。

「变长字段长度列表」所占用的字节数 = 所有「变长字段长度」占用的字节数之和。

举个例子，假设数据库表中有 2 个 varchar(10) 类型的字段，分别为 a 和 b，且数据库表的字符集为 ascii 字符集（1 个字符占用 1 字节）。

那么a和b字段的数据值的长度分别只需要用1字节表示就行了，因为1字节能表示最大的字节数是 255，而 varchar(10) 类型的字段最大允许存储的字节数是 10 字节，所以只需要用 1 字节表示变长字段的长度就行。

那么这种情况下的 「变长字段长度列表」所占用的字节数 = 1 字节 + 1字节 = 2 字节。

「变长字段长度列表」不是必须的，如果数据库表没有变长字段，比如字段类型都是int，那么行格式中就不需要「变长字段长度列表」。

NULL 值列表

用于标记一行记录中字段值为 NULL 的字段，二进制位的值为 1 时，代表该字段的值为NULL，二进制位的值为 0 时，代表该字段的值不为 NULL。

另外，NULL 值列表必须用整数个字节的位表示（1字节8位），如果使用的二进制位个数不足整数个字节，则在字节的高位补 0。

如果表中允许为 NULL 值的记录的个数小于等于 8 个，那么 NULL 值列表就会用 1 字节表示。

如果如果表中允许为 NULL 值的记录的个数大于8 并且小于等于 16，那么 NULL 值列表就会用 2 字节表示，以此类推。

因此，如果表中有字段允许为 NULL，那么「NULL 值列表」至少占用 1 字节空间。

「NULL 值列表」不是必须的，如果数据库表中的字段都定义成 NOT NULL，那么行格式中就不需要「NULL 值列表」。

记录头信息

记录头信息中包含的内容很多，比如记录的删除标记位，指向下一条记录的指针等等，不是本文问题的重点，所以我就不细讲了。

varchar(n) 中 n 最大取值为多少？

我们要清楚一点，MySQL 规定除了 TEXT、BLOBs 这种大对象类型之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过 65535 个字节。

也就是说，一行记录除了 TEXT、BLOBs 类型的列，限制最大为 65535 字节，注意是一行的总长度，不是一列。

知道了这个前提之后，我们再来看看这个问题：「varchar(n) 中 n 最大取值为多少？」

varchar(n) 字段类型的 n 代表的是最多存储的字符数量，并不是字节大小哦。

要算 varchar(n) 最大能允许存储的字节数，还要看数据库表的字符集，因为字符集代表着，1个字符要占用多少字节。

比如 ascii 字符集， 1 个字符占用 1 字节，那么  varchar(100) 意味着最大能允许存储 100 字节的数据。

单字段的情况

前面我们知道了，一行记录最大只能存储 65535 字节的数据。

那假设数据库表只有一个 varchar(n) 类型的列且字符集是 ascii，在这种情况下， varchar(n) 中 n 最大取值是 65535 吗？

不着急说结论，我们先来做个实验验证一下。

我们定义一个 varchar(65535) 类型的字段，字符集为 ascii 的数据库表。

CREATE TABLE test ( `name` VARCHAR(65535) NULL) ENGINE = InnoDB DEFAULT CHARACTER SET = ascii ROW_FORMAT = COMPACT;

看能不能成功创建一张表：

结果显示，创建失败了。

从报错信息就可以知道一行数据的最大字节数是 65535（不包含 TEXT、BLOBs 这种大对象类型），其中包含了 storage overhead。

问题来了，这个 storage overhead 是什么呢？其实就是「变长字段长度列表」和 「NULL 值列表」。

也就是说一行数据的最大字节数 65535，其实是包含「变长字段长度列表」和 「NULL 值列表」所占用的字节数的。

所以， 我们在算 varchar(n) 中 n 最大值时，需要减去 storage overhead  占用的字节数。

这是因为我们存储字段类型为 varchar(n)  的数据时，其实分成了三个部分来存储：

真实数据真实数据占用的字节数NULL 标识，如果不允许为NULL，这部分不需要

本次案例中，「NULL 值列表」所占用的字节数是多少？

前面我创建表的时候，字段是允许为 NULL 的，所以会用 1 字节来表示「NULL 值列表」。

本次案例中，「变长字段长度列表」所占用的字节数是多少？

「变长字段长度列表」所占用的字节数 = 所有「变长字段长度」占用的字节数之和。

所以，我们要先知道每个变长字段的「变长字段长度」需要用多少字节表示？具体情况分为：

条件一：如果变长字段允许存储的最大字节数小于等于 255 字节，就会用 1 字节表示「变长字段长度」；条件二：如果变长字段允许存储的最大字节数大于 255 字节，就会用 2 字节表示「变长字段长度」；

我们这里字段类型是 varchar(65535) ，字符集是 ascii，所以代表着变长字段允许存储的最大字节数是 65535，符合条件二，所以会用 2 字节来表示「变长字段长度」。

因为我们这个案例是只有 1 个变长字段，所以「变长字段长度列表」= 1 个「变长字段长度」占用的字节数，也就是 2 字节。

因为我们在算 varchar(n) 中 n 最大值时，需要减去 「变长字段长度列表」和 「NULL 值列表」所占用的字节数的。

所以，在数据库表只有一个 varchar(n)  字段且字符集是 ascii 的情况下，varchar(n) 中 n 最大值 =  65535 - 2 - 1 = 65532。

我们先来测试看看  varchar(65533)  是否可行？

可以看到，还是不行，接下来看看 varchar(65532)  是否可行？

可以看到，创建成功了。说明我们的推论是正确的，在算 varchar(n) 中 n 最大值时，需要减去 「变长字段长度列表」和 「NULL 值列表」所占用的字节数的。

当然，我上面这个例子是针对字符集为 ascii 情况，如果采用的是 UTF-8，varchar(n)  最多能存储的数据计算方式就不一样了：

在 UTF-8 字符集下，一个字符串最多需要三个字节，varchar(n) 的 n 最大取值就是 65532/3 = 21844。

上面所说的只是针对于一个字段的计算方式。

多字段的情况

如果有多个字段的话，要保证所有字段的长度 + 变长字段字节数列表所占用的字节数 + NULL值列表所占用的字节数 <= 65535。

这里举个多字段的情况的例子。

实验结果：

总结

varchar(n) 中 n 最大取值为多少？

一行记录最大能存储 65535 字节的数据，但是这个是包含「变长字段字节数列表所占用的字节数」和「NULL值列表所占用的字节数」。所以， 我们在算 varchar(n) 中 n 最大值时，需要减去这两个列表所占用的字节数。

如果一张表只有一个 varchar(n)  字段，且允许为 NULL，字符集为 ascii。varchar(n) 中 n 最大取值为 65532。

计算公式：65535 - 变长字段字节数列表所占用的字节数 - NULL值列表所占用的字节数 = 65535 - 2 - 1 = 65532。

如果有多个字段的话，要保证所有字段的长度 + 变长字段字节数列表所占用的字节数 + NULL值列表所占用的字节数 <= 65535。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。