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

‍

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 = COMPAC
T;
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 = COMPAC
T;
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_F
ORMAT = 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_F
ORMAT = 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 = COMP
ACT;
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 = COMP
ACT;
Query OK, 0 rows affected (0.00 sec)

mysql>

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

‍

参考文章：

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

‍