The treatment of Mysql utf8mb4 character set by JDBC

When developing the Wechat open platform and accessing the data of Wechat official account, the nickname and text messages about users were that a large number of emoji text messages appeared, which timed out the storage range of the utf-8 character set.

When storing data, some special characters failed to store strings.

Server-side solution

When consulting the official documents of MySQL, the following official ways to support the conversion of utf8 to utf8mb4 are given

Https://dev.mysql.com/doc/refman/5.7/en/charset-unicode-conversion.html

The above steps are only for server-side configuration.

Additional special processing is required for the client

Because my current project is written in JAVA and uses JDBC connections to operate the database, here is the solution for JDBC operations

The previous configuration of JDBC's URL

String url = "jdbc:mysql://HOST:PORT/DATABASE?useUnicode=true&characterEncoding=UTF-8"

In connection configuration, declare the character encoding of UTF-8. In terms of current storage requirements, an error will be reported when storing emoji strings. Even if the database on the server side sets the utf8mb4 character set type

The client is modified to the following configuration

String url = "jdbc:mysql://HOST:PORT/DATABASE?useUnicode=true&characterEncoding=utf8mb4"

There is an error in the client connection, the character set is not supported

The solution to the above problems

The connection configuration is changed to

String url = "jdbc:mysql://HOST:PORT/DATABASE"

At the same time, the database configuration file my.cnf is modified accordingly.

[client] default-character-set = utf8mb4 [MySQL] default-character-set = utf8mb4 [mysqld] character-set-client-handshake = FALSEcharacter-set-server = utf8mb4collation-server = utf8mb4_unicode_ci

Restart the service MySQL service

The problem is solved here.