一次 emoji 导致的 mysql 插入异常排查

问题

通过 jdbc 向数据库中插入数据时，由于字段值有 emoji。导致错误：
Caused by: java.sql.SQLException: Incorrect string value: '\xF0\xA0\xBB\xB8\xE5\x90...' for column 'address' at row 1

• 环境

库本身的字符集设置为 utf8。
但是 character_set_client，character_set_connection，character_set_results 均为 utf8mb4。
utf8mb4 是 utf8 的超集（参考：https://blog.csdn.net/vsiryxm/article/details/82919643）

MySQL [(none)]> show variables like 'character%';
+--------------------------+---------------------------------------+
| Variable_name            | Value                                 |
+--------------------------+---------------------------------------+
| character_set_client     | utf8mb4                               |
| character_set_connection | utf8mb4                               |
| character_set_database   | utf8                                  |
| character_set_filesystem | binary                                |
| character_set_results    | utf8mb4                               |
| character_set_server     | utf8                                  |
| character_set_system     | utf8                                  |
| character_sets_dir       | /u01/mysql57_20190925/share/charsets/ |
+--------------------------+---------------------------------------+

建表语句

CREATE TABLE `detail` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `address` varchar(100) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '地址',
) ENGINE=InnoDB AUTO_INCREMENT=6162 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

可见：

• 表本身编码为 utf8mb4，是支持写入 emoji 的。
•  character_set_client，character_set_connection，character_set_results 均为 utf8mb4。这说明客户端只要正常连上，不做任何字符集设置修改，就能够写入 emoji。

排查

• 问题出在客户端 jdbc 配置上。

查看 jdbc 的连接串：

connectTimeout=3000;autoReconnect=true;failOverReadOnly=false;socketTimeout=900000;rewriteBatchedStatements=true;characterEncoding=utf8

可以看到 characterEncoding=utf8 的设置。

参看 mysql 官方文档（https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html）

characterEncoding： If 'useUnicode' is set to true, what character encoding should the driver use when dealing with strings? 

而 useUnicode 默认为 true。

可见：正是 characterEncoding=utf8 这个设置导致了 emoji 无法正确插入。

解决

1. 连接串的 characterEncoding=utf8 改成 characterEncoding=utf8mb4

或者

2. 添加 connectionInitSql 参数

connectTimeout=3000;autoReconnect=true;failOverReadOnly=false;socketTimeout=900000;rewriteBatchedStatements=true;characterEncoding=utf8;connectionInitSql=set names utf8mb4

延伸阅读

• mysql  jdbc 连接串中所有参数

https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html

• utf8 与 utf8mb4  区别

https://blog.csdn.net/vsiryxm/article/details/82919643                 
utf8mb4 是 utf8 的超集，除了将编码改为utf8mb4外，不需要做其他转换。

• mysql 的 set names 做了什么？

https://blog.csdn.net/justsomebody126/article/details/103728644

 

实践插入 emoji

https://www.jianshu.com/p/728702fd6087

CREATE TABLE `emoji` (
  `id` int(11) NOT NULL,
  `value` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET names utf8mb4;
insert into emoji values(null,'😀');