写点什么

这个线上 BUG,让你彻底搞懂了 MySQL 的字符集,别问我咋知道的

作者:Java你猿哥
  • 2023-05-27
    湖南
  • 本文字数:2824 字

    阅读完需:约 9 分钟

那天,我还在外面吃成都六姐的冒菜。

牛肉丸裹上麻酱后,狠狠嘬一口,都要入嘴了。

产品经理突然发来消息。

"线上有些用户不能注册了"

心想着"关我 x 事,又不是我做的模块",放下手机。

不对,那老哥上礼拜刚离职了,想到这里,夹住毛肚的手微微颤抖

对面继续发:"还有些用户不能改名"


"如果用上表情符号的话,问题必现"

可以了,这下问题几乎直接定位了。

危,速归。


有经验的兄弟们很容易看出,这肯定是因为字符集的缘故。


复现问题

我们来简单复现下这个问题。

如果你有一张数据库表,建表 sql 就像下面一样。


建表 sql 语句

接下来如果你插入的数据是


insert 成功 case

能成功。一切正常。

但如果你插入的是


insert 失败 case

就会报错

Incorrect string value: '\xF0\x9F\x98\x81' for column 'name' at row 1
复制代码

区别在于后者多了个 emoji 表情。

明明也是字符串,为什么字符串里含有 emoji 表情,插入就会报错呢?

我们从字符集编码这个话题开始聊起。


编码和字符集的关系

虽然我们平时可以在编辑器上输入各种中文英文字母,但这些都是给人读的,不是给计算机读的,其实计算机真正保存和传输数据都是以二进制 0101 的格式进行的。

那么就需要有一个规则,把中文和英文字母转化为二进制,比如"debug",计算机就需要把它转化为下图这样。


debug 的编码

其中 d 对应十六进制下的 64,它可以转换为 01 二进制的格式。

于是字母和数字就这样一一对应起来了,这就是 ASCII 编码格式。

它用一个字节,也就是 8 位来标识字符,基础符号有 128 个,扩展符号也是 128 个。

也就只能表示下英文字母和数字

这哪里够用。

塞牙缝都不够。

于是为了标识中文,出现了 GB2312 的编码格式。为了标识希腊语,出现了 greek 编码格式,为了标识俄语,整了 cp866 编码格式。

这百花齐放的场面,显然不是一个爱写 if else 的程序员想看到的。

为了统一它们,于是出现了 Unicode 编码格式,它用了 2~4 个字节来表示字符,这样理论上所有符号都能被收录进去,并且它还完全兼容 ASCII 的编码,也就是说,同样是字母 d,在 ASCII 用 64 表示,在 Unicode 里还是用 64 来表示。

不同的地方是 ASCII 编码用 1 个字节来表示,而 Unicode 用则两个字节来表示。

比如下图,同样都是字母 d,unicode 比 ascii 多使用了一个字节。


unicode 比 ascii 多使用一个字节

我们可以注意到,上面的 unicode 编码,放在前面的都是 0,其实用不上,但还占了个字节,有点浪费,完全能隐藏掉。如果我们能做到该隐藏时隐藏,这样就能省下不少空间,按这个思路,就是就有了 UTF-8 编码


编码格式

来总结下。

按照一定规则把符号和二进制码对应起来,这就是编码。而把 n 多这种已经编码的字符聚在一起,就是我们常说的字符集

比如 utf-8 字符集就是所有 utf-8 编码格式的字符的合集。


字符和字符集的关系


mysql 的字符集

想看下 mysql 支持哪些字符集。可以执行 show charset;


数据库支持哪些字符集

上面这么多字符集,我们只需要关注 utf8 和 utf8mb4 就够了。


utf8 和 utf8mb4 的区别

上面提到 utf-8 是在 unicode 的基础上做的优化,既然 unicode 有办法表示所有字符,那 utf-8 也一样可以表示所有字符,为了避免混淆,我在后面叫它大 utf8

而从上面 mysql 支持的字符集的图里,我们看到了 utf8 和 utf8mb4。

先说 utf8mb4 编码,mb4 就是 most bytes 4 的意思,从上图最右边的 Maxlen 可以看到,它最大支持用 4 个字节来表示字符,它几乎可以用来表示目前已知的所有的字符。

再说 mysql 字符集里的 utf8,它是数据库的默认字符集。但注意,此 utf8 非彼 utf8,我们叫它小 utf8 字符集。为什么这么说,因为从 Maxlen 可以看出,它最多支持用 3 个字节去表示字符,按 utf8mb4 的命名方式,准确点应该叫它 utf8mb3

