当前位置:   article > 正文

mysql-connector-java 插入 utf8mb4 字符失败问题处理分析_mysql jdbc utf8 utf8mb4

mysql jdbc utf8 utf8mb4

更改MySQL数据库的编码为utf8mb4

原文:http://blog.csdn.net/woslx/article/details/49685111

utf-8编码可能2个字节、3个字节、4个字节的字符,但是MySQL的utf8编码只支持3字节的数据,而移动端的表情数据是4个字节的字符。如果直接往采用utf-8编码的数据库中插入表情数据,Java程序中将报SQL异常:

  1. java.sql.SQLException: Incorrect string value: ‘\xF0\x9F\x92\x94’ for column ‘name’ at row 1
  2. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
  3. at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3593)
  4. at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3525)
  5. at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1986)
  6. at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2140)
  7. at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2620)
  8. at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1662)
  9. at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1581)

可以对4字节的字符进行编码存储,然后取出来的时候,再进行解码。但是这样做会使得任何使用该字符的地方都要进行编码与解码。

utf8mb4编码是utf8编码的超集,兼容utf8,并且能存储4字节的表情字符。 
采用utf8mb4编码的好处是:存储与获取数据的时候,不用再考虑表情字符的编码与解码问题。

更改数据库的编码为utf8mb4:

1. MySQL的版本

utf8mb4的最低mysql版本支持版本为5.5.3+,若不是,请升级到较新版本。

2. MySQL驱动

5.1.34可用,最低不能低于5.1.13

3.修改MySQL配置文件(非必须)

修改mysql配置文件my.cnf(windows为my.ini) 
my.cnf一般在etc/mysql/my.cnf位置。找到后请在以下三部分里添加如下内容: 
[client] 
default-character-set = utf8mb4 
[mysql] 
default-character-set = utf8mb4 
[mysqld] 
character-set-client-handshake = FALSE 
character-set-server = utf8mb4 
collation-server = utf8mb4_unicode_ci 
init_connect='SET NAMES utf8mb4'

4. 重启数据库,检查变量

SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';

Variable_nameValue
character_set_clientutf8mb4
character_set_connectionutf8mb4
character_set_databaseutf8mb4
character_set_filesystembinary
character_set_resultsutf8mb4
character_set_serverutf8mb4
character_set_systemutf8
collation_connectionutf8mb4_unicode_ci
collation_databaseutf8mb4_unicode_ci
collation_serverutf8mb4_unicode_ci

collation_connection 、collation_database 、collation_server是什么没关系。

但必须保证

系统变量描述
character_set_client(客户端来源数据使用的字符集)
character_set_connection(连接层字符集)
character_set_database(当前选中数据库的默认字符集)
character_set_results(查询结果字符集)
character_set_server(默认的内部操作字符集)

这几个变量必须是utf8mb4。

5. 数据库连接的配置

数据库连接参数中: 
characterEncoding=utf8会被自动识别为utf8mb4,也可以不加这个参数,会自动检测。 
而autoReconnect=true是必须加上的。

6. 将数据库和已经建好的表也转换成utf8mb4

更改数据库编码:ALTER DATABASE caitu99 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

或 set names utf8mb4

更改表编码:ALTER TABLE TABLE_NAME CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
如有必要,还可以更改列的编码

查看表的编码: show full columns from TABLE_NAME

 

mysql-connector-java 插入 utf8mb4 字符失败问题处理分析

原文:https://blog.csdn.net/FHGFHFYUUGY/article/details/90292139

问题说明

业务数据库实例的编码由 utf8 修改为 utf8mb4 后, java 业务插入表情符等宽字符(4 字节)的时候一直报错以下相关的错误:

  1. ### Cause:java.sql.SQLException:Incorrect string value:\xF0\x9F\x98\x8E for column nick_name at row 1
  2. ;uncategorized SQLException for SQL[]; SQL state [HY000]; error code[1366];Incorrect string value: \xF0\x9F\x98\x8E for column nick_name at row 1

程序及数据库运行的版本及环境如下所示:

Centos 7.6
kernel-3.10.0-957.1.3.el7.x86_64
mysql-connector-java-5.1.46
Percona-Server-5.6.38-rel83.0-Linux
测试环境中使用同样的 mysql-connector-java 版本, 程序可以正常插入. 所不同的是测试环境修改完编码后重启了 MySQL 服务, 线上环境仅做以下修改, 重启程序而不重启 MySQL 服务:

