docker 部署 MySQL 使用 utf8mb4 字符集

背景

utf8 是 MySQL 早期版本中支持的一种字符集,只支持最长三个字节的 UTF-8 字符,也就是 Unicode 中的基本多文本平面。这可能是因为在 MySQL 发布初期,基本多文种平面之外的字符确实很少用到。而在 MySQL5.5.3 版本后,要在 MySQL 中保存 4 字节长度的 UTF-8 字符,就可以使用 utf8mb4 字符集了。例如可以用 utf8mb4 字符编码直接存储 emoj 表情,而不是存表情的替换字符。

正文

相信好多人都被 emoji 表情在 MySQL 中存储的问题坑过,被坑过的人都记住了在 MySQL 中创建库表时要使用 utf8mb4,而不是 utf8。

但如果 MySQL 的服务端没有设置为 utf8mb4 的话,使用 jdbc 往里写 emoji 同样会报错,即便是指定了 characterEncoding=utf8 也不会起作用,并且 characterEncoding 不支持设置为 utf8mb4

报错通常为:

1
Incorrect string value: '\xF0\x9F...' for column 'xxx' at row 1

解决办法是将服务端的默认字符集改为 utf8mb4。我们的 MySQL 是使用 docker 启动的,需要把配置文件映射进去。在这里我们踩过一次坑,之前我们是将 my.conf 文件映射到容器的 /etc/my.cnf,实际使用时发现配置文件并没有生效,需要映射到 /etc/mysql/my.cnf 才能生效。

解决问题:

先查看一下当前数据库的字符集:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
> show variables like '%char%';

+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

发现默认都是 latin1,现在我们在 my.conf 中的相应模块中加入如下配置,然后重启 MySQL:

1
2
3
4
5
6
7
8
9
[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci

再次查看字符集:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
> show variables like '%char%';

+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

发现已经更改为了 utf8mb4,再次尝试插入 emoji 成功,问题解决。

补充说明:

jdbc 在连接 MySQL 时,如果不指定 characterEncoding 会默认使用 MySQL 服务端 的字符集,因为之前我们的 MySQL 服务端字符集为 latin1,所以手动指定了一下 characterEncoding=utf8,但这样使用的是 utf8 编码建立连接,所以依旧不能插入 emoji。

在修改为 utf8mb4 之后也就不用设置 characterEncoding=utf8useUnicode=true 参数了(我尝试了下,不去掉也没有什么问题)。

docker MySQL 启动命令

1
docker run --name mysql --net host -v /data04/docker/mysql:/var/lib/mysql -v /opt/tianhe/mysql/my.cnf:/etc/mysql/my.cnf -v /opt/data:/opt/data -e "MYSQL_ROOT_PASSWORD=xxxxxx" -d mariadb:10.2