MySQL 异常：max key length is 767 bytes

前言

最近迁移几张表，又遇到 767 异常，迁移前只检查了 sql_mode 忽略对比了这个参数，导致几张表创建失败，其实解决方法也很简单，开启 innodb_large_prefix 参数重新导入即可。该 ERROR 常发生在字符串类型的大字段上，本篇文章简单记录下该问题。

[42000][1071] Specified key was too long; max key length is 767 bytes

1. 767 bytes 解读

如何知道某张表字符串字段占多少 bytes 呢？需要结合表的字符集来看：

CREATE TABLE `test_index` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(800) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

上表的字符类型是 utf8 我们可以通过查表来判断字符最大可占用的空间：

show character set;

Charset	Description	Default collation	Maxlen
big5	Big5 Traditional Chinese	big5_chinese_ci	2
dec8	DEC West European	dec8_swedish_ci	1
cp850	DOS West European	cp850_general_ci	1
hp8	HP West European	hp8_english_ci	1
koi8r	KOI8-R Relcom Russian	koi8r_general_ci	1
latin1	cp1252 West European	latin1_swedish_ci	1
latin2	ISO 8859-2 Central European	latin2_general_ci	1
swe7	7bit Swedish	swe7_swedish_ci	1
ascii	US ASCII	ascii_general_ci	1
ujis	EUC-JP Japanese	ujis_japanese_ci	3
sjis	Shift-JIS Japanese	sjis_japanese_ci	2
hebrew	ISO 8859-8 Hebrew	hebrew_general_ci	1
tis620	TIS620 Thai	tis620_thai_ci	1
euckr	EUC-KR Korean	euckr_korean_ci	2
koi8u	KOI8-U Ukrainian	koi8u_general_ci	1
gb2312	GB2312 Simplified Chinese	gb2312_chinese_ci	2
greek	ISO 8859-7 Greek	greek_general_ci	1
cp1250	Windows Central European	cp1250_general_ci	1
gbk	GBK Simplified Chinese	gbk_chinese_ci	2
latin5	ISO 8859-9 Turkish	latin5_turkish_ci	1
armscii8	ARMSCII-8 Armenian	armscii8_general_ci	1
utf8	UTF-8 Unicode	utf8_general_ci	3
ucs2	UCS-2 Unicode	ucs2_general_ci	2
cp866	DOS Russian	cp866_general_ci	1
keybcs2	DOS Kamenicky Czech-Slovak	keybcs2_general_ci	1
macce	Mac Central European	macce_general_ci	1
macroman	Mac West European	macroman_general_ci	1
cp852	DOS Central European	cp852_general_ci	1
latin7	ISO 8859-13 Baltic	latin7_general_ci	1
utf8mb4	UTF-8 Unicode	utf8mb4_general_ci	4
cp1251	Windows Cyrillic	cp1251_general_ci	1
utf16	UTF-16 Unicode	utf16_general_ci	4
utf16le	UTF-16LE Unicode	utf16le_general_ci	4
cp1256	Windows Arabic	cp1256_general_ci	1
cp1257	Windows Baltic	cp1257_general_ci	1
utf32	UTF-32 Unicode	utf32_general_ci	4
binary	Binary pseudo charset	binary	1
geostd8	GEOSTD8 Georgian	geostd8_general_ci	1
cp932	SJIS for Windows Japanese	cp932_japanese_ci	2
eucjpms	UJIS for Windows Japanese	eucjpms_japanese_ci	3
gb18030	China National Standard GB18030	gb18030_chinese_ci	4

例如 utf8 的 Maxlen = 3 表示一个字符最大占用 3 bytes。那么 varchar 类型小于 255 字符，就不会触发 767 异常。

2. 异常原因

为什么会有这个异常呢？其实该异常属于 MySQL Innodb 引擎的限制，例如一张表最多有 1017 个字段，一张表最大有 64 个二级索引一样，如果索引列的字符长度很大的话，二级索引占用的空间也会非常大，一个页面存储的索引字段减少，索引深度会更容易变深，增加 IO 次数，搜索的性能也不会理想。

If innodb_large_prefix is enabled (the default), the index key prefix limit is 3072 bytes for InnoDB tables that use the DYNAMIC or COMPRESSED row format. If innodb_large_prefix is disabled, the index key prefix limit is 767 bytes for tables of any row format.

Innodb 引擎的限制：14.23 InnoDB Limits

3. 5.7 和 8.0 版本的区别

在 5.7 版本中，对于使用 DYNAMIC 或 COMPRESSED 行格式的 InnoDB 表，索引前缀的限制为 767 bytes，如果开启 innodb_large_prefix 参数，限制阈值上调为 3072 bytes。

在 8.0 版本中，参数 innodb_large_prefix 被删除，索引前缀最大为 3072 bytes 不再有 767 bytes 的限制。

ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

4. 版本 5.6 特殊情况

在 5.6 版本中 innodb_default_row_format = Compact 默认值，只有 DYNAMIC 和 COMPRESSED 行格式突破 767，所以 5.6 版本修改参数后，依然无法创建大于 767 字节的索引，此时可以去查表的行格式，大概率是行格式不支持：

select TABLE_SCHEMA, TABLE_NAME, ROW_FORMAT
from information_schema.TABLES where TABLE_NAME = '表名'

此时修改行格式可以解决：

alter table 表名 row_format=dynamic;