set global character_set_client = utf8mb4;
set global character_set_connection = utf8mb4;
set global character_set_database = utf8mb4;
set global character_set_results = utf8mb4;
set global character_set_server = utf8mb4;
set global collation_server = utf8mb4_general_ci;
set global collation_database = utf8mb4_general_ci;
set global collation_connection = utf8mb4_general_ci;

jdbc 配置说明

参考 connector-j-reference-charset 可以看到如果程序要插入 utf8mb4 字符, 需要满足以下条件:

Connector/J 5.1.47 及以上版本:
  1. 指定 characterEncoding 参数为 UTF8/UTF-8 即可, 新版本直接映射到 utf8mb4 编码;
  2. 如果 connectionCollation 指定的排序规则不是 utf8mb4 相关的, 则 characterEncoding 参数会重写为排序规则对应的编码;

Connector/J 5.1.47 以下版本:
  1. 设置 MySQL 参数变量 character_set_server=utf8mb4;
  2. 指定 characterEncoding 参数为 UTF8/UTF-8, jdbc 程序会进行探测是否使用 utf8mb4;
所以对于 mysql-connector-java 版本来讲, 我们的条件已经满足, 不过还是插入失败. 另外 characterEncoding 参数的值只可以指定 connector-j-reference-charset 链接中 Table 5.3提到的编码名, 指定其余的编码名, jdbc 在建立连接的时候就是失败报错.

问题分析说明

mysql-connect-java 如何处理编码

