【MySQL】MySQL创建索引提示字符长度超限制解决办法

 

1.报错信息

在执行mysql添加索引sq脚本的时候报错:"Specified key wastoo long; max key length is 767 bytes"
意思大概就是:需要添加索引的字段的长度太长,超过了767bytes.

 

2.分析原因

需要添加的索引字段如果是varchar类型,字段设置的长度不能起过767个字节,即是767/3=255个三字节,而UTF-8则是三字节。

针对innodb存储引擎,如果是多字段联合索引,有长度限制:每个字段的长度不能大于767个字节,并且联合索引所有字段的长度总和不能大于3072个字节。
而MyISAM存储引擎,长度限制则是:每个字段的长度不能大于1000个字节,并且联合索引所有字段的长度总和不能大于1000个字节。

注意:utf8mb4是4字节字符集,默认支持的索引字段最大长度是191字符(767字节/4字节每字符≈191字符),因此在varchar(255)或char(255)类型字段上创建索引会失收。

然后我就去看了一下表结构上的需要添加索引的字段的varchar长度,发现有个字段是:varchar(255),而表是设置了 utf8mb4(utf8mb4是指每个字符的最大字节数是4,用于存储emoji表情数据,所以4*255=1020>767了),所以导致了联合索引添加失败。

 

3.解决方案

1、针对字段的长度,可以根据实际情况,我将varchar(255)改成了varchar(64),然后联合索引就添加成功了。

2、修改mysql数据库配置,可以启用启用innodb_large_prefix参数,来使得单个索引字段的长度突破767,具体操作命令如下:(一般不建议直接修改数据库配置,此方去了解下)

--查看innodb_large_prefix,innodb_file_format参数
show variables like 'innodb_large_prefix';
show variables like 'innodb_file_format;

--修改innodb_large_prefix,innodb_file_format参数
set global innodb_large_prefix=1;
set global innodb_file_format=BARRACUDA;

innodb_large_prefix=1#月innodb_file_format=BARRACUDA时, 对于row_format为dynamic的表可以指定索引列长度大于767bytes。但是索引列总长度的仍然不能大于3072bytes。

注意:启用innodb_large_prefix,能够取消对于索引中每列长度的限制,但是无法取消对于索引总长度的限制;
启用innodb_large_prefix,必须同时指定innodb_file_format=barraacuda, innodb_file_per_table=true,并且建
表的时候指定表的row_format为dynamic或者compressed,mysql 5.6中row_format默认值为compact;

 

3、适当情况下可考虑前缀索引(后续完善前缀索引相关知识)

 

 

4.参考文档

关于索引长度的限制,最主要的因素:1.存储引擎;2.字符集。

字符集的影响在于,不同的字符集,单个字符包含的最大字节数有所不同。
1.utf8字符集,一个字符最多包含3个字节。
2.utf8mb4字符集,一个字符最多包含4个字节。

 

InnoDB引擎
1) Mysql版本<=5.7.6
如果是单字段索引,则字段长度不应超过767字节。
如果是联合索引,则每个字段长度都不应超过767字节,且所有字段长度合计不应超过3072字节。
这种情况下,常见字符集和引擎组合后的字符长度限制如下:

 

 

 

2) Mysql版本>=5.7.7

在该版本情况下,由于InnoDB引擎的innodb_large_prefix等选项默认值改变,单字段索引长度限制增大。
如果是单字段索引,则字段长度不应超过3072字节。
如果是联合索引,则每个字段长度都不应超过3072字节,且所有字段长度合计不应超过3072字节。
这种情况下,常见字符集和引擎组合后的字符长度限制如下: