【mysql】mysql字符集设置为：utf8mb4，创建表时候错误Specified key was too long; max key length is 767 bytes
- 经验分享
- 时间：2020-01-05 14:04
- 2576人已阅读
🔔🔔🔔好消息!好消息!🔔🔔🔔
有需要的朋友👉:联系凯哥
mysql在创建数据库的时候，字符集设置的不是utf8而是utf9mb4，在导入sql脚本的时候，发现提示如下错误：
从上图中，我们可以看出，使用的是innodb及字符集。错误提示是长度太长了：Specified key was too long; max key length is 767 bytes
来查看下创建表的语句：
CREATE TABLE `xxl_job_registry` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`registry_group` varchar(50) NOT NULL,
`registry_key` varchar(255) NOT NULL,
`registry_value` varchar(255) NOT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `i_g_k_v` (`registry_group`,`registry_key`,`registry_value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
varchar的长度明明设置的是255啊。怎么会报出767 bytes的错误呢？
我们在看看错误提示：
[Err] 1071 - Specified key was too long; max key length is 767 bytes。
这个就是因为联合所以长度限制的。
我们来看看MySql InnoDB引擎对索引长度的限制：
mysql单索引限制：
在默认情况下，InnoDB对单一的字段索引长度限制最大为767个字节。
这个长度怎么来的呢 ？当mysql创建数据库的时候，字符集使用的是UTF-8的时候，我们知道UTF-8每个字符使用三个字节来存储的。即：256*3-1=767了。这个767字符大小的限制就是从这里来的。
联合索引(前缀索引)限制：
同样的，mysql对前缀索引也有同样的限制。根据字符集不同，长度限制也不同。
字符集使用utf8的时候长度限制是:767个
使用uft8mb4的时候长度限制是：3072个
但是，在文章一开始，凯哥就强调了，凯哥数据库使用的字符集是：utf8mb4。我们也知道，utf8mb4编码的每个字符使用四个字节来存储的。我们来计算下：256*4-1>767。
从凯哥的sql脚本可以看出：KEY `i_g_k_v` (`registry_group`,`registry_key`,`registry_value`) 使用到了联合索引(前缀索引)。
随意凯哥数据库的字符集使用的不是utf8，但是mysql系统变量innodb_large_prefix未开启(因为凯哥使用的是默认配置)。如果系统变量innodb_large_prefix开启了，就会对使用dynamic或者是comperssed行格式的InnoD表，索引键长度限制为3072个字节了。如果没有开启这个，InnoDB会对，无论什么表索引键长度限制都是767了。
解决方案有两种：
1：mysql系统变量innodb_large_prefix开启。
需要修改配置，重启mysql服务等等。。。太麻烦了。凯哥这里使用了第二种方案
2：修改联合主键中每个字段的长度
联合主键的三个字段如下：
`registry_group` varchar(50) NOT NULL,
`registry_key` varchar(255) NOT NULL,
`registry_value` varchar(255) NOT NULL,
将varchar(255)的修改为varchar(100)后(注：这里的长度根据自己需求进行设置。如果非要用255个字符以上，请选择使用第一种解决方案)，sql脚本就执行成功。如下图：
延伸知识点：
当遇到如下错误：
错误信息是3072的时候，说明开启了系统变量，但是还是超长了。这个时候，可以采用同样的方案来处理.
Mysql优化的时候：
在做 sql 优化时，对之前的表添加个索引，却给出了 Specified key 'idx_t' was too long; max key length is 767 bytes 提示，后来通过查询资料，发现：
由于 MySQL Innodb 引擎表索引字段长度的限制为 767 字节，因此对于多字节字符集的大字段（或者多字段组合索引），创建索引会出现上面的错误。 以 utf8mb4 字符集 字符串类型字段为例：utf8mb4 是 4 字节字符集，则默认支持的索引字段最大长度是： 767 字节 / 4 字节每字符 = 191 字符，因此在 varchar(255) 或 char(255) 类型字段上创建索引会失败。
于是，改了下字段的长度，然后再把优化后的 sql 和之前的 sql 进行对照，果真快了不少。