本日在MySQL 5.6版本的数据库中修改InnoDB表字段长度时碰着了”ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes”错误,第一次碰着这个错误，遂花了点进修、研究过、总结这个问题。 

我们先来建设一个测试表，结构这样的错误。

mysql> use MyDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
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=utf8;
Query OK, 0 rows affected (0.02 sec)
 
 
mysql> ALTER TABLE TEST MODIFY CODE_VALUE1 VARCHAR(350);
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
mysql>

其实这个“ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes”错误是指超出索引字节的限制，并不是指字段长度限制。在官方文档“Limits on InnoDB Tables”有关于这方面的先容、描写（详情请见参考资料）：

MySQL 5.6文档内容如下 

By default, the index key prefix length limit is 767 bytes. See Section 13.1.13, “CREATE INDEX Syntax”. 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 DYNAMIC or COMPRESSED row format.

 

Attempting to use an index key prefix length that exceeds the limit returns an error. To avoid such errors in replication configurations, avoid enablinginnodb_large_prefix on the master if it cannot also be enabled on slaves.

The limits that apply to index key prefixes also apply to full-column index keys.

MySQL 5.7文档内容如下：

If innodb_large_prefix is enabled (the default), the index key prefix limit is 3072 bytes for InnoDB tables that use 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_large_prefix is deprecated and will be removed in a future release. innodb_large_prefix was introduced in MySQL 5.5 to disable large index key prefixes for compatibility with earlier versions of InnoDB that do not support large index key prefixes.

The index key prefix length limit is 767 bytes for InnoDB tables that use the REDUNDANT or COMPACT row format. 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.

Attempting to use an index key prefix length that exceeds the limit returns an error. To avoid such errors in replication configurations, avoid enablinginnodb_large_prefix on the master if it cannot also be enabled on slaves.

The limits that apply to index key prefixes also apply to full-column index keys.

假如启用了系统变量innodb_large_prefix（默认启用，留意尝试版本为MySQL 5.6.41,默认是封锁的，MySQL 5.7默认开启），则对付利用DYNAMIC或COMPRESSED行名目标InnoDB表，昆山软件公司，索引键前缀限制为3072字节。假如禁用innodb_large_prefix，则对付任何行名目标表，索引键前缀限制为767字节。

innodb_large_prefix将在今后的版本中删除、弃用。在MySQL 5.5中引入了innodb_large_prefix，用来禁用大型前缀索引，以便与不支持大索引键前缀的早期版本的InnoDB兼容。

对付利用REDUNDANT或COMPACT行名目标InnoDB表，索引键前缀长度限制为767字节。譬喻，您大概会在TEXT或VARCHAR列上利用高出255个字符的列前缀索引到达此限制，假设为utf8mb3字符集，而且每个字符最多包括3个字节。

实验利用超出限制的索引键前缀长度会返回错误。要制止复制设置中呈现此类错误，昆山软件开发，请制止在主处事器上启用enableinnodb_large_prefix（假如无法在从处事器上启用）。

合用于索引键前缀的限制也合用于全列索引键。

留意：上面是767个字节，而不是字符，详细到字符数量，昆山软件开发，这就跟字符集有关。GBK是双字节的，UTF-8是三字节的

办理方案：

1：启用系统变量innodb_large_prefix

留意：光有这个系统变量开启是不足的。必需满意下面几个条件：