跳至主要內容

mysql 插入数据会失败,为什么?

小白debug大约 11 分钟

mysql 插入数据会失败,为什么?


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

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

产品经理突然发来消息。

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

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

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

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

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

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

危,速归。


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


复现问题

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

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

建表sql语句
建表sql语句

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

insert成功case
insert成功case

能成功。一切正常。

但如果你插入的是

insert失败case
insert失败case

就会报错

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

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

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

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


编码和字符集的关系

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

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

debug的编码
debug的编码

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

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

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

将它能表示的所有字符,给聚在一起,这就是我们常说的ASCII 字符集

因为使用的字节数过少,因此也就只能表示下英文字母和数字

这哪里够用。

塞牙缝都不够。

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

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

为了统一它们,于是出现了Unicode 字符集,它用了 2~4 个字节来表示字符,简单的字符就用 2 个字节表示,复杂的就用 3 到 4 个字节。这样理论上所有符号都能被收录进去,并且完全兼容 ASCII 码,也就是说,同样是字母 d,在 ASCII 用 64 表示,在 Unicode 里还是用 64 来表示。但unicode 本身只是个字符集,它只规定了某个字符的具体数字是多少,但并没有规定这些数字是要用几个字节去存。

如果你用UTF-32的编码格式,它有 32 位,就会用 4 个字节去存。

如果你用UTF-16的编码格式,它有 16 位,就会用 2 个字节去存。

不同的地方是 ASCII 用 1 个字节来表示,而 Unicode 下,不管使用 UTF-16 还是 UTF-32 都至少要用两个字节来表示。

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

unicode比ascii多使用一个字节
unicode比ascii多使用一个字节

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

编码格式
编码格式

来总结下。

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

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

当然,这里说的 utf-8 字符集,其实就是 unicode 字符集。

字符和字符集的关系
字符和字符集的关系

mysql 的字符集

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

数据库支持哪些字符集
数据库支持哪些字符集

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


utf8 和 utf8mb4 的区别

注意下面的内容非常容易晕,大家请放慢阅读速度,关注下 utf 和 8 之间有没有横杠"-"。

上面提到 utf-8 编码是 unicode 字符集的众多编码中的一种,utf-8 也一样可以表示所有字符,为了避免混淆,我在后面叫它大 utf8

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

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

再说 mysql 字符集里的utf8,它是数据库的默认字符集。但注意,此 utf8 非彼 utf-8,我们叫它小 utf8字符集。

注意,这里的小 utf8和上面提到的大 utf8,并不是一回事。

为什么这么说,因为从 Maxlen 可以看出,它最多支持用 3 个字节去表示字符,按 utf8mb4 的命名方式,准确点应该叫它utf8mb3

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

utf8mb3和utf8mb4的关系
utf8mb3和utf8mb4的关系

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

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

这时候就需要用到 collation 了。

通过SHOW COLLATION WHERE Charset = 'utf8mb4';可以查看到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-32 和 utf-16 编码格式,而 utf-8 则在 utf-32 和 utf-16 的基础上做了优化,压缩了空间。也就是说,utf-8, utf-16, utf32 都只是 unicode 字符集的编码格式而已。
  • mysql 默认的 utf8 字符集,其实只是 utf8mb3,并不完整,当插入 emoji 表情等特殊字符时,会报错,导致插入、更新数据失败。改成 utf8mb4 就好了,它能支持更多字符。
  • mysql 建表时如果不知道该选什么字符集,无脑选 utf8mb4 就行了,你会感谢我的。

参考资料

《从根儿上理解 mysql》


最后

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

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

好一个击鼓传雷

有点东西哦。


那么问题来了。

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


最近原创更文的阅读量稳步下跌,思前想后,夜里辗转反侧。

我有个不成熟的请求。


离开广东好长时间了,好久没人叫我靓仔了。

大家可以在评论区里,叫我一靓仔吗?

我这么善良质朴的愿望,能被满足吗?

如果实在叫不出口的话,能帮我点下右下角的点赞和在看吗?


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

关注公众号:【小白 debug】


不满足于在留言区说骚话?

加我,我们建了个划水吹牛皮群,在群里,你可以跟你下次跳槽可能遇到的同事或面试官聊点有意思的话题。就超!开!心!

文章推荐: