让 MySQL 支持存储 emoji 表情

先重现下问题，创建数据库 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='文章表'
复制代码

由于数据库的字符集和 排序 规则分别是 utf8 和 utf8_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 (':smile:')"
        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 (':smile:');
复制代码

报错

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 (':smile:');
复制代码

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

所以，在 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_client 和 character_set_results 的范围的时候，是有可能正常工作的。比如 character_set_connection 为 utf8mb4 ，而 character_set_client 和 character_set_results 均为 utf8 ，我们执行

INSERT INTO article (title) VALUES ('你好');
复制代码

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

INSERT INTO article (title) VALUES (':smile:');
复制代码

数据就会出现乱码。

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

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