起因

公司的MySQL服务器编译的时候使用的是 UTF-8(即 utf8mb3), 在需要使用 utf8mb4 的字段上, 才显式设置为 utf8mb4.

环境

MySQL 5.6.21, 端口 3308
Ubuntu 14.04 64位

JDBC使用的连接字符串3
jdbc:mysql://10.0.0.40:3308/test?useUnicode=true&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useServerPrepStmts=false&rewriteBatchedStatements=true&useCompression=true

MySQL JDBC 驱动版本的不同处理

以前 MySQL JDBC 版本为 5.1.18, 新的项目使用 5.1.46. 旧版本(5.1.18以正常插入 emoji 字符, 但新的 5.1.46 却会报如下异常)

Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x92\x92' for column 'name' at row 1
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965) ~[mysql-connector-java-5.1.46.jar:5.1.46]
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3976) ~[mysql-connector-java-5.1.46.jar:5.1.46]
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3912) ~[mysql-connector-java-5.1.46.jar:5.1.46]
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530) ~[mysql-connector-java-5.1.46.jar:5.1.46]
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683) ~[mysql-connector-java-5.1.46.jar:5.1.46]
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2482) ~[mysql-connector-java-5.1.46.jar:5.1.46]
	at com.mysql.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1552) ~[mysql-connector-java-5.1.46.jar:5.1.46]
	at com.mysql.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2607) ~[mysql-connector-java-5.1.46.jar:5.1.46]
	at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1480) ~[mysql-connector-java-5.1.46.jar:5.1.46]
	at com.example.mysqldemo2.MysqlDemo2Application.insertTestData(MysqlDemo2Application.java:70) [classes/:na]
	at com.example.mysqldemo2.MysqlDemo2Application.run(MysqlDemo2Application.java:23) [classes/:na]
	at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:813) [spring-boot-2.1.1.RELEASE.jar:2.1.1.RELEASE]

分析

5.1.18 VS 5.1.46

通过 TCPDUMP 抓包, 发现它在创建连接的时候, 发送了如下一些参数设置

抓包命令

sudo tcpdump -i eth0 dst port 3308 -vvv -X

5.1.18 发送的设置如下

SET.NAMES.utf8mb4
SET.character_set_results=NULL
SET.autocommit=1
SET.sql_mode='STRICT_TRANS_TABLES'

5.1.46 发送的设置如下

SET.character_set_results=NULL
SET.autocommit=1
SET.sql_mode='STRICT_TRANS_TABLES'

那最大的区别就是有无发送 set NAMES utf8mb4 了.

源码分析 5.1.18

在类 com.mysql.jdbc.CharsetMapping.java 文件中的 VersionedStringProperty 里有

		if (property.startsWith("*")) {
			property = property.substring(1);
			preferredValue = true;
		}

