MySQL提示“Error 1709: Index column size too large. The maximum column size is 767 bytes.”
现象描述
MySQL在大字段上创建索引,或者字段的长度时，可能会遇到如下错误：
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
解释原因
原因是Mysql的innodb引擎表，在缺省状态下，缺省情况下，索引键前缀长度限制最长为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个字符；假设字符集为utfmb4，那么每个字符占4个字节，那么这类字段的字符长度最长则是767/4 ≈ 191个字符。
只有当innodb_large_prefix配置选项被启用时，对于使用DYNAMIC
或COMPRESSED
行格式的InnoDB表，索引键前缀长度限制将提高到3072
字节。
看个例子，我有一张表a_test,表结构如下所示。
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字节的限制。
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，看看会发生什么。
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是否开启:
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。
show table status like '%a_test%'\G
修改表的row_format为Dynamic。
alter table a_test row_format=dynamic;
验证一下：
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了。
mysql中表的row_format
关于mysql的行记录格式
在早期的InnoDB版本中，由于文件格式只有一种，因此不需要为此文件格式命名。随着InnoDB引擎的发展，开发出了不兼容早期版本的新文件格式，用于支持新的功能。为了在升级和降级情况下帮助管理系统的兼容性，以及运行不同的MySQL版本，InnoDB开始使用命名的文件格式。
1. Antelope: 先前未命名的，原始的InnoDB文件格式。它支持两种行格式：COMPACT 和 REDUNDANT。MySQL5.6的默认文件格式。可以与早期的版本保持最大的兼容性。不支持 Barracuda 文件格式。
2. Barracuda: 新的文件格式。它支持InnoDB的所有行格式，包括新的行格式：COMPRESSED 和 DYNAMIC。与这两个新的行格式相关的功能包括：InnoDB表的压缩，长列数据的页外存储和索引建前缀最大长度为3072字节。
在 mysql 5.7.9及以后版本，默认行格式由innodb_default_row_format变量决定，它的默认值是DYNAMIC。
在早期的InnoDB版本中，由于文件格式只有一种，因此不需要为此文件格式命名。随着InnoDB引擎的发展，开发出了不兼容早期版本的新文件格式，用于支持新的功能。为了在升级和降级情况下帮助管理系统的兼容性，以及运行不同的MySQL版本，InnoDB开始使用命名的文件格式。
1. Antelope: 先前未命名的，原始的InnoDB文件格式。它支持两种行格式：COMPACT 和 REDUNDANT。MySQL5.6的默认文件格式。可以与早期的版本保持最大的兼容性。不支持 Barracuda 文件格式。
2. 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字节。只有当innodb_large_prefix配置选项被启用时，对于使用DYNAMIC
或COMPRESSED
行格式的InnoDB表，索引键前缀长度限制将提高到3072
字节。
在myql 5.7.9及以后版本，默认行格式由innodb_default_row_format变量决定，它的默认值是DYNAMIC。8.0之后，innodb_large_prefix参数被移除，默认开启。
