mysql报错分析：Specified key was too long

博主：早安向日葵
发布时间：2022 年 09 月 12 日
5122 次浏览
21 条评论
2822字数
分类：技术分享

### 1.问题复盘

最近在做一个应用迁移的时候，需要做一个数据库的迁移，我直接将原来的sql脚本直接在系统上run，发现居然报错了，还是在建表的时候报的错：

```sql
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
```

很奇怪，明明是相同的sql脚本，但是换了一个环境就报了这个错了，上面报错的意思是索引字段长度太长，超过了767字节，立刻想到几种可能进行排查：

- mysql引擎不同
- 使用的表字符集不同
- mysql配置或版本不同

看一下报错的建表语句：

```sql
CREATE TABLE IF NOT EXISTS `cluster` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
    `gmt_create` datetime NOT NULL COMMENT '创建时间',
    `gmt_modified` datetime NOT NULL COMMENT '修改时间',
    `cluster_id` varchar(200) DEFAULT NULL COMMENT 'cluster',
    `device_id` varchar(500) DEFAULT NULL COMMENT 'device',
    `physical_id` varchar(300) DEFAULT NULL COMMENT 'physical_id',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_physical_cluster_id` (`physical_id`)
    UNIQUE KEY `uk_cluster_asw` (`cluster_id`,`device_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=341 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='xxx';
```

前两种可以马上排除，因为用的ENGINE和CHARSET是一致的，然后对比数据库版本，确实不同，线上的是8.0的而使报错的这个是5.6版本的mysql server。差别很大，下面具体说。

### 2.原因分析

#### 2.1 InnoDB和Myisam

首先，InnoDB和Myisam在索引长度上是有所区别的，其中：

1. **InnoDB存储引擎，每个列的长度不能大于767 bytes；所有组成索引列的长度和不能大于3072 bytes。**
2. **myisam存储引擎，每个列的长度不能大于1000 bytes，所有组成索引列的长度和不能大于1000 bytes。**

我这里使用的是InnoDB引擎，所以主要介绍InnoDB的情况，一般表使用字符集是utf8或者utf8mb4（能支持emoji表情等），utf8是三个字节，也就是说utf8单列索引最大长度为767/3=255（varchar 255），联合索引所有字段加起来不能超过3072/3=1024。utf8mb4则支持的索引长度更小。网上还有说使用latin可以使得支持的长度更长，但是在中国用latin1很不友好。

#### 2.2 InnoDB能不能支持更长索引

可以。这也是今天遇到这个问题的原因。**在MySQL 5.6以及以前的版本，InnoDB引擎默认索引长度不能超过767 bytes**（正好报错的版本是5.6）。解决办法是可以开启innodb_large_prefix让InnoDB引擎默认索引长度达到3072 bytes。这样就可以支持无论是单列索引还是联合索引都能达到3072字节的上限（不能再多了），但是如1中的索引就都满足约束了。

```sql
# 300 * 4 = 1200 < 3072
UNIQUE KEY `uk_physical_cluster_id` (`physical_id`)
# (300+200) * 4 = 2800 < 3072
UNIQUE KEY `uk_cluster_asw` (`cluster_id`,`device_id`) 
```

```sql
# MySQL 5.6以及以前的版本可执行
set global innodb_large_prefix=1;
set global innodb_file_format=BARRACUDA;
```

但是，为什么高版本的mysql server就没有这个问题呢？
原来，在MySQL 5.6默认开启innodb_large_prefix=ON这个配置，因此自然就能使得单列或联合索引达到3072字节的最大限制，到MySQL 8.0以后更简单粗暴，直接移除了innodb_large_prefix、innodb_file_format等变量，row format为DYNAMIC或COMPRESSED能够让索引长度达到3072 bytes，REDUNDANT和COMPACT只有767 bytes，MySQL 8.0默认innodb_default_row_format=DYNAMIC，所以高版本不用动，只要是InnoDB引擎就能满足。
至此，这个问题的原因总算解决了，至于为什么是767和3072？找了一些博客，参考文章中有解答：

> 767是历史问题，char最大是255，以前设计者以为一个长度255的索引就够用了，以前UTF8最大支持3个字节，256x3-1=767；InnoDB一个page的默认大小是16k，由于是Btree组织，要求叶子节点上一个page至少要包含两条记录，所以一个记录最多不能超过8k，又由于InnoDB的聚簇索引结构，一个二级索引要包含主键索引，因此每个单个索引不能超过4k (极端情况，primay-key和某个二级索引都达到这个限制)，由于需要预留和辅助空间，减掉后不能超过3500，取个“整数”即1024bytes*3=3072bytes。

至此，问题解决！

### 参考文章

1.[MySQL创建联合索引报key长度超3072 bytes的[42000][1071]错误_App Store公众号小程序:分享录的博客-CSDN博客](https://blog.csdn.net/xubingtao/article/details/123824946)

最后修改：2022 年 09 月 12 日

如果觉得我的文章对你有用，请随意赞赏

21 条评论

fmpccpcbhh
March 4th, 2025 at 04:17 pm

作者以简洁明了的语言，传达了深刻的思想和情感。

回复
okndyqafds
March 3rd, 2025 at 01:47 am

建议补充国内外研究对比，以拓展视野。

回复
byfavscexr
March 2nd, 2025 at 09:28 pm

这篇文章如同一幅色彩斑斓的画卷，每一笔都充满了独特的创意。

回复
kryepnefkc
March 1st, 2025 at 11:44 am

语言通俗易懂，适合目标读者群体。

回复
jjzxzulncs
March 1st, 2025 at 11:42 am

建议在揭露问题时提供建设性解决方案。

回复
fgeanwxaew
March 1st, 2025 at 02:57 am

作者对主题的挖掘深入骨髓，展现了非凡的洞察力和理解力。

回复
oifdcigdhb
February 28th, 2025 at 07:17 pm

内容的丰富性和深度让人仿佛置身于知识的海洋，受益匪浅。

回复
cefmggnhuu
January 6th, 2025 at 02:05 pm

哈哈哈，写的太好了https://www.lawjida.com/

回复
spkqeurslu
December 26th, 2024 at 07:11 pm

《十二道锋味》大陆综艺高清在线免费观看：https://www.jgz518.com/xingkong/114183.html

回复
xzzsrxahhp
December 8th, 2024 at 11:17 pm

《抖包袱大会》大陆综艺高清在线免费观看：https://www.jgz518.com/xingkong/1894.html

回复
wyylkgayhd
November 29th, 2024 at 07:07 pm

《爱你久别重逢(经典珍藏版)》短片剧高清在线免费观看：https://www.jgz518.com/xingkong/154833.html

回复
ikgwmtqtpp
November 26th, 2024 at 11:07 am

《第一杀手王亚樵》国产剧高清在线免费观看：https://www.jgz518.com/xingkong/38064.html

回复
mmlaaxzocw
November 20th, 2024 at 07:22 am

真好呢

回复
gshunoiiej
November 13th, 2024 at 05:03 pm

《阿约提亚－英雄之旅》喜剧片高清在线免费观看：https://www.jgz518.com/xingkong/135803.html

回复
pebbcddxfg
October 19th, 2024 at 02:45 pm

兄弟写的非常好 https://www.cscnn.com/

回复
pisbudjkij
October 5th, 2024 at 08:13 pm

看的我热血沸腾啊www.jiwenlaw.com

回复
praqcsbjgh
September 27th, 2024 at 12:49 pm

怎么收藏这篇文章？

回复
ftzrrrqstn
September 23rd, 2024 at 05:10 pm

叼茂SEO.bfbikes.com

回复
zmxjlbicvx
September 23rd, 2024 at 09:15 am

不错不错，我喜欢看

回复
yrxxxpyzbc
September 23rd, 2024 at 02:19 am

叼茂SEO.bfbikes.com

回复
kdpckmliup
September 22nd, 2024 at 06:33 pm

博主真是太厉害了！！！

回复

发表评论取消回复

评论 *

私密评论

名称 *

🎲

邮箱 *

地址

思维峰网络
我们汇聚了一批互联网技术和营销领域的专业人才，专注于为企业提供...
思维峰网络
我们汇聚了一批互联网技术和营销领域的专业人才，专注于为企业提供...
yqilponuqd
文章紧扣主题，观点鲜明，展现出深刻的思考维度。
dltkniarin
文章结构紧凑，层次分明，逻辑严密，让人一读即懂。
fmpccpcbhh
作者以简洁明了的语言，传达了深刻的思想和情感。

mysql报错分析：Specified key was too long

早安向日葵 • 2022 年 09 月 12 日

### 1.问题复盘

最近在做一个应用迁移的时候，需要做一个数据库的迁移，我直接将原来的sql脚本直接在系统上run，发现居然报错了，还是在建表的时候报的错：

```sql
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
```

- mysql引擎不同
- 使用的表字符集不同
- mysql配置或版本不同

看一下报错的建表语句：

### 2.原因分析

#### 2.1 InnoDB和Myisam

首先，InnoDB和Myisam在索引长度上是有所区别的，其中：

#### 2.2 InnoDB能不能支持更长索引

```sql
# 300 * 4 = 1200 < 3072
UNIQUE KEY `uk_physical_cluster_id` (`physical_id`)
# (300+200) * 4 = 2800 < 3072
UNIQUE KEY `uk_cluster_asw` (`cluster_id`,`device_id`) 
```

```sql
# MySQL 5.6以及以前的版本可执行
set global innodb_large_prefix=1;
set global innodb_file_format=BARRACUDA;
```

至此，问题解决！

### 参考文章

1.[MySQL创建联合索引报key长度超3072 bytes的[42000][1071]错误_App Store公众号小程序:分享录的博客-CSDN博客](https://blog.csdn.net/xubingtao/article/details/123824946)

mysql报错分析：Specified key was too long

21 条评论

发表评论取消回复

ubuntu中如何挂载移动硬盘

mysql报错分析：Specified key was too long

tcpdump详解

HHU《算法导论》期末

maven中依赖冲突的解决

Hexo博客换电脑了如何搬移

JAVA设计模式（一）面向对象设计原则

maven中依赖冲突的解决

mysql报错分析：Specified key was too long

hexo博客部署到云服务器

mysql报错分析：Specified key was too long

21 条评论

发表评论 取消回复

mysql报错分析：Specified key was too long

发表评论取消回复