即如果字符串是以 * 开头的表示就优先使用它. 而字符集的设置里的字符串是 CHARSET_CONFIG.setProperty("javaToMysqlMappings" 的 value. 其中 UTF-8 为

			+ "UTF-8 = 		utf8,"
			+ "UTF-8 =				*> 5.5.2 utf8mb4,"

可以看到 *> 5.5.2 utf8mb4 这个是以 * 开头的, 即如果使用的是 useUnicode=true(上面JDBC URL 中设置的), 则优先使用 utf8mb4. (判断方法为 getMysqlEncodingForJavaEncoding ). 如果 getMysqlEncodingForJavaEncoding 方法返回的字符集名字, 跟服务器的变量 character_set_client 和 character_set_connection 同时匹配的话, 则不发送 set NAMES 命令, 否则发送 set NAMES xxx, xxx 为 getMysqlEncodingForJavaEncoding 方法返回的字符串的名字.

在这里, 是要设置的. 因为 getMysqlEncodingForJavaEncoding 方法返回的是 utf8mb4, 而 character_set_client 为 utf8, character_set_connection 为 utf8.

源码分析 5.1.46

同样在 com.mysql.jdbc.CharsetMapping.java 类中的 getMysqlCharsetForJavaEncoding 方法. 由于 JAVA_ENCODING_UC_TO_MYSQL_CHARSET 中的 UTF8 的配置分别为

                new MysqlCharset(MYSQL_CHARSET_NAME_utf8, 3, 1, new String[] { "UTF-8" }),
                new MysqlCharset(MYSQL_CHARSET_NAME_utf8mb4, 4, 0, new String[] { "UTF-8" }),

上面的 1 和 0 是表示优先级. 从这里可以看到, 5.1.46 版本中, 默认的优先使用为 utf8 而不是 utf8mb4. 所以 getMysqlCharsetForJavaEncoding 方法返回 utf8.

这时, utf8 跟服务器的字符集是一致的, 所以就不发送 set NAMES 来修改了. 所以在抓包时看到, 相比 5.1.18 版本少了一条 set NAMES 命令.

服务器返回的变量示例

serverVariables = {HashMap@3629}  size = 20
 0 = {HashMap$Node@3929} "net_buffer_length" -> "8192"
 1 = {HashMap$Node@3930} "interactive_timeout" -> "120"
 2 = {HashMap$Node@3931} "query_cache_size" -> "0"
 3 = {HashMap$Node@3932} "character_set_connection" -> "utf8"
 4 = {HashMap$Node@3933} "max_allowed_packet" -> "1073741824"
 5 = {HashMap$Node@3934} "net_write_timeout" -> "10"
 6 = {HashMap$Node@3935} "lower_case_table_names" -> "1"
 7 = {HashMap$Node@3936} "collation_server" -> "utf8_general_ci"
 8 = {HashMap$Node@3937} "system_time_zone" -> "HKT"
 9 = {HashMap$Node@3938} "wait_timeout" -> "120"
 10 = {HashMap$Node@3939} "time_zone" -> "SYSTEM"
 11 = {HashMap$Node@3940} "character_set_server" -> "utf8"
 12 = {HashMap$Node@3941} "auto_increment_increment" -> "1"
 13 = {HashMap$Node@3942} "license" -> "GPL"
 14 = {HashMap$Node@3943} "character_set_client" -> "utf8"
 15 = {HashMap$Node@3944} "sql_mode" -> 
 16 = {HashMap$Node@3945} "character_set_results" -> "utf8"
 17 = {HashMap$Node@3946} "transaction_isolation" -> "REPEATABLE-READ"
 18 = {HashMap$Node@3947} "query_cache_type" -> "OFF"
 19 = {HashMap$Node@3948} "init_connect" ->

解决办法

  • 如果服务允许重启, 则建议可以在 mysql 里设置, 将所有的数据, 全修改为 utf8mb4, 以及 mysql 服务器中 charset 相关的变量设置为 utf8mb4
  • 如果服务不允许重启, 降 jdbc 驱动为 5.1.18 (其他的版本不清楚, 可以按上面的思路来看下是否优先支持 utf8mb4 )
  • 也可以在执行相应的 SQL 之前, 执行一次 set names utf8mb4

参考资料

client 与 server 默认是自动进行检测的. 如果服务器端指定了 character_set_server 变量, 则 JDBC 驱动会自动使用该字符集(在不指定 JDBC URL 参数 characterEncoding 和 connectionCollation 的情况下). 可以通过 characterEncoding (该参数值是使用 Java 风格的形式指定. 例如 UTF-8 )来进行手工指定, 而不是自动检测. 使用 UTF-8 (5.1.46 及更早版本则表示 mysql 的 utf8, 5.1.47 及之后的版本则表示 mysql 的 utf8mb4) 为了在 MySQL JDBC 驱动版本 5.1.46 及之前的版本中使用 utf8mb4, 则服务器端必须配置 character_set_server=utf8mb4, 否则JDBC URL参数 characterEncoding=UTF-8 表示的是 MySQL 的 utf8, 而不是 utf8mb4.

注意: 我在 MySQL 版本为 5.5.40 (character_set_server=utf8) + mysql driver 5.1.18 中可以正常使用 utf8mb4(JDBC URL参数 useUnicode=true). 而不必像上面文档中说的, 必须指定服务器的变量 character_set_server=utf8mb4 .

从上面的 changes 文件中可以了解到.

utf8mb4 是从 5.1.13 版本开始支持的

这个版本是根据服务器配置的 character_set_server=utf8mb4 自动检测的. 也可以通过JDBC URL 参数 characterEncoding=UTF-8 来设置, 它会自动在建立连接时调用 set names utf8mb4 来实现.