通过数据库底层ibd文件来理解char(n) varchar(n)中n的含义

前言

不知你是否还在纠结char(n) 和 varchar(n)中的n到底是最多存储 n个字符 还是 n个字节 这样的问题？

那么本文将会通过mysql数据插入的场景举例 以及 查询底层数据库的存储文件xx.ibd来回答你这个问题。好的，那我们就准备开始吧！

数据插入场景实践

伙计们，我准备新建两张表，分别仅存储char 和 varchar类型的数据来验证是【字符还是字节】的疑惑。

下面这张图是我创建的测试表：

CREATE DATABASE dbtest

CREATE TABLE a1(
-- 定长 n=5
one char(5)
)

CREATE TABLE a2(
-- 变长 n=5
two VARCHAR(5)
)

场景一：char插入测试

往只存在char列的表a1分别插入5个英文、中文以及大于五个的字符串，看看结果是什么？

-- 五个英文字符
INSERT INTO a1(one)
VALUES("abcde")

-- 五个中文字符
INSERT INTO a1(one)
VALUES("我爱代码丰")

-- 大于五的字符
INSERT INTO a1(one)
VALUES("我不爱代码丰")

char插入结果：

设置char(5)，五个中文字符可以插入：

同样，五个英文字符可以插入：

当然了，超过五个的字符就会提示过长，插入失败：

场景二：varchar插入测试

小伙伴们，同样往只存在varchar列的表a2分别插入5个英文、中文以及大于五个的字符串，看看结果是什么？（猜都猜到肯定是和char类型的插入是一样的）

INSERT INTO a2(two)
VALUES("abcde")

INSERT INTO a2(two)
VALUES("我爱代码丰")

INSERT INTO a2(two)
VALUES("我不爱代码丰")

varchar插入结果：

同样，在插入大于设置的字符串长度的数据，则插入失败

总结n的含义

在MySQL4.0版本以下，varchar(5)：指的是5个字节
在MySQL5.0版本以上，varchar(5)：指的是5个字符

原来是这样嗦，现在常用的MySQL数据库的版本都是5.7和8.0，也就是n就是代表的是字符而不是字节。

我又出现了一个问题：

varchar(n)最多可以插入的字符串是多大的？

换句话来说varchar(n)中的n最多可以设置到多少？

65535？65532？【65535是个什么数字，见场景二：一条行记录最大的存储空间是65535字节】

还是其他？

场景三：varchar最大插入字符串是多少？

首先 先要对表的一条记录的最大存储空间有了解：

The maximum row size for the used table type, not counting BLOBs, is 65535

这句话的意思是：

MySQL对一条记录占用的最大存储空间是有限制的，除BLOB或者TEXT类型的列之外， 其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过65535个字节。

注：其实就可以简单理解为插入一条行记录，这个记录底层最大只能以65535个字节去存储

其次要对不同编码的多少字节代表一个字符有清楚的了解

例如ascii中A字符是码位是65（以二进制表示则是：1000001），即一个字符可以用一个字节表示（8bit）

而UTF-8编码中，一个中文字符需要三个字节（24bit）来表示

ok，了解到65535以及字符编码的含义后，我们接着举下一个场景来验证varchar(n)

这个神秘n到底可以设置为多少？

例一：ascii编码的可空例子

由于我们知道ascii编码一个字符就是用一个字节来表示，

所以假如建表的字符集为ascii

那么就意味着插入一条记录

字符数和65536上限的字节数是一比一的关系，也就是假如设置varchar（65535），那么实际代表的就是插入的数据的字符串的长度也是65535（暂不考虑能否插入成功）

这里也能对于第一次接触的朋友有一点绕，大白话理解就是：

一行数据上限就是65535字节

设置varchar（100）就是表示我想要插入的字符串长度为100，并且由于是ascii编码，那么底层消耗的也是100个字节

好的，下面请看例子：

CREATE TABLE b1(
three VARCHAR(65535)
)CHARSET=ascii

CREATE TABLE b1(
three VARCHAR(65532)
)CHARSET=ascii

插入结果：

 ascii插入结果分析：

你可以从上图发现，假如字符集设置为ascii是不能够设置为最大的65535的，并且只能设置为65532（少了三个字节）这是为什么呢？经过查询文档发现：

