mysql 1071 specified_MySQL 问题分析 : ERROR 1071 (42000) : Specified key was too long

原标题：MySQL 问题分析 : ERROR 1071 (42000) : Specified key was too long

来源：潇湘隐者 ，

www.cnblogs.com/kerrycode/p/9680881.html

今天在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