说说 VARCHAR 背后的那些事

2023-12-12 08:54:22

在使用MySQL的过程中，在存储字符串时，大家或许都有过这样或那样的困惑，譬如：

1. 对于固定长度的字符串，为什么推荐使用 CHAR 来存储？

2. VARCHAR 可设置的最大长度是多少？

3. 给定一个字符串，怎么知道它的空间使用情况？

4. 创建索引时，提示“Index column size too large. The maximum column size is 767 bytes”，该如何解决？

5. VARCHAR 为何要按需设置？VARCHAR(50) 和 VARCHAR(500) 有什么区别？

下面就这些问题做一个系统的分析。

1. CHAR与VARCHAR的区别

两者都可用来存储字符串。只不过 CHAR 常用来存储固定长度的字符串，VARCHAR 常用来存储可变长度的字符串。为什么要这样区分呢？

首先看下面这个表格。CHAR(4) 和 VARCHAR(4) 的存储对比。

值	CHAR(4)	存储需求（字节）	VARCHAR(4)	存储需求（字节）
‘‘	‘ ‘	4	‘‘	1
‘ab‘	‘ab ‘	4	‘ab‘	3
‘abcd‘	‘abcd‘	4	‘abcd‘	5
‘abcdefgh‘	‘abcd‘	4	‘abcd‘	5

基于表格的内容，我们可以得出以下结论：

对于 CHAR(4) ，

1. 无论插入什么值，存储需求都是不变的，固定4个字节。

2. 在实际存储的时候，对于不足4字节的值，右边会以空格填充。

对于 VARCHAR(4) ，

1. 存储的需求与插入的值有关。

2. 存储的需求 = 字符串所占的字节数 + 1。为什么要加1呢？这个与 VARCHAR 的实现有关，为了实现“按需分配”的目的，它需要额外的字节来表示字符串的长度。

所以，对于固定长度的字符串推荐使用 CHAR 来存储，相对于 VARCHAR ，前者会少用一个字节。

另外，‘abcdefgh‘被截断为‘abcd‘进行存储，是在 SQL_MODE 非严格模式下。具体什么是 SQL_MODE 的非严格模式，可参考：使用MySQL，SQL_MODE有哪些坑，你知道么？

2. VARCHAR(M) 能设置的最大长度

M 限制了 VARCHAR 能存储的字符串的最大长度，注意，是字符，不是字节，其有效值范围为 0 ~ 65535。虽然可设置的范围是 0 ～ 65535，但 M 真的就能设置为65535 吗？

看下面这个测试。

mysql> create table t (c1 varchar(65535)) charset latin1;
ERROR 1118 (42000): 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

mysql> create table t (c1 varchar(65534)) charset latin1;
ERROR 1118 (42000): 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

mysql> create table t (c1 varchar(65533)) charset latin1;
ERROR 1118 (42000): 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

mysql> create table t (c1 varchar(65532)) charset latin1;
Query OK, 0 rows affected (0.06 sec)

由此来看，在 latin1 字符集下，M 最大就只能设置为 65532。

其实不然，再看下面这个示例。

mysql> create table t (c1 varchar(65533) not null) charset latin1;
Query OK, 0 rows affected (0.06 sec)

mysql> create table t (c1 varchar(65534) not null) charset latin1;
Query OK, 0 rows affected (0.06 sec)

如果将列定义为NOT NULL，M 最大可设置为 65533。

上面演示的是latin1下的使用场景，如果是其它字符集呢？

mysql> create table t (c1 varchar(65533) not null) charset utf8mb4;
ERROR 1074 (42000): Column length too big for column ‘c1‘ (max = 16383); use BLOB or TEXT instead

mysql> create table t (c1 varchar(65533) not null) charset utf8;
ERROR 1074 (42000): Column length too big for column ‘c1‘ (max = 21845); use BLOB or TEXT instead

基于报错信息，可以看出，对于utf8mb4字符集，M最大只能设置为16383。对于utf8字符集，M最大只能设置为21845。这两个数值是怎么计算出来的呢？

