MySQL数据库的数据从utf8转utf8mb4笔记【20210629】

发表于 2021-06-29
阅读 60

介绍

介绍

福哥在将数据库里的数据编码从utf8转为utf8mb4的时候得到了如下的错误:

Specified key was too long; max key length is 3072 bytes

这是怎么回事呢?怎么解决呢?

原因

从字面理解就是索引长度超限了,最大的长度是3072个字节。福哥的数据表的有一个字段是1000个字符长度的varchar,utf8编码一个字符需要三个字节空间,而utf8mb4一个字符需要四个字节空间(从名字也可以看出来utf8mb4就是utf8 max bytes 4的缩写)。

utf8编码下,1000*3 < 3072是装得下的,utf8mb4编码下,1000*4 > 3072就装不下了,所以同样的内容转换成utf8mb4后就报错了。

前缀索引

既然我们的字段长度使用utf8mb4超过了最大长度3072个字节,那么直接存储是行不通了!

MySQL设计了一个前缀索引机制,就是说我们可以规定一个数值n,MySQL会把字段的前n个字符索引起来。这样做虽然会损失一部分性能,但是可以解决无法创建数据表的问题。

前缀索引长度

前缀索引默认长度最大是767个字节,如果不够用的话需要开启大前缀索引支持。

set global innodb_large_prefix = on;

set global innodb_file_format = Barracuda;

并且数据表的数据行格式必须是DYNAMIC或者COMPRESSED,可以通过ROW_FORMAT设置。

ROW_FORMAT=DYNAMIC

设置前缀索引

福哥设置3072后重新创建数据表得到了下面的错误:

Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys

我了个去,索引前缀长度不能比索引字段大,这个可怎么办啊?

后来福哥设置和索引长度一样的1000后重新创建数据表,居然成功了!

设置前缀索引

一个索引里面可以为每个字段设置前缀,也可以只为部分字段设置前缀。但是MySQL对索引字段长度总量有要求,下面我们就来一一介绍一下。

单字段索引

如果索引只有一个varchar字段,那么前缀索引长度写多少都可以,只要不超过字段本身长度即可。

home/topic/2021/0629/13/19521a1c4f1c638e751d8e3d91839967.png

组合索引

组合索引的话,全部字段长度加一起不能超过768就可以。(这是什么鸟规定啊。。。)

data和data2,600 + 168 = 768,刚刚好!

home/topic/2021/0629/13/9d42fcdfd9dafbc3875f7e3f239f9871.png

arg1、arg2和data,1 + 1 + 766 = 768,也是刚刚够数!

home/topic/2021/0629/13/057660bb6ddd3ba3f72882d621562174.png

总结

今天福哥带着童鞋们学习了MySQL数据库的前缀索引的使用技巧,通过前缀索引可以让我们为那些巨大的字段设置索引,还可以为字段很大数据很短的字段设计合理的索引长度。

鬼谷子叔叔

跟福哥学编程吧~~
日志
212
浏览
1626