MySQL 异常:max key length is 767 bytes

文若2年前技术文章747

前言

最近迁移几张表,又遇到 767 异常,迁移前只检查了 sql_mode 忽略对比了这个参数,导致几张表创建失败,其实解决方法也很简单,开启 innodb_large_prefix 参数重新导入即可。该 ERROR 常发生在字符串类型的大字段上,本篇文章简单记录下该问题。

42000 Specified key was too long; max key length is 767 bytes

1. 767 bytes 解读

如何知道某张表字符串字段占多少 bytes 呢?需要结合表的字符集来看:

CREATE TABLE `test_index` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `name` varchar(800) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

上表的字符类型是 utf8 我们可以通过查表来判断字符最大可占用的空间:

show character set;

CharsetDescriptionDefault collationMaxlen
big5Big5 Traditional Chinesebig5_chinese_ci2
dec8DEC West Europeandec8_swedish_ci1
cp850DOS West Europeancp850_general_ci1
hp8HP West Europeanhp8_english_ci1
koi8rKOI8-R Relcom Russiankoi8r_general_ci1
latin1cp1252 West Europeanlatin1_swedish_ci1
latin2ISO 8859-2 Central Europeanlatin2_general_ci1
swe77bit Swedishswe7_swedish_ci1
asciiUS ASCIIascii_general_ci1
ujisEUC-JP Japaneseujis_japanese_ci3
sjisShift-JIS Japanesesjis_japanese_ci2
hebrewISO 8859-8 Hebrewhebrew_general_ci1
tis620TIS620 Thaitis620_thai_ci1
euckrEUC-KR Koreaneuckr_korean_ci2
koi8uKOI8-U Ukrainiankoi8u_general_ci1
gb2312GB2312 Simplified Chinesegb2312_chinese_ci2
greekISO 8859-7 Greekgreek_general_ci1
cp1250Windows Central Europeancp1250_general_ci1
gbkGBK Simplified Chinesegbk_chinese_ci2
latin5ISO 8859-9 Turkishlatin5_turkish_ci1
armscii8ARMSCII-8 Armenianarmscii8_general_ci1
utf8UTF-8 Unicodeutf8_general_ci3
ucs2UCS-2 Unicodeucs2_general_ci2
cp866DOS Russiancp866_general_ci1
keybcs2DOS Kamenicky Czech-Slovakkeybcs2_general_ci1
macceMac Central Europeanmacce_general_ci1
macromanMac West Europeanmacroman_general_ci1
cp852DOS Central Europeancp852_general_ci1
latin7ISO 8859-13 Balticlatin7_general_ci1
utf8mb4UTF-8 Unicodeutf8mb4_general_ci4
cp1251Windows Cyrilliccp1251_general_ci1
utf16UTF-16 Unicodeutf16_general_ci4
utf16leUTF-16LE Unicodeutf16le_general_ci4
cp1256Windows Arabiccp1256_general_ci1
cp1257Windows Balticcp1257_general_ci1
utf32UTF-32 Unicodeutf32_general_ci4
binaryBinary pseudo charsetbinary1
geostd8GEOSTD8 Georgiangeostd8_general_ci1
cp932SJIS for Windows Japanesecp932_japanese_ci2
eucjpmsUJIS for Windows Japaneseeucjpms_japanese_ci3
gb18030China National Standard GB18030gb18030_chinese_ci4

例如 utf8 的 Maxlen = 3 表示一个字符最大占用 3 bytes。那么 varchar 类型小于 255 字符,就不会触发 767 异常。

2. 异常原因

为什么会有这个异常呢?其实该异常属于 MySQL Innodb 引擎的限制,例如一张表最多有 1017 个字段,一张表最大有 64 个二级索引一样,如果索引列的字符长度很大的话,二级索引占用的空间也会非常大,一个页面存储的索引字段减少,索引深度会更容易变深,增加 IO 次数,搜索的性能也不会理想。

If innodb_large_prefix is enabled (the default), the index key prefix limit is 3072 bytes for InnoDB tables that use the DYNAMIC or COMPRESSED row format. If innodb_large_prefix is disabled, the index key prefix limit is 767 bytes for tables of any row format.

Innodb 引擎的限制:14.23 InnoDB Limits  

3. 5.7 和 8.0 版本的区别

在 5.7 版本中,对于使用 DYNAMIC 或 COMPRESSED 行格式的 InnoDB 表,索引前缀的限制为 767 bytes,如果开启 innodb_large_prefix 参数,限制阈值上调为 3072 bytes。

在 8.0 版本中,参数 innodb_large_prefix 被删除,索引前缀最大为 3072 bytes 不再有 767 bytes 的限制。

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


相关文章

Kubernetes openelb

1、背景在云服务环境中的 Kubernetes 集群里,通常可以用云服务提供商提供的负载均衡服务来暴露 Service,但是在本地没办法这样操作。而 OpenELB 可以让用户在裸金属服务器、边缘以及...

ES架构模型

ES架构模型

1.整体架构Elasticsearch是一个高度可扩展的开源全文搜索和分析引擎。它允许您快速、近实时地存储、搜索和分析大量数据。它通常用作支持具有复杂搜索功能和需求的应用程序的底层引擎/技术。Elas...

prometheus黑盒监控

prometheus黑盒监控

一.背景黑盒监控:主要关注的现象,一般都是正在发生的东西,例如出现一个告警,业务接口不正常,那么这种监控就是站在用户的角度能看到的监控,重点在于能对正在发生的故障进行告警。二.操作前了解相关配置和要求...

MySQL 通过 MTS 多线程恢复增量日志备份

MySQL 通过 MTS 多线程恢复增量日志备份

一、前言当发生 DDL 误删数据时,只能通过 备份 + 增量日志 来恢复,备份恢复到新实例后,将增量日志备份进行应用,此时只能单线程效率不高,我们可以使用 MySQL 5.7 推出的 MTS 多线程复...

Tomcat调优处理

1、隐藏版本号进入tomcat的lib目录找到catalina.jar文件unzip catalina.jar之后会多出两个文件夹 进入org/apache/catalina/util编辑配置文件Se...

副本集的同步、心跳、选举、回滚

一、副本集同步过程   MongoDB的复制功能是依赖与oplog来实现的。   1.数据写入主节点同时在local.oplog.rs集合写入对应的oplog   2.备份节点第一次同步数据时,会先进...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。