在utf8mb4字符集中，最多需要4个字节来表示一个字符，所以 65535 / 4 = 16383 。而在utf8字符集中，最多需要3个字节来表示一个字符，所以 65535 / 3 = 21845。

由此来看，在设置 M 的大小时，起决定作用的并不是 M 的有效值范围（0 ~ 65535），而是 M * 字符集的最大字节数不能超过65535个字节。

为什么不能超过 65535 字节呢？因为MySQL限制了一条记录的最大长度就是 65535 字节。

除此之外，对于VARCHAR，在实际设置时，还需考虑以下两个因素：

1. MySQL需要1 ～ 2个字节来表示字符串的长度。具体来说，如果字符串占用的字节数在 0 ～255 之间，需1个字节来表示，如果大于 255 个字节，则需2个字节来表示。

2. 如果列定义为NULL，额外还需要1个字节。

既然 65535 是记录的最大长度，则这个限制不仅仅是针对一列，而是所有列。即所有列的长度加起来不能超过65535。

看下面这个示例，指定了2个列，分别定义为VARCHAR和INT。

mysql> create table t (c1 varchar(65530) not null,c2 int not null) charset latin1;
ERROR 1118 (42000): 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

mysql> create table t (c1 varchar(65529) not null,c2 int not null) charset latin1;
Query OK, 0 rows affected (0.10 sec)

因为INT会占4个字节，所以 c1 最大就只能设置为65535 - 4 - 2 = 65529。这里之所以要减去2，是因为varchar(65529)超过了255个字节，需要2个字节来表示其长度。

报错信息中，提到“The maximum row size for the used table type, not counting BLOBs, is 65535”，即记录的最大长度限制不包括BLOB等字段。

之所以将BLOB和TEXT排除在外，是因为它的内容会单独存储在其它页中。但即便如此，存储BLOB和TEXT的指针信息也需要9 ~ 12个字节，具体来说：

TINYTEXT(TINYBLOB): 9 字节
TEXT(BLOB): 10 字节
MEDIUMTEXT(MEDIUMBLOB): 11字节
LONGTEXT(LONGBLOB): 12字节。

看下面这个示例，指定了2个列，分别定义为VARCHAR和TEXT。

mysql> create table t (c1 varchar(65524) not null,c2 text not null) charset latin1;
ERROR 1118 (42000): 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 somecolumns to TEXT or BLOBs

mysql> create table t (c1 varchar(65523) not null,c2 text not null) charset latin1;
Query OK, 0 rows affected (0.13 sec)

因为TEXT占了10个字节，所以 c1 最大可设置为65535 - 10 - 2 = 65523。

注意，上面提到的一条记录的最大长度不能超过65535字节是MySQL的限制，与存储引擎无关。实际上，存储引擎对行长也有限制。在InnoDB存储引擎中，就规定一条记录的最大长度不能超过数据页大小的1/2。

InnoDB中数据页的大小由innodb_page_size参数决定，默认为16K。所以，在页长为16K的情况下，InnoDB中一条记录的大小不能超过8K。如果超过了8K，InnoDB会将部分变长字段存储在外部页中。

看下面这个示例，因为定义的都是CHAR，定长字段，所以不会存储在外部页中。

mysql> create table t (
            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)
            ) charset latin1;
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.

3. VARCHAR的空间使用情况

给定一个VARCHAR类型的字符串，怎么知道它的空间使用情况呢？

首先，看看官方文档的说法。

类型	存储需求
VARCHAR(M)	L + 1 bytes if column values require 0 ? 255 bytes, L + 2 bytes if values may require more than 255 bytes

这里的 L 指的是字符串所占的字节数，与字符集有关。

以“中国”为例。

在utf8mb4中，一个中文汉字需要 3 个字节来表示，所以，“中国”通过utf8mb4来存储，会占用6个字节。

而在GBK中，一个中文汉字只需2个字节来表示，所以，“中国”通过GBK来存储，会占用4个字节。

除此之外，还需要额外的1~2个字节来表示字符串的长度。

4. VARCHAR(50) 和 VARCHAR(500) 的区别

