索引长度过长 ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
一： 遇到问题
1. 今天在进行MySQL迁移的时候遇到了一个问题，源版本为 5.6.24 新版本为：5.7.24；由于购买的虚拟机内存为8G，配置较低，因此更改了 innodb_page_size 的大小,更改为8192，默认的大写为16384。即16k。
创建MySQL表的语句如下：
CREATE TABLE `mfg_org`.`mfg_stu_wrong` ( `wrongid` varchar(255) CHARSET `utf8` COLLATE `utf8_general_ci` COMMENT '' NOT NULL , `userid` int(11) COMMENT '' NULL , `questionid` int(11) COMMENT '' NULL , `subject` varchar(255) CHARSET `utf8` COLLATE `utf8_general_ci` COMMENT '' NULL DEFAULT '' , `bgrade` varchar(255) CHARSET `utf8` COLLATE `utf8_general_ci` COMMENT '' NULL DEFAULT '' , `secid` int(11) COMMENT '知识点id' NOT NULL , `quetype` varchar(255) CHARSET `utf8` COLLATE `utf8_general_ci` COMMENT '题型(1：选择题，2：填空题，3：简答题)' NULL DEFAULT '' , `answer` text CHARSET `utf8` COLLATE `utf8_general_ci` COMMENT '正确答案' NULL , `useranswer` text CHARSET `utf8` COLLATE `utf8_general_ci` COMMENT '用户答案' NULL , `isdelete` char(1) CHARSET `utf8` COLLATE `utf8_general_ci` COMMENT '是否删除1：删除，0：不删除' NULL DEFAULT '0' , `createtime` datetime COMMENT '入库时间' NULL DEFAULT CURRENT_TIMESTAMP , `secname` varchar(255) CHARSET `utf8` COLLATE `utf8_general_ci` COMMENT '知识点名称' NOT NULL , PRIMARY KEY (`wrongid`), INDEX `subject_userid_isdelete_secid_secname` (`subject`, `userid`, `isdelete`, `secid`, `secname`) , INDEX `userid_subject_secid_isdelete_createtime` (`userid`, `subject`, `secid`, `isdelete`, `createtime`) , INDEX `questionid_userid` (`questionid`, `userid`) , INDEX `userid_subject_secid_isdelete_createtime1` (`userid`, `subject`, `secid`, `isdelete`, `createtime`) ) engine=InnoDB DEFAULT CHARSET=`utf8` DEFAULT COLLATE `utf8_general_ci` comment = '错题本' ;
结果出现了报错：
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
其实从上面的报错我们已经能看到原因了：
索引达到长度限制，所以报错
对于myisam和innodb存储引擎，prefixes的长度限制分别为1000 bytes和767 bytes。注意prefix的单位是bytes，但是建表时我们指定的长度单位是字符。
以utf8字符集为例，一个字符占3个bytes。因此在utf8字符集下，对myisam和innodb存储引擎创建索引的单列长度不能超过333个字符和255个字符。
从上面可以看出，mysql 在创建单列索引的时候对列的长度是有限制的 myisam和innodb存储引擎下长度限制分别为1000 bytes和767 bytes。(注意bytes和character的区别)
如下代码所示：
1 mysql> create table test3(id varchar(255),key(id)); 2 Query OK, 0 rows affected (0.11 sec) >>索引列长度小于767(255*3=765),表创建成功 3 4 mysql> drop table test3; 5 Query OK, 0 rows affected (0.03 sec) 6 7 mysql> create table test3(id varchar(256),key(id)); >>索引列长度大于767(256*3=768),所以创建表失败 8 ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes 9 mysql> create table test3 (id varchar(255),name varchar(255),name1 varchar(255),name2 varchar(255),name3 varchar(5),key (id,name,name1,name2,name3)); >>innodb下多列索引，所有列长度和大于3072/3=1024 (255*4+5=1025),所以表创建失败 10 ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes 11 mysql> create table test3 (id varchar(255),name varchar(255),name1 varchar(255),name2 varchar(255),name3 varchar(4),key (id,name,name1,name2,name3)); >> 12 Query OK, 0 rows affected (0.09 sec)
二： 解决方案
1. 启用innodb_large_prefix参数
在配置文件中添加 innodb_large_prefix = 1
innodb_large_prefix >>启用innodb_large_prefix参数能够取消对于索引中每列长度的限制(但是无法取消对于索引总长度的限制) 启用innodb_large_prefix有如下前提条件： Enable this option to allow index key prefixes longer than 767 bytes (up to 3072 bytes) for InnoDB tables that use the DYNAMIC and COMPRESSED row formats.(Creating such tables also requires the option values innodb_file_format=barracuda and innodb_file_per_table=true.)
>>启用innodb_large_prefix必须同时指定innodb_file_format=barracuda，innodb_file_per_table=true，并且建表的时候指定表的row_format为dynamic或者compressed(mysql 5.6中row_format默认值为compact)
See Section 14.6.7, “Limits on InnoDB Tables” for the relevant maximums associated with index key prefixes under various settings. >> For tables using the REDUNDANT and COMPACT row formats, this option does not affect the allowed key prefix length.
2. 修改 innodb_page_size
配置文件中修改 innodb_page_size=16384
三：总结
1、系统变量innodb_large_prefix为ON
2、系统变量innodb_file_format为Barracuda
3、ROW_FORMAT为DYNAMIC或COMPRESSED
4、 innodb_page_size=16384
