面试官：说一说 mysql 的 varchar 字段最大长度？
在 mysql 建表 sql 里，我们经常会有定义字符串类型的需求。
比方说 user 表里的名字，就是个字符串。mysql 里有两个类型比较适合这个场景。
char 和 varchar。
声明它们都需要在字段边上加个数组，比如 char(100)和 varchar(100)，这个 100 是指当前字段能放的最大字符数。
char 和 varchar 的区别在于，varchar 虽然声明了最大能放 100 个字符，但一开始不需要分配 100 个字符的空间，可以根据需要慢慢增加空间。而 char 一开始声明是多少，就固定预留多少空间。
所以，varchar 比起 char 更省空间，一般没啥大事，大家都爱用 varchar。
那问题来了，声明 varchar 字段时，它的最大长度是多少呢？
相信大家应该听说过 varchar 字段的最大长度是 65535 吧。
没听过也没关系，你现在听到了。
但实际上是这样吗？
我们来做个实验。
varchar 最大值是多少
我们直接拿 65535 来试一下。
长度为 65535 的 varchar 报错
很明显报错了。
报错内容也说了, 由于列长度过大导致报错，最长是 16383。
把上面的 65535 改成 16383，确实是成功了。
哦？所以说 varchar 最大值是 16383？
当然不是。
这其实还有好几个因素影响这这个最大值。
不同字符集的影响
varchar 里放的是字符串，而字符串看起来可以是英文字母，也可以是数字或中文。但不管怎么样，都可以把这样的中英文数字转成二进制的 01 串。
按照一定规则把符号和二进制码对应起来，这就是编码。而把 n 多这种已经编码的字符聚在一起，就是我们常说的字符集。
建表语句里有个 CHARSET，这里填的是字符集。
不同的字符集要求使用的字节个数也不同，我们可以通过 show charset; 看到 mysql 支持哪些字符集，以及这些字符集里存储一个字符所需的最大字节数（Maxlen）。
查看 mysql 支持哪些 charset
我们尝试下把建表 sql 语句里的 CHARSET 改一改，比如改成 utf8mb3。
我们再执行下，会发现，最大值又不一样了。
utf8mb3 下的报错
并且，上面虽然提示 max=21845，但要是真执行起来会发现还是报错。在改为 21844 之后才成功。
不讲武德。
再把字符集改为 latin1。会发现，最大值会是 65533。
varchar 为 65533 时创建成功
这里渐渐可以发现规律。
utf8mb4 的 maxlen=4，对应 varchar 最大长度=16383。4*16383 = 65532。
utf8mb3 的 maxlen=3，对应 varchar 最大长度=21844。3*21844 = 65532。
latin1 的 maxlen=1，对应 varchar 最大长度=65533。 1 * 65533 = 65533。
也就是说 varchar 边上的长度代表的是这一列能放的最大字符数，而 maxlen 代表单个字符占用的最大字节数。相乘的结果很接近 65535。说明 65535 是指的字节数，而不是字符数。
也就是说 varchar 的最大长度，根据选择的字符集的不同，会有区别。
总的来说接近于 65535 除以 字符集的 maxlen。
但其实这样还不够严谨。还有其他影响因素。
是否可以为 NULL 的影响
上面的建表语句里声明了 test 字段都是NOT NULL，也就是非空，如果我们将这个改成可以为 NULL，再用 CHARSET=latin1 去试试。这时候就会发现，前面 NOT NULL 的时候最大能使用 65533 去建表，现在报错了。
改成 65532，就能成功了，也就是最长长度少了 1 个字节。
是否为 NULL 的影响
这是因为一个字段是否为 NULL 这件事情，是需要一个字节去记录下来的。
而当字段为 NOT NULL 的时候，则可以省下这个字节。
列数的影响
上面提到的情况都是在表里只有一列时的结果，当我们表里有更多的列时，我们会发现 varchar 的最大值还会有变化。比如同样还是 latin1 字符集，我们再增加一列 varchar 类型，并且用的还是前面允许的最大值 65533。
结果发现这次会失败。
两个 varchar 列的情况
查了一下资料发现，原来 65535 是 mysql 单行的最大长度（不包含 blob 和 text 等类型的情况下）
mysql 表里单行中的所有列加起来（不考虑其他隐藏列和记录头信息） ，占用的最大长度是 65535 个字节。
注意上面加粗的部分，加起来不超过 65535。
比如如果还有 int 的列，那它占用 4 个字节，bigint 占用 8 个字节，字段越多，留给单个 varchar 列的空间就越少。
因此，前面提到的 varchar 的最大长度，接近于 65535 除以 字符集的 maxlen，但前提是只有一列 not null 的 varchar 类型的字段。
为什么不是 65535 而是 65533？
不过问题又来了，上面建表 sql 里，不管是那种字符集，最后得到的字符数都约等于 65533。
但数据库单行最大值应该是 65535。65535 - 65533 = 2 。这里面还差了个 2，为什么呢？
这就要聊一下 mysql 单行里数据到底是怎么存储的。
数据表行存储的格式
我们可以通过 show table status 命令，查看到当前表格使用的行格式。
查看到当前表格使用的行格式
通过上面的 Row_format 字段可以看到这个表用的是 Dynamic 行格式。
事实上，现在的 mysql 数据表一般都是采用 Dynamic 行记录格式。
我们来看下Dynamic行格式长什么样子。
Dynamic 行记录格式
Dynamic 格式将行记录分为两部分，分为是行记录的额外信息和行记录的真实数据。
行记录的额外信息：
变长字段长度列表：指的是 varchar，text，blob 这种类型，它们属于变长字段，这里表示的就是这些字段的长度。
NULL 值列表：用来记录当前行里哪些列是为 null 的。如果全部列都是 not null 的话，那就不需要有这个字段。
记录头信息：这是固定 5 个字节，用来记录一些特殊的信息，比如这一行是否被删了，这一行在这个 16k 的数据页内是不是最小的，以及指向下一条记录的指针之类的一些信息，不需要太关注。
行记录的真实数据：
里面放的就是一行里，每一列的真正内容。除了我们建表时里涉及到的列以外，还有一些隐藏列。
比如 Row_ID，这个是在建表是没有声明主键时，数据表自动会生成的隐藏主键。另外还有trx_id字段，用于记录当前这一行数据行是被哪个事务修改的，和一个roll_pointer字段，这个字段是用来指向当前这个数据行的上一个版本，通过这个字段，可以为这行数据形成一条版本链，从而实现多版本并发控制（MVCC）。有没有很眼熟，这个在之前写的文章里出现过。
隐藏列有哪些
所以我们回过头来看我们建的表，当只有一列 not null 的 varchar 字段时，行记录长下面这样。
单条 varchar 数据的 Dynamic 行记录格式.drawio
前面提到，行最大值 65535 字节是不包含隐藏列和记录头信息的，所以其实是指上图中红色的部分。
而最左边的变长字段长度列表中，为了表示 varchar 列的长度，占用了两个字节，也就是 16 位，2 的 16 次方，最大可以表示 65535 的长度，正好足够用来表示 varchar 列当前的长度是 65533。
所以 65535 - 65533 = 2 。这里面差的 2，是用来存 varchar 字段长度去了。
一个页才 16k，怎么保存 65533（64k）数据？
之前的文章里其实多次提到了 mysql 底层是以页的形式去存储数据的，而一个页固定 16k，而一个 varchar 字段最大能放 65533 字节数据，换算一下大概是 64k，整整 4 个 16k 的页。
页结构
这里面是怎么实现的？
对于这种情况，其实行数据里针对这个超大的 varchar 字段只保存个 20 字节的指针（实际上是个偏移量），这个指针会指向新的页（off page），这些页里保存的是实际的 varchar 字段里的 65533 字节数据。这种由于字段过长导致需要额外的页来保存数据的现象叫行溢出。
行溢出
大于 64k 的字符串该怎么处理？
如果离谱点，数据量更大，比 64k 还大，这时候就不能继续用 varchar 了，需要改用 text 和 blob 类型字段。
而 text 和 blob 类型本身也是分 TINY、MEDIUM，LONG 三个档位的，对应着不同的数据长度，最大到 4G 左右。
像下面这样就可以将数据类型定义为 LONGTEXT。
而他们的存储方式也跟 varchar 的情况类似，只保存 20 个字节的指针，实际数据保存在其他溢出页里。
以前我们查某一行数据，他们都在一个 16k 的数据页里，查询时只要一次磁盘 IO 就能将这个数据页读取出来。
当一个数据库里某行数据里有个特别大的字符串时，我们如果还想把整行数据给读出来，那我们还得把 off page 的数据给全部读出来，这意味着更多的磁盘 IO，性能就更差了。
为了规避这个问题，我们写 select sql 的时候，如果发现某列字段，是个特别长的字符串时，能不读它就尽量不加到 select 里，这也是为什么大家不建议使用select * from table的原因。
blob 和 text 的区别
一般来说，blob 和 text 都可以用来放超长字符串。但它们会有一点点区别。
我们知道字符集（charset）下还有个校对规则（collation）的概念，比如同样是 a，大写 A 和小写 a 能不能算作是一个字符，这会影响比较和排序，collation 就是定义这个规则用的。
blob 没有字符集的概念，而 text 有。这意味如果用 blob 来存文本的话，就没法用字符集的校对规则来排序和做比较。
还有一个区别，blob 还能保存二进制数据，比如压缩过的文本数据，图片或者视频，别笑，虽然不合适，但我确实见过有人拿它来保存视频。。。
总结
现在的 mysql 数据表一般采用 Dynamic 行记录格式。它由行记录的额外信息和行记录的真实数据组成。
mysql 表里单行中的所有列加起来（不考虑其他隐藏列和记录头信息） ，占用的最大长度是 65535 个字节。
如果数据表里只有一列 not null 的 varchar 字段，它的最大长度，接近于 65535 除以 字符集的 maxlen。
如果要存放大于 64k 的字段数据，可以考虑使用 longtext 和 longblob 等类型。
mysql 的数据页大小是 16k，为了保存 varchar 或者 text，blob 这种长度可能大于 16k 的字段，在 Dynamic 行格式中，会只保留 20 个字节的指针，实际数据则放在其他溢出页中。为了将它们读取出来，会需要更多的磁盘 IO。
blob 和 text 很像，但 blob 没有字符集的概念，并且还能存放二进制的数据，比如图片或视频，但实际上图片和视频更推荐放在对象存储（Object Storage Service，简称 oss）中。
原文：https://mp.weixin.qq.com/s/RPTalBoELQuJNNzOu1OA3w
如果感觉本文对你有帮助，点赞关注支持一下，想要了解更多 Java 后端，大数据，算法领域最新资讯可以关注我公众号【架构师老毕】私信 666 还可获取更多 Java 后端，大数据，算法 PDF+大厂最新面试题整理+视频精讲
领取资料添加小助理vx：bjmsb2020 2020-12-19 加入
Java领域;架构知识;面试心得;互联网行业最新资讯
评论