MySQL导入报错“Index column size too large. The maximum column size is 767 bytes.”
Tags： Index column size too largeMySQLThe 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; |
验证一下：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | mysql> alter table a_test row_format=dynamic; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show table status like '%a_test%'\G ; *************************** 1. row *************************** Name: a_test Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 0 Avg_row_length: 0 Data_length: 16384 Max_data_length: 0 Index_length: 16384 Data_free: 0 Auto_increment: NULL Create_time: 2022-12-27 17:13:41 Update_time: NULL Check_time: NULL Collation: utf8mb4_0900_ai_ci Checksum: NULL Create_options: row_format=DYNAMIC Comment: 测试表 1 row in set (0.00 sec) ERROR: No query specified mysql> alter table a_test modify `biz_id` varchar(192) NOT NULL DEFAULT '' COMMENT '业务id'; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 |
可见，当修改表的row_format为dynamic后，可以biz_id字段的字符长度可以超过191了。
如果报错，可以根据报错的行数打开sql文件，查看第33行代码，发现建表语句如下：
1 2 3 4 5 6 | CREATE TABLE `airport_log` ( `id` varchar(255) NOT NULL, `create_time` datetime DEFAULT NULL, `create_user_id` varchar(30) DEFAULT NULL PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
解决
查看：
1 2 | show variables like 'innodb_file_format'; show variables like 'innodb_large_prefix'; |
修改：
1 2 | set global innodb_file_format = Barracuda; set global innodb_large_prefix = on; |
如果是MySQL rds，那么可以在界面修改参数 innodb_large_prefix 参数值为 ON，然后单击 提交。该参数在5.6中默认为 off，可以修改为 on；在5.7中默认为 on，不用修改。
也可以通过命令修改：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | MySQL [(none)]> show variables like '%innodb_large_prefix%'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | innodb_large_prefix | OFF | +---------------------+-------+ 1 row in set (0.03 sec) MySQL [(none)]> set global innodb_large_prefix=on; Query OK, 0 rows affected, 1 warning (0.00 sec) MySQL [(none)]> show variables like '%innodb_large_prefix%'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | innodb_large_prefix | ON | +---------------------+-------+ 1 row in set (0.02 sec) |
所以，建表语句可以修改为：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | CREATE TABLE `airport_log` ( `id` varchar(255) NOT NULL, `create_time` datetime DEFAULT NULL, `create_user_id` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 row_format=dynamic; -- 或者 CREATE TABLE `airport_log` ( `id` varchar(100) NOT NULL, `create_time` datetime DEFAULT NULL, `create_user_id` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 或者 CREATE TABLE `airport_log` ( `id` int NOT NULL, `create_time` datetime DEFAULT NULL, `create_user_id` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
mysql中表的row_format
在早期的InnoDB版本中，由于文件格式只有一种，因此不需要为此文件格式命名。随着InnoDB引擎的发展，开发出了不兼容早期版本的新文件格式，用于支持新的功能。为了在升级和降级情况下帮助管理系统的兼容性，以及运行不同的MySQL版本，InnoDB开始使用命名的文件格式。
Antelope: 先前未命名的，原始的InnoDB文件格式。它支持两种行格式：COMPACT 和 REDUNDANT。MySQL5.6的默认文件格式。可以与早期的版本保持最大的兼容性。不支持 Barracuda 文件格式。
Barracuda: 新的文件格式。它支持InnoDB的所有行格式，包括新的行格式：COMPRESSED 和 DYNAMIC。与这两个新的行格式相关的功能包括：InnoDB表的压缩，长列数据的页外存储和索引建前缀最大长度为3072字节。
在 mysql 5.7.9及以后版本，默认行格式由innodb_default_row_format变量决定，它的默认值是DYNAMIC。
在早期的InnoDB版本中，由于文件格式只有一种，因此不需要为此文件格式命名。随着InnoDB引擎的发展，开发出了不兼容早期版本的新文件格式，用于支持新的功能。为了在升级和降级情况下帮助管理系统的兼容性，以及运行不同的MySQL版本，InnoDB开始使用命名的文件格式。
Antelope: 先前未命名的，原始的InnoDB文件格式。它支持两种行格式：COMPACT 和 REDUNDANT。MySQL5.6的默认文件格式。可以与早期的版本保持最大的兼容性。不支持 Barracuda 文件格式。
Barracuda: 新的文件格式。它支持InnoDB的所有行格式，包括新的行格式：COMPRESSED 和 DYNAMIC。与这两个新的行格式相关的功能包括：InnoDB表的压缩，长列数据的页外存储和索引建前缀最大长度为3072字节。
在 msyql 5.7.9 及以后版本，默认行格式由innodb_default_row_format变量决定，它的默认值是DYNAMIC，也可以在 create table 的时候指定ROW_FORMAT=DYNAMIC。用户可以通过命令 SHOW TABLE STATUS LIKE'table_name' 来查看当前表使用的行格式，其中 row_format 列表示当前所使用的行记录结构类型。
一行记录可以以不同的格式存在InnoDB中，行格式分别是Compact、Redundant、Dynamic和Compressed行格式。
总结
Mysql的innodb引擎表，在缺省状态下，索引键前缀长度限制最长为767字节（假设字符集为utf8mb4，那么每个字符占4个字节，那么这类字段的字符长度最长则是767/4 ≈ 191个字符）。只有当innodb_large_prefix配置选项被启用时，对于使用DYNAMIC或COMPRESSED行格式的InnoDB表，索引键前缀长度限制将提高到3072字节（对于utf8mb4来说就是768个字符）。
在myql 5.7.9及以后版本，默认行格式由innodb_default_row_format变量决定，它的默认值是DYNAMIC。8.0之后，innodb_large_prefix参数被移除，默认开启。
