【MySQL】java.sql.SQLException: Incorrect string value: '\xF0\x9F\x92\x9' for column 插入国外文字失败解决方案

分手后的思念是犯贱 2021-12-10 10:41 306阅读 0赞

**【引言】**

线上问题，Excel导入某字段是印度文，导入报错，插入Mysql失败，具体错误如下：

Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x92\x95 V...' for column '***' at row 1
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2484)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
    at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2079)
    at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1756)
    ... 60 more

**【问题缘由】**

由于mysql默认编码为utf-8，最大只占3个字节，一些非常见字符，比如该例子中“\\xF0\\x9F\\x92\\x95 V…”占4个字节，这样往数据表里插入4个字节的数据就会出错。

**【解决方案】**

找到问题原因，就想到之前解决过微信中存储emoji表情的问题，[微信小程序中使用emoji表情][emoji]，所以，很快找到了解决办法。

要么从代码中编码存入，解码取出；要么修改数据库字符集，且mysql版本必须5.5以上。

由于是旧系统，涉及到此字段的地方也很多，从代码中改，是行不通的，遗漏一个地方就会出问题，而目前使用的mysql版本是5.7，所以，决定采取第二种方案。

**【解决过程】**

*  **第一次尝试：修改数据库字段的字符集**

先尝试了一把仅修改那一个字段的字符集为utf8mb4，结果导入失败。

*  **第二次尝试：修改整张表的字符集**

尝试把整个表的字符集都改为utf8mb4，结果还是导入失败。

以为知道了解决办法，可以很快完成任务，结果并不是这样，两次失败，又回到起点。

查了些文章，了解到数据库的字符集分为**server、client、connection、database、system和result六个级别**。  
1、client是客户端使用的字符集，相当于网页中的字符集设置如下

2、connection是连接数据库的字符集设置类型，如果没有指明连接数据库使用的字符集类型就按照服务器端默认的字符集设置。

3、database是数据库服务器中某个库使用的字符集设定，如果建库时没有指明，将使用服务器安装时指定的字符集设置。

4、results是数据库给客户端返回时使用的字符集设定，如果没有指明，使用服务器默认的字符集。

5、server是服务器安装时指定的默认字符集设定。

6、system是数据库系统使用的字符集设定。

system一般默认是utf-8字符集，server是最高的字符集设定，database没有单独设定就按照server的字符集设定，其他都是按照server的设定设置字符集。还有，数据库内的每个表和字段也都有字符集的概念，一般都是根据上一级结构决定自身的字符集，比如表就根据database库的设定决定自己的字符集，字段根据表来决定自己的字符集。

所以，我查了下数据库目前的字符集，sql如下：

show global variables like '%character%'; 
    show global variables like '%collation%';

结果如下：

<table> 
 <thead> 
  <tr> 
   <th>Variable_name</th> 
   <th>Value</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>character_set_client</td> 
   <td>utf8mb4</td> 
  </tr> 
  <tr> 
   <td>character_set_connection</td> 
   <td>utf8mb4</td> 
  </tr> 
  <tr> 
   <td>character_set_database</td> 
   <td>utf8mb4</td> 
  </tr> 
  <tr> 
   <td>character_set_filesystem</td> 
   <td>utf8mb4</td> 
  </tr> 
  <tr> 
   <td>character_set_results</td> 
   <td>utf8mb4</td> 
  </tr> 
  <tr> 
   <td>character_set_server</td> 
   <td>utf8</td> 
  </tr> 
  <tr> 
   <td>character_set_system</td> 
   <td>utf8</td> 
  </tr> 
  <tr> 
   <td>collation_connection</td> 
   <td>utf8mb4_general_ci</td> 
  </tr> 
  <tr> 
   <td>collation_database</td> 
   <td>utf8mb4_general_ci</td> 
  </tr> 
  <tr> 
   <td>collation_server</td> 
   <td>utf8_general_ci</td> 
  </tr> 
 </tbody> 
</table>

除了server和system级的编码是utf8，其他编码已经是utf8mb4.因为知道server级是最高级别，就猜想是不是需要把server设置成utf8mb4的字符集格式，开始第三次尝试。

*  **第三次尝试：修改server的字符集**

尝试把mysql server的字符集改为utf8mb4，结果还是导入失败。

回到起点，又继续查，查到有篇文章写到与数据库连接驱动也有关系，换到这个思路，又开始了新的尝试，所以开始去官网相关文档上查。

在mysql官方文档关于连接驱动Using Character Sets and Unicode章节内容中写道：

All strings sent from the JDBC driver to the server are converted automatically from native Java Unicode form to the client character encoding, including all queries sent using Statement.execute(), Statement.executeUpdate(), Statement.executeQuery() as well as all PreparedStatement and CallableStatement parameters with the exclusion of parameters set using setBytes(), setBinaryStream(), setAsciiStream(), setUnicodeStream(), and setBlob().

大致含义是指所有从JDBC发起的字符串链接驱动发送至server端后，会自动地转化为java客户端编码字符集格式，包括所有的查询，执行execute(),executeUpdate()及executeQuery()等方法。

另外，在Setting the Character Encoding相关内容中写道：  
The character encoding between client and server is automatically detected upon connection (provided that the Connector/J connection properties characterEncoding and connectionCollation are not set). You specify the encoding on the server using the system variable character\_set\_server (for more information, see Server Character Set and Collation). The driver automatically uses the encoding specified by the server.

大致含义是指对于数据库连接驱动来说，client和server端的字符集是可以自动转化的（假如连接时没有设置字符集格式）。我们可以通过设置character\_set\_server这一变量来设置server的编码格式，从而就会使用server级的编码格式。

最后，在备注中写道：  
For Connector/J 5.1.46 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.

For Connector/J 5.1.47 and later:  
When UTF-8 is used for characterEncoding in the connection string, it maps to the MySQL character set name utf8mb4.

也就是说在连接驱动5.1.46版本以前，想要使用utf8mb4的字符集格式连接，则server端必须配置为utf8mb4；如果没有设置，当使用utf8设置连接时，则连接的字符集格式还是utf8格式，也就是只能存放3个字节的字符集格式。

在5.1.47版本以后，当使用utf8连接时，则会自动匹配到utf8mb4字符集格式。

看了下项目中的数据库连接驱动jar包，版本是5.1.45-bin。所以，the server MUST be configured with character\_set\_server=utf8mb4，server必须设置utf8mb4，否则还是会匹配到utf8字符集类型。  
但其实在第三次尝试办法中，已经设置了server级别的字符集为utf8mb4，但是还是失败。

*  **最后的尝试：升级mysql-connector-java jar版本**

将原来的5.1.45-bin版本，替换为5.1.47版本，并且将该字段的编码格式改为utf8mb4。最终结果，导入成功。

**【总结】**

遇到问题，有了解决办法，有时候能顺利解决问题，有时候又并不能解决问题，可能是具体环境不一样，还需要具体分析。

刚开始可能并不知道问题缘由，一顿瞎试，这过程中可能又会有了其他思路，最终才得以解决问题。

[emoji]: https://blog.csdn.net/hu_zhiting/article/details/78339259