jdbc&mysql中emoji和sql_mode问题详细分析

同事遇到的一个小问题，就是emoji保存出错，深究一下，发现还是有搞头的，记录一下。

问题描述

• 数据库版本：mysql5.6
• 数据库sql_mode设置为空
• 数据库表该字段字符集：utf8mb4
• jdbc连接参数：useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull
• jdbc connector版本：5.1.18

具体表现(测试过程)

• 代码中抛异常，Incorrect string value: '……' for column '……' at row X
• 使用客户端工具sqlyog/navicat执行，只有警告，没有error，存入表情为乱码，如果set names utf8mb4，就正常
• 使用客户端工具，首先执行SET sql_mode='strict_trans_tables'，然后存入表情，报错，不是警告

参考文档

mysql document

原因分析

• jdbc建立连接时，连接上默认的sql_mode是STRICT_TRANS_TABLES，在这种情况下，如果遇到无法正常解析的字符，报错而不是警告，而使用客户端连接时，sql_mode默认为空，这种情况下，不会报错，保存为乱码，但是会有警告
• For Connector/J 8.0.12 and earlier: In order to use the utf8mb4 character set for the connection, the server MUST be configured with character_set_server=utf8mb4; if that is not the case, when UTF-8 is used for characterEncoding in the connection string, it will map to the MySQL character set name utf8, which is an alias for utf8mb3. 也就是说，5.6版本mysql server，设置是characterEncoding=utf-8，其实这条连接上，字符集设置是utf8mb3，这种情形下，表情符被转为四个字节发送到mysql server，但是，mysql server尝试将这些字节流转为字符，很明显，失败！于是报错，抛异常，如果sql_mode不是STRICT_TRANS_TABLES，这时候不会报错，而是会存入乱码。

转载于:https://my.oschina.net/wuxiaofei/blog/3094635