65535个字节除了列本身的数据之外，还包括一些其他的数据，以Compact行格式
为例，比如说我们为了存储一个VARCHAR(M)类型的列，除了真实数据占有空间以外，
还需要记录的额外信息。

即：

两个字节的可变长字段长度+一个字节的NULL值标志

这样就可以解释清楚了吧，为什么少三个字节，因为这三个字节需要存储其他的额外信息！

例二：ascii编码的非空例子

假如字段设置为NOT NULL，那还是最多设置为65532吗？

有了例一的铺垫，相信例二你也是很快就能理解

答案：不是
因为显示设置字段为NOT NULL后，就不需要额外信息中的NULL值标识符了，所以可以插入65533
【少两个字节，就是存储两字节的变长字段的长度】

例三：UTF8编码的简单例子

例一例二是关于ascii编码的例子，例三则是关于UTF-8编码的例子，两者之前的区别就是UTF-8是使用三个字节去存储一个字符（ascii是一个字节存储一个字符）

大白话理解：

假如此时编码是UTF-8则

varchar（100）实际占用是300个字节（100个字符X3）（n代表的一直都是字符哟）

我们依然是举一个例子来看看是否是这样：

CREATE TABLE c1(
four VARCHAR(65533)
)CHARSET=utf8

 结果分析：

确实如此，提示最大设置的字符串长度为21845（65535 / 3）

同样由于额外信息的占位，最大设置不能为21845，而是21844

（65536-1【NULL值的1字节】-2【变长字段长度的两个字节】 / 3）

例四：UTF8编码的复杂例子（包含多字段）

相信看到这里的小伙伴已经明白在UTF-8中的表只存在varchar单字段的n的设置最大长度，让我们引申一下，假如一张表不仅仅是存在varchar字段，还存在其他的字段呢？（int char等……）

其实也是很简单的，只要理解了一行记录最大的字节为65535，那么我们只需要减去其他固定长度的字段所占的字节，不就是我们剩下的那个变长字段的字符数的设置上限了吗

哈哈哈，很简单的啦

例子如下：

create table d4(
a int, b char(30), c varchar(21812)
) charset=utf8;
-- 设置了固定长度的int char和变长的varchar 

 

结果分析：

在表中存在int char等其他固定长度的字段的时候，只需要减去他们所占的字节数，就是varchar字符串长度设置的上限！

（65535-1-2-4-30*3）/ 3 =21812

1 - > NULL值列表的一个字节

2 - > varchar（n）的可变长度占用的2字节 

4 - > int固定长度占用4字节

30 * 3 - > char（30）在utf-8编码下占用90字节

/3 - > utf-8的原因

通过底层ibd文件去理解真实的存储

我们还是虚拟一个场景：

通过HEX fiend（mac软件）打开 /var/lib/mysql/你的库/表. 下面的XX.ibd文件 

例如：如下图所示

 04 08   可变长字段列表（逆序排列：col2当前存了8个字符 col1当前存了4个字符）

 00         NULL值列表，由于当前没有插入为null的数据，所以为00

61 62 63 64 65 66 67 68 -> 插入的8个字符的变长数据abcdefgh

65 72 69 63 -> 插入的4个字符的变长数据eric

63 6F 64 65 20 20 20 20  ->插入的8个字符的定长数据 code 

注：为什么出现4个重复的20

20（16进制展示）对应的十进制是32 对应的ascii码就是空

由于我们字段设置的为char(8)，插入不满8个字符的字符串后，会使用【空】占位

至于从记录可变长字段，NULL值列表到真实数据之间的一堆数据是什么，这里仅给出参考图片，假如有兴趣的朋友请参考其他博主的博客！

给朋友们留一个问题呗？

为什么NULL值列表和可变长字段列表分别仅用1个字节和2个字节就可以存储下的？

假如varchar类型的字段在一张表中设置了很多很多，2个字节怎么存储得下他们的长度的？

解答：

参考下这篇博主的文章吧（我也没搞懂 哈哈哈哈哈）

(67条消息) mysql 创建表字段长度范围_MySQL原理 - InnoDB引擎 - 行记录存储 - Compact 行格式_weixin_39960920的博客-CSDN博客