mysql字符集utf8mb4失效踩坑

mysql

现象

mysql数据表的字符集已经设置成了utf8mb4，但是通过JDBC向数据库写入4字节的emoji表情时报错，但是通过直接使用命令行插入该4字节的emoji表情时却成功了。示例如下：

1
2
3
4
5
6
CREATE TABLE `user_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(11) NOT NULL,
  `age` int(4) DEFAULT NULL
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

JDBC写入失败

1
2
3
4
User user = new User();
user.setName("\uD83D\uDC8B");
user.setAge(18);
userMapper.insertUser(user);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
org.springframework.jdbc.UncategorizedSQLException: 
### Error updating database.  Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x92\x8B' for column 'name' at row 1
### The error may involve com.wakzz.database.persistence.UserMapper.insertUser-Inline
### The error occurred while setting parameters
### SQL: insert into user_info (name, age)   values   (?, ? )
### Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x92\x8B' for column 'name' at row 1
; uncategorized SQLException; SQL state [HY000]; error code [1366]; Incorrect string value: '\xF0\x9F\x92\x8B' for column 'name' at row 1; nested exception is java.sql.SQLException: Incorrect string value: '\xF0\x9F\x92\x8B' for column 'name' at row 1

	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
	at com.sun.proxy.$Proxy81.insert(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:278)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:58)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
	at com.sun.proxy.$Proxy92.insertUser(Unknown Source)

命令行写入成功

1
2
mysql> insert into user_info (name,age) values ('💋',18);
Query OK, 1 row affected

解决

修改mysql配置文件，添加以下配置：

1
character_set_server = utf8mb4

修改前字符集

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> show variables like "%char%";
+--------------------------+----------------------------------------+
| Variable_name            | Value                                  |
+--------------------------+----------------------------------------+
| character_set_client     | utf8mb4                                |
| character_set_connection | utf8mb4                                |
| character_set_database   | latin1                                 |
| character_set_filesystem | binary                                 |
| character_set_results    | utf8mb4                                |
| character_set_server     | utf8                                   |
| character_set_system     | utf8                                   |
| character_sets_dir       | /usr/soft/mysql-5.6.31/share/charsets/ |
+--------------------------+----------------------------------------+
8 rows in set

mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | latin1_swedish_ci  |
| collation_server     | utf8_general_ci    |
+----------------------+--------------------+
3 rows in set

修改后字符集

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> show variables like "%char%";
+--------------------------+----------------------------------------+
| Variable_name            | Value                                  |
+--------------------------+----------------------------------------+
| character_set_client     | utf8mb4                                |
| character_set_connection | utf8mb4                                |
| character_set_database   | latin1                                 |
| character_set_filesystem | binary                                 |
| character_set_results    | utf8mb4                                |
| character_set_server     | utf8mb4                                |
| character_set_system     | utf8                                   |
| character_sets_dir       | /usr/soft/mysql-5.6.31/share/charsets/ |
+--------------------------+----------------------------------------+
8 rows in set

mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | latin1_swedish_ci  |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set

原理

通过抓包比较mysql配置character_set_server修改前后的tcp数据，发现每一个mysql的数据库连接建立成功后会JDBC会自动执行一次字符集设置语句SET NAMES xxx。当character_set_server为utf8时JDBC执行SET NAMES utf8，而当character_set_server为utf8mb4时JDBC执行SET NAMES utf8mb4。

image

image

在命令行中测试SET NAMES发现即使数据库表的字符集是utf8mb4时，若执行了SET NAMES utf8也会导致4字节字符写入mysql失败。成功复现了JDBC写入emoji写入异常的问题。

1
2
3
4
5
mysql> SET NAMES utf8;
Query OK, 0 rows affected

mysql> insert into user_info (name,age) values ('💋',18);
1366 - Incorrect string value: '\xF0\x9F\x92\x8B' for column 'name' at row 1
1
2
3
4
5
mysql> SET NAMES utf8mb4;
Query OK, 0 rows affected

mysql> insert into user_info (name,age) values ('💋',18);
Query OK, 1 row affected

在官方文档MySQL Connector/Java中，文档表明JDBC会通过character_set_server的值自动执行SET NAMES命令设置字符集编码。其目的是自动检测服务端字符集编码配置而减少JDBC客户端的字符集编码配置。

Functionality Added or Changed

  • Connector/J did not support utf8mb4 for servers 5.5.2 and newer.
  • Connector/J now auto-detects servers configured with character_set_server=utf8mb4 or treats the Java encoding utf-8 passed using characterEncoding=… as utf8mb4 in the SET NAMES= calls it makes when establishing the connection. (Bug #54175)

继续抓包以及查看JDBC源码发现，JDBC成功与mysql建立连接后，mysql会返回其版本号和一些附带信息例如字符集编码。如下图，0x00000167位表示当前mysql的默认字符集编码。图一的字符集编码值为0x21即十进制33，图二的字符集编码值为0x2D即十进制45。

通过翻阅JDBC源码发现，33表示utf8，45表示utf8mb4，即该值就是mysql的character_set_server值。

image

image

mysql-connector-java-5.1.46-sources.jar!/com/mysql/jdbc/CharsetMapping.java:258

1
2
collation[33] = new Collation(33, "utf8_general_ci", 1, MYSQL_CHARSET_NAME_utf8);
collation[45] = new Collation(45, "utf8mb4_general_ci", 1, MYSQL_CHARSET_NAME_utf8mb4);

在获取mysql的服务器参数后，解析字符集编码：

  • 当character_set_server为utf8时，执行SET NAMES utf8
  • 当character_set_server为utf8mb4时，执行SET NAMES utf8mb4

mysql-connector-java-5.1.46-sources.jar!/com/mysql/jdbc/ConnectionImpl.java:1697

1
2
3
4
5
6
7
8
9
10
11
12
13
boolean utf8mb4Supported = versionMeetsMinimum(5, 5, 2);
// 解析mysql返回的字符集编码,33表示 utf8;45表示 utf8mb4
boolean useutf8mb4 = utf8mb4Supported && (CharsetMapping.UTF8MB4_INDEXES.contains(this.io.serverCharsetIndex));

if (!getUseOldUTF8Behavior()) {
    if (dontCheckServerMatch || !characterSetNamesMatches("utf8") || (utf8mb4Supported && !characterSetNamesMatches("utf8mb4"))) {
        // 执行set names命令指定字符集编码从而自动配置mysql字符集
        execSQL(null, "SET NAMES " + (useutf8mb4 ? "utf8mb4" : "utf8"), -1, null, DEFAULT_RESULT_SET_TYPE,
                DEFAULT_RESULT_SET_CONCURRENCY, false, this.database, null, false);
        this.serverVariables.put("character_set_client", useutf8mb4 ? "utf8mb4" : "utf8");
        this.serverVariables.put("character_set_connection", useutf8mb4 ? "utf8mb4" : "utf8");
    }
}
>