【mysql】mysql字符集设置为:utf8mb4,创建表时候错误Specified key was too long; max key length is 767 bytes

  • 作者: 凯哥Java(公众号:凯哥Java)
  • 经验分享
  • 时间:2020-01-05 14:04
  • 2296人已阅读
简介 mysql在创建数据库的时候,字符集设置的不是utf8而是utf9mb4,在导入sql脚本的时候,发现提示如下错误:从上图中,我们可以看出,使用的是innodb及字符集。错误提示是长度太长了:Specifiedkeywastoolong;maxkeylengthis767bytes来查看下创建表的语句:CREATETABLE`xxl_job_registry`( `id`int(11)N

🔔🔔好消息!好消息!🔔🔔

 如果您需要注册ChatGPT,想要升级ChatGPT4。凯哥可以代注册ChatGPT账号代升级ChatGPT4

有需要的朋友👉:微信号 kaigejava2022

3c7a3ba34ced56a96b967687f1b4106d.jpg

mysql在创建数据库的时候,字符集设置的不是utf8而是utf9mb4,在导入sql脚本的时候,发现提示如下错误:

f66fd3d0d16507aa3dcab44f31e3dee0.png

从上图中,我们可以看出,使用的是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脚本就执行成功。如下图:

9cd1bc31a73e85a86947f1cbf29dd273.png

延伸知识点:

当遇到如下错误:

6a70b383458b5afbbe7dc88591d7684c.png

错误信息是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 进行对照,果真快了不少。

TopTop