合 MySQL导入报错“Index column size too large. The maximum column size is 767 bytes.”
Tags： 故障处理MySQLIndex column size too largeThe maximum column size is 767 bytes
现象
MySQL在大字段上创建索引,或者字段的长度时，可能会遇到如下错误：
ERROR 1709 (HY000) at line 33: Index column size too large. The maximum column size is 767 bytes.
原因
原因是Mysql的innodb引擎表，在缺省状态下，缺省情况下，索引键前缀长度限制最长为767字节。
产生此问题的原因是因为MySQL Innodb索引字段长度最大为767字节，如果索引大小超过767字节则会引发该错误。
对于多字节字符集的大字段或者多字段组合，创建索引时会出现该问题。
详情见官方文档：https://dev.mysql.com/doc/refman/5.6/en/innodb-limits.html
By default, the index key prefix length limit is 767 bytes. See Section 13.1.13, “CREATE INDEX Statement”. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a utf8mb3 character set and the maximum of 3 bytes for each character. When the innodb_large_prefix configuration option is enabled, the index key prefix length limit is raised to 3072 bytes for InnoDB tables that use the DYNAMIC or COMPRESSED row format.
如官方文档上所写，如果要在text或varchar类型字段上创建索引，假设字符集为utfmb3，那么每个字符占3个字节，那么这类字段的字符长度最长则是767/3 ≈ 255个字符；假设字符集为utf8mb4，那么每个字符占4个字节，那么这类字段的字符长度最长则是767/4 ≈ 191个字符。
以utf8mb4字符集字符串类型字段为例。utf8mb4是4字节字符集，默认支持的索引字段最大长度是191字符（767字节/4字节每字符≈191字符），因此在varchar(255)或char(255)类型字段上创建索引会失败。
只有当innodb_large_prefix配置选项被启用时，对于使用DYNAMIC或COMPRESSED行格式的InnoDB表，索引键前缀长度限制将提高到3072字节。
看个例子，我有一张表a_test,表结构如下所示。
1 2 3 4 5 6 7 | CREATE TABLE `a_test add index ` ( `id` bigint NOT NULL COMMENT '主键ID', `biz_id` varchar(191) NOT NULL DEFAULT '' COMMENT '业务id', `config_code` varchar(32) NOT NULL DEFAULT '' COMMENT '模板code', PRIMARY KEY (`id`), UNIQUE KEY `uniq_bizid_config` (`biz_id`,`config_code`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT COMMENT='测试表' |
可以看到表的Row_Format为compact，那么在字段biz_id(varchar(191))上建立索引idx_biz_id(biz_id)时，索引键前缀长度有最长为767字节的限制。
1 2 3 | mysql> alter table a_test add index idx_biz_id(`biz_id`); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 |
可以看到此时可以成功创建索引idx_biz_id(biz_id)，接下来，我们将biz_id字段的修改为192，看看会发生什么。
1 2 | mysql> alter table a_test modify `biz_id` varchar(192) NOT NULL DEFAULT '' COMMENT '业务id'; ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes |
此时如果想成功修改字段长度，需要开启innodb_large_prefix，并且设置表a_test的row_format为DYNAMIC或COMPRESSED。
查看需要开启innodb_large_prefix是否开启:
1 | show variables like "%innodb_large_prefix%" |
备注：8.0之后，该参数被移除，默认开启。innodb_large_prefix: Enables longer keys for column prefix indexes. Removed in MySQL 8.0.0.
查看表的row_format。
1 | show table status like '%a_test%'\G |
修改表的row_format为Dynamic。
1 | alter table a_test row_format=dynamic; |
验证一下：
