[MySQL] specified key was too long max key length is 767bytes

Love The Way You Lie 2022-07-13 03:25 128阅读 0赞

## 一、问题描述 ##

描述：创建唯一索引时，数据库报错:

> specified key was too long max key length is 767bytes

数据库版本：5.5.22-log

引擎: InnoDB

数据库结构为：

CREATE TABLE `cdkey` ( `id` int(11) NOT NULL AUTO_INCREMENT, `cdkey` varchar(200) NOT NULL COMMENT '邀请码', `created_time` datetime NOT NULL COMMENT '创建时间', `updated_time` datetime NOT NULL COMMENT '修改时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COMMENT='邀请码'

创建语句如下：

alter table `cdkey` add index `INDEX` (`cdkey`);

运行报错：  
![这里写图片描述][SouthEast]

## 二、问题分析 ##

参考自：[http://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes][http_stackoverflow.com_questions_1814532_1071-specified-key-was-too-long-max-key-length-is-767-bytes]

允许索引字段前缀的字节限制为 767 bytes，且：

*  当字符集为 utf8mb4 时，一个字符对应 4 bytes;
 *  当字符集为 utf8 时，一个字符对应 3 bytes;

所以对于（一）中的问题：

字符集为 utf8mb4, 所以可申请索引的 varchar 长度为：767/4 ≈ 191；

而字符集为 utf8 时，可设置的创建索引 varchar 长度为: 767/3 ≈ 255

## 三、版本差异 ##

官方文档：[innodb\_large\_prefix][innodb_large_prefix]

在 5.6.3 之后的版本，字节限制提高到了 3072 bytes.

测试环境：5.7.9

引擎: InnoDB

数据库结构：

CREATE TABLE `test_index2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(1000) NOT NULL, `uid` int(11) NOT NULL, `created_time` datetime NOT NULL, `updated_time` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

运行索引创建命令：

alter table `test_index2` add index `INDEX` (`name`);

报错为：  
![这里写图片描述][SouthEast 1]

从（二）可得：

字符集为 utf8mb4 时，限制 varchar 字符为：3072/4 = 768;

字符集为 utf8 时，限制 varchar 字符为：3072/3 = 1024;

因此字符集为 utf8mb4 时，将 name 字段索引前缀长度控制在 768 以内即可成功创建索引。

[SouthEast]: /images/20220713/858dd7e6abb64f9babb19f109fbe774b.png
[http_stackoverflow.com_questions_1814532_1071-specified-key-was-too-long-max-key-length-is-767-bytes]: http://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes
[innodb_large_prefix]: http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_large_prefix
[SouthEast 1]: /images/20220713/7f1537891d664865b41a9a2a31b3f590.png