MySQL varchar 单字段的最大字符长度是多少

MySQL varchar 单字段的最大字符长度是多少

MySQL 行记录的存储结构：

变长字段长度列表	NULL值列表	记录头信息	row_id	trx_id	roll_ptr	列1	列2	列n
每个变长字段值的长度 （倒序），根据变长字段的长度而定	每个允许为NULL字段的标志位 （倒序），每个NULL字段占1位	(5字节）	隐藏字段，不一定有（6字节）	隐藏字段，一定有（6字节）	隐藏字段，一定有（7字节）
记录的额外信息			记录的真实数据

‍

MySQL一行记录除了 TEXT，BLOB 类型的列，其余的字段长度加起来不能超过 65535 字节；

mysql> CREATE TABLE test (`name` VARCHAR(65535)  NULL) ENGINE = InnoDB DEFAULT CHARACTER SET = ascii ROW_FORMAT = COMPACT;
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>

varchar 指定的是字符数， ascii​ 编码下，一个字符占用一个字节；

65535 字段包含：「变长字段长度列表」、「NULL 值列表」、真实数据长度；

「变长字段长度列表」：每个 varchar 字段占用的字节数，这里只有一个字段，假设最大长度 65535 对应十六进制 0xFFFF，占用 2 字节的空间；

「NULL 值列表」：只有一个字段，占用 1 字节；

65535 - 「NULL 值列表」长度 - 「变长字段长度列表」长度 = 65535 - 1 - 2 = 65532

‍

所以在字符集是 ascii ，字段允许为空的情况下，单字段的 varchar 的最大值是 65532：

mysql> CREATE TABLE test (`name` VARCHAR(65533)  NULL) ENGINE = InnoDB DEFAULT CHARACTER SET = ascii ROW_FORMAT = COMPACT;
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>


mysql> CREATE TABLE test (`name` VARCHAR(65532)  NULL) ENGINE = InnoDB DEFAULT CHARACTER SET = ascii ROW_FORMAT = COMPACT;
Query OK, 0 rows affected (0.02 sec)

mysql>

在字符集是 ascii ，字段不允许为空的情况下，单字段的 varchar 的最大值是 65533：

mysql> CREATE TABLE test (`name` VARCHAR(65534) NOT NULL DEFAULT '') ENGINE = InnoDB DEFAULT CHARACTER SET = ascii ROW_FORMAT = COMPACT;
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>


mysql> CREATE TABLE test (`name` VARCHAR(65533) NOT NULL DEFAULT '') ENGINE = InnoDB DEFAULT CHARACTER SET = ascii ROW_FORMAT = COMPACT;
Query OK, 0 rows affected (0.00 sec)

mysql>

‍

如果字符集是 utf8mb4，字符允许为空，65535 / 4 = 16383 = 0x3FFF，「变长字段长度列表」也是占用两字节

所以 varchar 字段的最大字符长度是：(65535 - 1 - 2) / 4 = 65532 / 4 = 16383

mysql> CREATE TABLE test (`name` VARCHAR(16384)  NULL) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8mb4 ROW_FORMAT = COMPACT;
ERROR 1074 (42000): Column length too big for column 'name' (max = 16383); use BLOB or TEXT instead
mysql>


mysql> CREATE TABLE test (`name` VARCHAR(16383)  NULL) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8mb4 ROW_FORMAT = COMPACT;
Query OK, 0 rows affected (0.00 sec)

mysql>

字符不允许为空时，(65535 - 2) / 4 = 65533 / 4 = 16383 ，最大也是 16383 字符。

‍

多字段的情况下：每个字段的长度 + 「变长字段长度列表」 + 「NULL 值列表」 <= 65535

‍

以下内容粘自MySQL官方文档：17.10 InnoDB Row Formats

• The variable-length part of the record header contains a bit vector for indicating NULL​ columns. If the number of columns in the index that can be NULL​ is ​N​​, the bit vector occupies CEILING(<em class="replaceable"><code>N</code></em>/8)​ bytes. (For example, if there are anywhere from 9 to 16 columns that can be NULL​, the bit vector uses two bytes.) Columns that are NULL​ do not occupy space other than the bit in this vector. The variable-length part of the header also contains the lengths of variable-length columns. Each length takes one or two bytes, depending on the maximum length of the column. If all columns in the index are NOT NULL​ and have a fixed length, the record header has no variable-length part.
• Records in the clustered index contain fields for all user-defined columns. In addition, there is a 6-byte transaction ID field and a 7-byte roll pointer field.
• If no primary key is defined for a table, each clustered index record also contains a 6-byte row ID field.

‍

参考文章：

MySQL 一行记录是怎么存储的？

17.10 InnoDB Row Formats

‍