让 MySQL 支持存储 emoji 表情

16,935 阅读5分钟

先重现下问题,创建数据库 test_db

create database test_db default charset utf8 default collate utf8_general_ci;

创建数据表

CREATE TABLE `article` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(120) NOT NULL DEFAULT '' COMMENT '标题',
  `abstract` varchar(600) NOT NULL DEFAULT '' COMMENT '摘要',
  `created_at` int(11) NOT NULL DEFAULT '0',
  `updated_at` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='文章表'

由于数据库的字符集和排序规则分别是 utf8utf8_general_ci,创建数据表时虽没有指定字符集以及对应的排序规则,则取数据库的配置。

使用 PyMySQL 连接数据库,插入一个 emoji 表情

# coding=utf-8

import pymysql

connection = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd',
                             db='db',
                             charset='utf8',
                             cursorclass=pymysql.cursors.DictCursor)

try:
    with connection.cursor() as cursor:
        sql = "INSERT INTO article (title) VALUES ('😄')"
        cursor.execute(sql)

    connection.commit()
finally:
    connection.close()

执行后报如下错误

pymysql.err.InternalError: (1366, "Incorrect string value: '\\xF0\\x9F\\x98\\x84' for column 'title' at row 1")

之所以出现这个错误,是因为 MySQL 的 utf8 字符集存储不了 emoji 表情。

诶,不会吧,utf8 不是一种 unicode 编码吗,不是应该支持世界上大部分的字符吗?我们平常说的 utf8 确实是这样的,但是 MySQL 中的 utf8 其实是阉割版的 utf8,它最多只用 3 个字节存储字符,所以存储不了表情,这个 utf8 其实是 utf8mb3 的别名。

如果我们要支持表情的存储,我们需要完整的 utf8 字符集,最多可以用 4 个字节来存储字符,这个字符集名字叫 utf8mb4。

所以,要支持表情的存储,我们将数据库的字符集改为 utf8mb4 就可以了。怎么改呢?

首先修改表 article 中类型为字符串的列

ALTER TABLE article MODIFY title varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '标题';
ALTER TABLE article MODIFY abstract varchar(600) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '摘要';

为了让新增的列的字符串也是 utf8mb4 的字符集,我们可以修改表的字符集

ALTER TABLE article DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

如果想让新增的表默认也是这个字符集,可以修改数据库的字符集

ALTER DATABASE test_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

具体是修改列、表还是库,按照你的需要自行决定。

修改脚本中连接数据库的字符集,将

charset='utf8'

改为

charset='utf8mb4'

重新执行脚本,会发现执行成功。

到这里,我们可以说是修改完成了,但是我觉得有些细节必须提下。不然,当出现问题的时候,还是不知道啥情况。

使用客户端连接数据库的时候,我们可以指定默认编码

$ mysql -u root -p --default-character-set=utf8mb4

在配置数据库的时候,我们发现在 client 下也可以配个类似的配置

[client]
default-character-set=utf8mb4

就是说,我们连接的时候,如果没有指定字符集,则取配置文件中的值;否则,取指定的字符集。

使用不同的字符集连接数据库的时候,会影响下面 3 个变量

character_set_client
character_set_connection
character_set_results

比如,连接时如果没有指定字符集,则会取配置 utf8mb4,这 3 个变量的值都是 utf8mb4;如果指定 字符集 utf8,则这 3 个变量的值都是 utf8。这 3 个变量是可以动态修改的

SET character_set_client = utf8mb4;
SET character_set_connection = utf8mb4;
SET character_set_results = utf8mb4;

也可以使用下面的命令代替,功能和上面三条语句一致

SET NAMES utf8mb4;

当这 3 个变量值与 数据库的字符集不统一的时候,就有可能出错或乱码。

如果选择数据库 test_db 并设置如下

use test_db;
SET NAMES utf8;

执行

INSERT INTO article (title) VALUES ('😄');

报错

Incorrect string value: '\xF0\x9F\x98\x84' for column 'title' at row 1

如果选择数据库 test_db 并设置如下

use test_db;
SET character_set_client = ascii;
SET character_set_connection = utf8mb4;
SET character_set_results = ascii;

执行

INSERT INTO article (title) VALUES ('😄');

我们会发现存入的数据乱码。

所以,在 PyMySQL 客户端中,我们需要指定编码为 utf8mb4,从而使脚本工作正常。

这 3 个变量是怎么工作的呢?我简单说明细下。

character_set_client 指客户端请求内容的字符集,character_set_connection 指服务器处理内容的字符集,character_set_results 指服务器返回的响应的字符集。

我们都知道计算机只认 0 和 1,所以数据库服务器收到的请求或者返回的响应都是一串字节。服务器收到请求时,会将请求以 character_set_client 的字符集进行解码,然后以 character_set_connection 的字符集进行编码,然后丢给服务器处理。待处理结束后,又将结果使用 character_set_connection 的字符集进行解码,然后使用 character_set_results 的字符集进行编码返回。

PS:编码可以简单的理解为从字符串到字节串,解码相反。

因此,当 character_set_connection 的字符集范围大于 character_set_clientcharacter_set_results 的范围的时候,是有可能正常工作的。比如 character_set_connectionutf8mb4,而 character_set_clientcharacter_set_results 均为 utf8,我们执行

INSERT INTO article (title) VALUES ('你好');

但是这个前提是我们请求的 SQL 的字符范围不能超出 character_set_client,比如我们执行

INSERT INTO article (title) VALUES ('😄');

数据就会出现乱码。

同样,character_set_connection 的字符范围 也不能超过数据库的字符集的字符范围,否则也会出现乱码。

通常情况下,我们会将这 3 个变量的值统一,并设置成与数据库的字符集一致,这样配置更清晰一些。不过,是由客户端去指定字符集还是服务器配置,就得根据具体的场景去决定了。我觉得在服务器上配个 utf8 的默认字符集,然后客户端连接时按需指定也是个好办法。