满足了官方文档中的条件还是插入失败, 而使用 python, perl 等脚本程序却可以正常插入 utf8mb4 字符, 这点很让人迷惑. 我们参考 mysql-connector-java-5.1.46 的源程序可以看到以下代码:

  1. //src/com/mysql/jdbc/ConnectionImpl.java
  2. 1616    private boolean configureClientCharacterSet(boolean dontCheckServerMatch) throws SQLException {
  3. 1617        String realJavaEncoding = getEncoding();
  4. ......
  5. 1689                     if (realJavaEncoding != null) {
  6. 1690 
  7. 1691                         //
  8. 1692                         // Now, inform the server what character set we will be using from now-on...
  9. 1693                         //
  10. 1694                         if (realJavaEncoding.equalsIgnoreCase("UTF-8") || realJavaEncoding.equalsIgnoreCase("UTF8")) {
  11. 1695                             // charset names are case-sensitive
  12. 1696 
  13. 1697                             boolean utf8mb4Supported = versionMeetsMinimum(5, 5, 2);
  14. 1698                             boolean useutf8mb4 = utf8mb4Supported && (CharsetMapping.UTF8MB4_INDEXES.contains(this.io.serverCharsetIndex));
  15. 1699 
  16. 1700                             if (!getUseOldUTF8Behavior()) {
  17. 1701                                 if (dontCheckServerMatch || !characterSetNamesMatches("utf8") || (utf8mb4Supported && !characterSetNamesMatches("utf8mb4"))) {
  18. 1702                                     execSQL(null, "SET NAMES " + (useutf8mb4 ? "utf8mb4" : "utf8"), -1, null, DEFAULT_RESULT_SET_TYPE,
  19. 1703                                             DEFAULT_RESULT_SET_CONCURRENCY, false, this.database, null, false);
  20. 1704                                     this.serverVariables.put("character_set_client", useutf8mb4 ? "utf8mb4" : "utf8");
  21. 1705                                     this.serverVariables.put("character_set_connection", useutf8mb4 ? "utf8mb4" : "utf8");
  22. 1706                                 }
  23. 1707                             } else {
  24. 1708                                 execSQL(null, "SET NAMES latin1", -1, null, DEFAULT_RESULT_SET_TYPE, DEFAULT_RESULT_SET_CONCURRENCY, false, this.database, null,
  25. 1709                                         false);
  26. 1710                                 this.serverVariables.put("character_set_client", "latin1");
  27. 1711                                 this.serverVariables.put("character_set_connection", "latin1");
  28. 1712                             }
  29. 1713 
  30. 1714                             setEncoding(realJavaEncoding);


可以看到 1694 行代码即我们制定的 characterEncoding 参数, 后续的代码则为编码的自动探测. 1697 行代码为判断当前 MySQL 版本是否支持 utf8mb4 编码(mysql-5.5.2版本开始支持 utf8mb4 编码), 1698 行中 useutf8mb4 由两个条件来决定:

utf8mb4Supported
CharsetMapping.UTF8MB4_INDEXES.contains(this.io.serverCharsetIndex)
我们的数据库版本是 5.6.38 , 所以第一个条件是满足的, 第二个条件中的 this.io.serverCharsetIndex 来源于以下代码, 可以看到这段代码是程序与数据库连接的时候所做的握手协议处理, serverCharsetIndex 为 MySQL Server 返回给当前会话的编码号(对应 information_schema.COLLATIONS 表的 ID 字段), 所以第二个条件即为判断当前会话接收到的编码号是否存在于 CharsetMapping.UTF8MB4_INDEXES 的集合中.

  1. //src/com/mysql/jdbc/MysqlIO.java
  2.  998     /**
  3.  999      * Initialize communications with the MySQL server. Handles logging on, and
  4. 1000      * handling initial connection errors.
  5. 1001      * 
  6. 1002      * @param user
  7. 1003      * @param password
  8. 1004      * @param database
  9. 1005      * 
  10. 1006      * @throws SQLException
  11. 1007      * @throws CommunicationsException
  12. 1008      */
  13. 1009     void doHandshake(String user, String password, String database) throws SQLException {
  14. 1010         // Read the first packet
  15. ......
  16. 1118         if ((versionMeetsMinimum(4, 1, 1) || ((this.protocolVersion > 9) && (this.serverCapabilities & CLIENT_PROTOCOL_41) != 0))) {
  17. 1119 
  18. 1120             /* New protocol with 16 bytes to describe server characteristics */
  19. 1121             // read character set (1 byte)
  20. 1122             this.serverCharsetIndex = buf.readByte() & 0xff;
  21. 1123             // read status flags (2 bytes)
  22. 1124             this.serverStatus = buf.readInt();

参考 mysql-connector-java-4.1.47 版本的 changelog:

See Using Character Sets and Unicode for details, including how to use the utf8mb3 character set now for connection. (Bug #23227334, Bug #81196)
bug #81196 与我们碰到的问题相同. 如果 serverCharsetIndex 的值不是上述的集合中, jdbc 就会在会话建立后一直执行 SET NAMES utf8 操作.

协议分析

我们通过 tcpdump 来查看握手协议的报文信息:

  1. 0000   fe ee 16 93 fe 2d 52 54 00 48 bd 50 08 00 45 08  .....-RT.H.P..E.
  2. 0010   00 8b 4a b8 40 00 40 06 cc 78 0a 94 07 09 0a 94  ..J.@.@..x......
  3. 0020   07 04 0c e7 c5 0c b7 f4 a5 5a 5a 53 2f f9 80 18  .........ZZS/...
  4. 0030   00 e3 23 b2 00 00 01 01 08 0a a9 c6 ef e2 a9 b5  ..#.............
  5. 0040   7a 4b 53 00 00 00 0a 35 2e 36 2e 33 38 2d 38 33  zKS....5.6.38-83
  6. 0050   2e 30 2d 6c 6f 67 00 78 15 10 01 74 2d 7d 51 5e  .0-log.x...t-}Q^
  7. 0060   64 5b 79 00 ff f7 21 02 00 7f 80 15 00 00 00 00  d[y...!.........
  8. 0070   00 00 00 00 00 00 70 48 56 56 30 29 7c 58 24 48  ......pHVV0)|X$H
  9. 0080   7e 64 00 6d 79 73 71 6c 5f 6e 61 74 69 76 65 5f  ~d.mysql_native_
  10. 0090   70 61 73 73 77 6f 72 64 00                       password.


参考 MySQL 的 通信协议格式 :

  1. 1              [0a] protocol version
  2. string[NUL]    server version
  3. 4              connection id
  4. string[8]      auth-plugin-data-part-1
  5. 1              [00] filler
  6. 2              capability flags (lower 2 bytes)
  7.   if more data in the packet:
  8. 1              character set
  9. 2              status flags
  10. 2              capability flags (upper 2 bytes)
  11.   if capabilities & CLIENT_PLUGIN_AUTH {
  12. 1              length of auth-plugin-data
  13.   } else {
  14. 1              [00]
  15.   }
  16. string[10]     reserved (all [00])
  17.   if capabilities & CLIENT_SECURE_CONNECTION {
  18. string[$len]   auth-plugin-data-part-2 ($len=MAX(13, length of auth-plugin-data - 8))
  19.   if capabilities & CLIENT_PLUGIN_AUTH {
  20. string[NUL]    auth-plugin name
  21.   }


从上述的协议格式来查找 tcpdump 报文中的各字段信息如下:

  1. protocol version: 0a
  2. server version:   35 2e 36 2e 33 38 2d 38 33 2e 30 2d 6c 6f 67 00
  3. connection id:    78 15 10 01
  4. auth-plugin-date: 74 2d 7d 51 5e 64 5b 79
  5. [00] filler:      00
  6. capability flags: ff f7
  7. character set:    21
  8. status:           02 00

可以看到 MySQL Server 返回的 character set 为 0x21(十进制 33), 33 对应 information_schema.COLLATIONS 表中的 utf8 编码, 这意味着我们改了 MySQL Server 编码相关的参数后并没有将新的 utf8mb4 编码返回给客户端, 而是返回以前的编码.

MySQL 如何返回编码给客户端

我们以同样 MySQL 版本的 debug 版本进行测试, 如下所示为 debug 版本的 trace 信息:

  1. ......
  2. T@29   : | | | | | | <net_flush 224
  3. T@29   : | | | | | <send_server_handshake_packet 10513
  4. T@29   : | | | | <server_mpvio_write_packet 11619
  5. T@29   : | | | | >server_mpvio_read_packet
  6. T@29   : | | | | | >vio_read

这里的函数 send_server_handshake_packet 即实现了返回给客户端的握手协议, 10496 行即为 MySQL Server 返回的编码信息:

  1. //src/sql/sql_acl.cc
  2. 10419 static bool send_server_handshake_packet(MPVIO_EXT *mpvio,
  3. 10420                                          const char *data, uint data_len)
  4. 10421 {
  5. ......
  6. 10494   int2store(end, mpvio->client_capabilities);
  7. 10495   /* write server characteristics: up to 16 bytes allowed */
  8. 10496   end[2]= (char) default_charset_info->number;
  9. 10497   int2store(end + 3, mpvio->server_status[0]);

对于代码 default_charset_info->number , 其为 CHARSET_INFO 结构体的类型, 如下:

  1. typedef struct charset_info_st
  2. {
  3.   uint      number;
  4.   uint      primary_number;
  5.   uint      binary_number;
  6. ....
  7.   MY_CHARSET_HANDLER *cset;
  8.   MY_COLLATION_HANDLER *coll;
  9. } CHARSET_INFO;

从 sql/mysqld.cc 中的代码来看, default_charset_info 仅在 MySQL Server 启动的时候进行初始化使用, 可以看到其值为 character-set-server 的参数值:

  1. 4020 int init_common_variables()
  2.  4021 {
  3.  4022   umask(((~my_umask) & 0666));
  4.  4023   connection_errors_select= 0;
  5. ......
  6.  4302   if (item_create_init())
  7.  4303     return 1;
  8.  4304   item_init();
  9. ......
  10.  4322     if (!(default_charset_info=
  11.  4323           get_charset_by_csname(default_character_set_name,
  12.  4324                                 MY_CS_PRIMARY, MYF(MY_WME))))
  13.  4325     {
  14.  4326       if (next_character_set_name)
  15.  4327       {
  16.  4328         default_character_set_name= next_character_set_name;
  17.  4329         default_collation_name= 0;          // Ignore collation
  18.  4330       }
  19.  4331       else
  20.  4332         return 1;                           // Eof of the list
  21.  4333     }
  22.  4334     else
  23.  4335       break;
  24.  4336   }
  25.  7537   {"character-set-server", 'C', "Set the default character set.",
  26.  7538    &default_character_set_name, &default_character_set_name,
  27.  7539    0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0 },

而启动后更改编码相关的参数并不会触发 default_charset_info 的更新, 从 debug 版本的 trace 日志中即可看到, 上述相关的操作仅在连接建立的时候初始化:

  1. .......
  2. T@1    : <item_create_init 5792
  3. T@1    : >get_charset_by_csname
  4. T@1    : | enter: name: 'utf8'

从这方面来看, 修改正在运行的数据库的编码并不会触发 default_charset_info 的更新, 返回给客户端协议包中的编码就还是以前的编码.

解决方式

从上述的分析来看, mysql-connect-java-5.1.46 依赖数据库返回的编码, 不过由于数据库返回给客户端的编码还是以前的编码(同参数 character-set-server 的值一致), 所以要解决程序插入表情符的方式可以使用下面的方式:

1. 重启 MySQL Server

修改数据库的配置文件, 将原先 utf8 相关的编码都修改为 utf8mb4, 重启 MySQL Server , 新的default_charset_info 继承 character-set-server 参数的值, 返回给客户端的编码即为 utf8mb4 编码. 这种方式适合新创建的或者测试环境的数据库, 线上的已运行数据库一般不做重启操作.

2. 打补丁

参考 bugs 81196 提供的方式, 这种方式适用于 5.1.38 ~ 5.1.46 版本, 其额外获取当前会话的 collation 参数是否包含 utf8mb4 来决定 useutf8mb4 是否为真, 如下所示:

  1. diff --git a/src/com/mysql/jdbc/ConnectionImpl.java b/src/com/mysql/jdbc/ConnectionImpl.java
  2. index 9da30ea..854ae59 100644
  3. --- a/src/com/mysql/jdbc/ConnectionImpl.java
  4. +++ b/src/com/mysql/jdbc/ConnectionImpl.java
  5. @@ -1762,7 +1762,8 @@
  6.                              // charset names are case-sensitive
  7.                              boolean utf8mb4Supported = versionMeetsMinimum(5, 5, 2);
  8. -                            boolean useutf8mb4 = utf8mb4Supported && (CharsetMapping.UTF8MB4_INDEXES.contains(this.io.serverCharsetIndex));
  9. +                            boolean useutf8mb4 = utf8mb4Supported && (CharsetMapping.UTF8MB4_INDEXES.contains(this.io.serverCharsetIndex)
  10. +                                    || (getConnectionCollation() != null && StringUtils.startsWithIgnoreCase(getConnectionCollation(), "utf8mb4")));
  11.                              if (!getUseOldUTF8Behavior()) {
  12.                                  if (dontCheckServerMatch || !characterSetNamesMatches("utf8") || (utf8mb4Supported && !characterSetNamesMatches("utf8mb4"))) {

从 tcpdump -A -r .... 的报文来看:

  1. 12:08:22.994813 IP 10.0.21.17.50444 > 10.0.21.5.3303: Flags [P.], seq 261:1189, ack 110, win 115, options [nop,nop,TS val 2847242832 ecr 2848387046], length 928
  2. ..zP........./* mysql-connector-java-5.1.46 ( Revision: 9cc87a48e75c2d2e87c1a293b2862ce651cb256e ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server,......
  3. 12:08:22.994939 IP 10.0.21.5.3303 > 10.0.21.17.50444: Flags [P.], seq 110:1137, ack 1189, win 250, options [nop,nop,TS val 2848387046 ecr 2847242832], length 1027
  4. ......zP..........def....auto_increment_increment..?...........*....def....character_set_client..!................def....character_set_connection..!...........+....def....character_set_results..!...........*....def....character_set_server..!...........&....def....collation_server..!.6........."
  5. .........................2.utf8.utf8.utf8.utf8mb4.utf8mb4_general_ci..28800.GPL.1.....

jdbc 初始化的时候会获取一些变量参数的信息, 如上所示, collation 相关的参数均为 utf8mb4 相关的信息, 所以这种补丁的方式也可以解决碰到的问题, 这种方式需要开发者修改并编译对应的 mysql-connector-java 版本.

3. 升级 Connector/J 版本

上述有提到 5.1.47 版本的 characterEncoding 参数设置为 UTF8/UTF-8 的时候, 会直接映射到 utf8mb4, 不像低版本那样还需要依赖数据库返回的编码, 也不用重启数据库即可生效, 详见5.1.47-changelog . 从 changelog 可以看到比起 5.1.46 版本, 变更的并不多, 没有做大的更新, 升级的话不会对已有的功能产生影响. 不过线上升级建议分批操作, 以免存在问题影响所有的业务.

jdbc下载地址:https://repo1.maven.org/maven2/mysql/mysql-connector-java/

 

注:采用第三种方案解决了问题。

 

本文内容由网友自发贡献,转载请注明出处:【wpsshop博客】
推荐阅读
相关标签
  

闽ICP备14008679号