JDBC字符集乱码问题

Posted by RussXia on September 28, 2019

JDBC字符集乱码问题

问题背景

数据库:mysql 5.7

datasource数据源:druid

Connector/J: 5.1.38

公司线上报警,发现抛出 UncategorizedSQLException 异常,看到异常栈的信息,基本可以断定是字符集设置的问题。

Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x87\xA8\xF0\x9F...' for column 'nick_name' at row 1; uncategorized SQLException for SQL []; SQL state [HY000]; error code [1366]; Incorrect string value: '\xF0\x9F\x87\xA8\xF0\x9F...' for column 'nick_name' at row 1; nested exception is java.sql.SQLException: Incorrect string value: '\xF0\x9F\x87\xA8\xF0\x9F...' for column 'nick_name' at row 1
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)

但是在查看了线上数据库对应表的字符编码格式后,发现设置的是utfmb4。而且测试环境设置的也是utfmb4,并没有出现 UncategorizedSQLException 异常,正常插入了emoji表情符号。

问题排查

怀疑线上库和测试库可能字符集设置上存在差异,SHOW VARIABLES LIKE "character_set%"; 查看字符集设置。

测试环境字符集设置:

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_set_client	utf8
character_set_connection	utf8
character_set_database	utf8mb4
character_set_filesystem	binary
character_set_results	utf8
character_set_server	utf8
character_set_system	utf8

果然二者的字符配置不一样。此时我想当然的认为是线上 character_set_connection 编码格式是 utf8 导致的。但是在本地测试了下,将线上的字符集配置设置到本地,然而将 character_set_connection 设置为 utf8mb4,依然报错。

最后排查到, 原来Connector/J使用编码格式是server配置的 character_set_server 指定的字符集。

为什么要这样设计呢?有什么历史遗留问题?这块不是太了解,也不是很能理解。

问题解决

其实只要在java程序中,指定连接使用的字符集编码为utfmb4即可,即

String query = "set names utf8mb4";
stat.execute(query);

因为使用的是druid数据源来管理连接池,而druid提供connection-init-sqls参数来制定connection在init时需要执行的sql,所以增加配置

-- Spring Boot增加配置
spring.datasource.druid.connection-init-sqls=set names utf8mb4;

-- SpringMVC
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
    <!-- 前面的url,idle,active等等配置省略 -->
    <property name="connectionInitSqls" value="SET NAMES utf8mb4;"/>
</bean>

相关知识点

字符集和字符序

字符集定义了字符以及字符的编码,而字符序列则定义了字符的比较规则。所以一个字符集可以对应多种字符序。

通过 SHOW CHARACTER SET 命令可以查看MySQL支持的字符集,以及对该字符集默认的字符序,该字符所占长度等信息。下面是MySQL5.7.24所支持的字符集的部分内容。

Charset     Description                     Default_Collation   Maxlen
big5        Big5 Traditional Chinese        big5_chinese_ci     2
utf8        UTF-8 Unicode                   utf8_general_ci     3
utf8mb4     UTF-8 Unicode                   utf8mb4_general_ci  4
utf32       UTF-32 Unicode                  utf32_general_ci    4
binary      Binary  pseudo charset          binary              1
geostd8     GEOSTD8 Georgian                geostd8_general_ci  1
gb18030     China National Standard GB18030 gb18030_chinese_ci  4

我们可以看到,其中utf8占3个字节,utf8mb4占4个字节。utf8默认的字符序是utf8_general_ci,utf8mb4默认的字符序是 utf8mb4_general_ci

utf8和utf8mb4

MySQL在5.5.3之后增加了utfmb4字符集,mb4的意思是most bytes 4,原先的utf8只有三个字节,不能存储一些生僻汉字和emoji符号,例如:🚗 😊 🐟 等等。utf8mb4就是用来兼容的4个字节的unicode,utf8mb4是utf8的超集。所以可以用类似 alter table table_name convert to character set utf8mb4 这样的语句,将table的字符集转为utf8mb4。