很多人可能会好奇，VARCHAR 不是按需分配的么？在满足业务的需求情况下，设置 VARCHAR(50) 和 VARCHAR(500) 有什么区别呢？最后的空间占用还不是以实际写入的字符串为主，从这个角度来看，确实没错。但考虑以下两点：

1. 索引有最大长度的限制

对于行格式为 REDUNDANT 或 COMPACT 的InnoDB表，索引的最大长度被限制为767字节。所以，在MySQL 5.6 中，在创建索引时，我们通常会碰到“Index column size too large. The maximum column size is 767 bytes”错误：

mysql> create table t(c1 varchar(200)) charset=utf8mb4;   
Query OK, 0 rows affected (0.02 sec)

mysql> alter table t add index(c1);
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

上面这个限制不仅仅适用于单个索引，同样也适用于复合索引。

要解决这个问题，可选的方案有：

1. 减少字段的长度，确保字段的长度 * 字符集的最大字节数不超过767。

2. 设置前缀索引，确保前缀索引的长度 * 字符集的最大字节数不超过767。

mysql> alter table t add index(c1(191));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

3. 将表的行格式设置为 DYNAMIC 或 COMPRESSED，同时开启innodb_large_prefix，索引的最大长度可支持3072字节。

在 MySQL 5.6 中，如果要将表的行格式设置为DYNAMIC 或 COMPRESSED，必须将参数innodb_file_format设置为Barracuda。

innodb_file_format用来设置InnoDB文件的格式，支持的文件格式有Antelope和Barracuda。Antelope是最早的文件格式，支持REDUNDANT和COMPACT这两种行格式，而Barracuda则是最新的文件格式，支持DYNAMIC和COMPRESSED这两种行格式。

在 MySQL 5.6 中，innodb_file_format默认为Antelope，创建表时，如果没有显式指定row_format，则默认为Compact。

在 MySQL 5.7 中，innodb_file_format默认为Barracuda，创建表时，如果没有显式指定row_format，则默认为Dynamic。

在 MySQL 8.0 中，innodb_file_format被移除了，取而代之的是innodb_default_row_format。该参数用来设置默认的row_format，默认值为dynamic。

而innodb_large_prefix呢？在 MySQL 5.6 中，默认为OFF。在 MySQL 5.7 中，默认为ON，在 MySQL 8.0中，也移除了。

这也是为什么在 MySQL 5.6 中，更容易出现“Index column size too large. The maximum column size is 767 bytes”错误。

2. MEMORY引擎的限制

在 MySQL 8.0 之前，内存临时表只支持 MEMORY 引擎，而 MEMORY 引擎会将 VARCHAR 等变长类型作为定长来分配内存。

When in-memory internal temporary tables are managed by the MEMORY storage engine, fixed-length row format is used. VARCHAR and VARBINARY column values are padded to the maximum column length, in effect storing them as CHAR and BINARY columns.

可喜的是，从 MySQL 8.0 开始，内存临时表支持 TempTable 引擎。TempTable 引擎，从 MySQL 8.0.13 开始，优化了VARCHAR等变长类型的存储。

5. 总结

1. 对于固定长度的字符串推荐使用 CHAR 来存储。

2. VARCHAR能设置的最大长度与使用的字符集、自身占用的字节数、是否定义为NULL有关。

3. MySQL限制了一条记录的最大长度是 65535 字节。

4. InnoDB存储引擎限制了一条记录的最大长度不能超过数据页大小的1/2。但在实际处理的时候，InnoDB会将部分变长字段存储在外部页中，所以我们实际能存储的比限制的要大。

5. 字符串所占的字节数，与字符集有关。除此之外，还需要额外的1~2个字节来表示字符串的长度。

6. 在满足业务需求的情况下，VARCHAR应越短越好。

7. 对于行格式为 REDUNDANT 或 COMPACT 的InnoDB表，索引的最大长度为767字节。

8. 对于行格式为 DYNAMIC 或 COMPRESSED 的InnoDB表，索引的最大长度为3072字节。

9. MEMORY 引擎会将 VARCHAR 等变长类型作为定长来分配内存。