不好意思,有被严谨到的兄弟们,评论区扣个"严谨"。

它就像是阉割版的 utf8mb4,只支持部分字符。比如 emoji 表情,它就不支持。


utf8mb3 和 utf8mb4 的关系


而 mysql 支持的字符集里,第三列,collation,它是指字符集的比较规则

比如,"debug"和"Debug"是同一个单词,但它们大小写不同,该不该判为同一个单词呢。

这时候就需要用到 collation 了。

通过 SHOW COLLATION WHERE Charset = 'utf8mb4';可以查看到 utf8mb4 下支持什么比较规则。


utf8mb4 字符集比较规则

如果 collation = utf8mb4_general_ci,是指使用 utf8mb4 字符集的前提下,挨个字符进行比较(general),并且不区分大小写(_ci,case insensitice)。

这种情况下,"debug"和"Debug"是同一个单词


对比规则-大小写不敏感

如果改成 collation=utf8mb4_bin,就是指挨个比较二进制位大小

于是"debug"和"Debug"就不是同一个单词。


对比规则-大小写敏感


那 utf8mb4 对比 utf8mb3 有什么劣势吗?

我们知道数据库表里,字段类型如果是 char(2)的话,里面的 2 是指字符个数,也就是说不管这张表用的是什么编码的字符集,都能放上 2 个字符。

而 char 又是固定长度,为了能放下 2 个 utf8mb4 的字符,char 会默认保留 2*4(maxlen=4)= 8 个字节的空间。

如果是 utf8mb3,则会默认保留 2 * 3 (maxlen=3) = 6 个字节的空间。也就是说,在这种情况下,utf8mb4 会比 utf8mb3 多使用一些空间。

但这真的无关紧要,如果我不用 char,用 varchar 就好了,varchar 不是固定长度,也就没有上面这些麻烦事了。

所以我个人认为,utf8mb4 比起 utf8mb3 几乎没有劣势。


如何查看数据库表的字符集

如果我们不知道自己的表是用的哪种字符集,可以通过下面的方式进行查看。


查看数据库表的字符集


再看报错原因

到这里,我们回到文章开头的问题。

因为数据库表在建表的时候使用 DEFAULT CHARSET=utf8, 相当于指定了 utf8mb3 字符集格式。

而在执行 insert 数据的时候,又不讲武德,加入了 emoji 表情这种 utf8mb4 才能支持的字符,mysql 识别到这是 utf8mb3 不支持的字符,于是忍痛报错。

要修复也很简单,执行下面的 sql 语句,就可以把数据库表的字符集改成 utf8mb4。

ALTER TABLE user CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
复制代码

答应我,以后建表,我们都无脑选 utf8mb4。

选 utf8 除了在 char 字段场景下会比 utf8mb4 稍微省一点空间外,几乎没任何好处。

这点空间省下来了能提高你的绩效吗?不能。

但如果因此炸雷了,那你号就没了。


总结

  • ASCII 编码支持数字和字母。大佬们为了支持中文引入了 GB2312 编码格式,其他国家的大佬们为了支持更多语言和符号,也引入了相应的编码格式。为了统一这些各种编码格式,大佬们又引入了 unicode 编码格式,而 utf-8 则在 unicode 的基础上做了优化,压缩了空间。

  • mysql 默认的 utf8 字符集,其实只是 utf8mb3,并不完整,当插入 emoji 表情等特殊字符时,会报错,导致插入、更新数据失败。改成 utf8mb4 就好了,它能支持更多字符。

  • mysql 建表时如果不知道该选什么字符集,无脑选 utf8mb4 就行了,你会感谢我的。


最后

原本 A 同学设计这张表的时候非常简单,也有字符串类型的字段,但字段含义决定了肯定不会有奇奇怪怪的字符,用 utf8 很合理,还省空间。

后来交接给了 B 同学,B 同学在这基础上加过非常多的字段,离职前最后一个需求加的这个名称字段,所幸并没炸雷。最后到了我这里。

好一个击鼓传雷

有点东西哦。


那么问题来了。

这样的一个事故,复盘会一开,会挂 P 几呢?


别说了,一起在知识的海洋里呛水吧

用户头像

Java你猿哥

关注

一只在编程路上渐行渐远的程序猿 2023-03-09 加入

关注我,了解更多Java、架构、Spring等知识

评论

发布
暂无评论
这个线上BUG,让你彻底搞懂了MySQL的字符集,别问我咋知道的_Java_Java你猿哥_InfoQ写作社区