ERROR 1071 (42000): Specified key was too long； max key length is 767 bytes

 

今天节假日，有个学弟给我发消息说遇到这样的问题，ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes，有什么办法？这个问题蛮常见的了，小表基本不会遇到，只有那些varchar字段特别长的，或者有多个varchar字段，又要建立多个索引的表，才会遇到。

 

这个报错信息，是由于索引长度限制导致的，并不是字段长度限制，而是有一个系统参数innodb_large_prefix来控制的，我们先来官网上看看这个系统参数的介绍https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_large_prefix

这个是全局的系统参数，默认是是打开的，默认值是767types（最大可以调整到3072bytes），也就是说默认超过767，添加索引就会报错的，这个参数从mysql5.5版本引入的，但是5.6是默认关闭的，5.7里面是默认打开的，主要是用来禁用大型前缀索引，以便与不支持大索引键前缀的早期版本的InnoDB兼容。对于使用REDUNDANT或COMPACT行格式的InnoDB表，索引键前缀长度限制为767字节。例如，您可能会在TEXT或VARCHAR列上使用超过255个字符的列前缀索引达到此限制，假设为utf8mb3字符集，并且每个字符最多包含3个字节。尝试使用超出限制的索引键前缀长度会返回错误。要避免复制配置中出现此类错误，请避免在主服务器上启用enable innodb_large_prefix，适用于索引键前缀的限制也适用于全列索引键。

 

【备注】这里767不是字符数量，而是字节，字符集不同，字段能包容的长度就不同，比如拉汀latain是1字节，gbk是2字节，utf-8就是3字节。

 

innodb_large_prefix生效的话，需要满足下面几个条件：

1、系统变量innodb_large_prefix为ON

2、系统变量innodb_file_format为Barracuda

3、ROW_FORMAT为DYNAMIC或COMPRESSED

 

767报错之后如何解决这个问题，大概有以下方案：

1、调整innodb_large_prefix参数到

set global innodb_large_prefix=on;
set global innodb_file_format=Barracuda;

2、调整索引规则，使用前缀索引

ALTER TABLE TEST add index idx_cv(CODE_NAME(32));

 

不过在本地做测试的是，发现5.8里面已经没有这个参数了，索引建立也没有了字符长度的限制，看操作记录如下：

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.21    |
+-----------+
1 row in set (0.00 sec)

mysql> CREATE TABLE `TEST` (
    ->      `CODE_NAME` varchar(100) NOT NULL DEFAULT '',
    ->      `CODE_SEQ` smallint(6) NOT NULL DEFAULT '1',
    ->      `ACTIVE` char(1) DEFAULT 'Y',
    ->      `CODE_VALUE1` varchar(250) DEFAULT NULL,
    ->      PRIMARY KEY (`CODE_NAME`,`CODE_SEQ`),
    ->      KEY `IDX_GEN_CODE` (`CODE_NAME`,`CODE_VALUE1`)
    ->    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected, 1 warning (0.09 sec)

mysql> ALTER TABLE TEST MODIFY CODE_VALUE1 VARCHAR(350);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table test add index idx_code(code_value1,code_name);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show variables like 'innodb_large_prefix';
Empty set, 1 warning (0.34 sec)

mysql>
mysql> show variables like 'innodb_large_prefix';
Empty set, 1 warning (0.01 sec)

mysql>