Shulou(Shulou.com)06/01 Report--

The company has new requirements that ios clients can use emoji emoticons in online comments. Before mysql 5.5, UTF-8 encoding only supported 1-3 bytes; from MySQL 5.5, it could support 4 bytes of UTF encoding utf8mb4, and one character could support more character sets and more emoji.

Utf8mb4 is compatible with utf8 and can represent more characters than utf8. It is a superset of the utf8 character set. So now some new businesses, such as emoji emoticons in IOS, set the character set of the MySQL database to utf8mb4.

Let's look at the question first:

Caused by: java.sql.SQLException: Incorrect string value:'\ xF6\ x9D\ x98\ x84' for column 'comment' at row 1 at com.mysql.jdbc.SQLError.createSQLException (SQLError.java:1074) at com.mysql.jdbc.MysqlIO.checkErrorPacket (MysqlIO.java:4096) at com.mysql.jdbc.MysqlIO.checkErrorPacket (MysqlIO.java:4028) at com.mysql.jdbc.MysqlIO.sendCommand (MysqlIO.java:2490) at com.mysql.jdbc.MysqlIO.sqlQueryDirect ( MysqlIO.java:2651) at com.mysql.jdbc.ConnectionImpl.execSQL (ConnectionImpl.java:2734) at com.mysql.jdbc.PreparedStatement.executeInternal (PreparedStatement.java:2155) at com.mysql.jdbc.PreparedStatement.executeUpdate (PreparedStatement.java:2458)

If we set the column comment to varchar, which is used to store comment information, the new feature that comes online now stores emoji emoji. Inserting emoji emoji will report the above error, and the UTF-8 code may be two, three, or four bytes. The Emoji emoji is 4 bytes, while the utf8 encoding of Mysql is up to 3 bytes, so the data cannot be inserted. Utf8mb4 is compatible with utf8 and can represent more characters than utf8.

Solution: convert the encoding of Mysql from utf8 to utf8mb4.

The articles on the Internet have their own opinions, and this paper records the real available parameters in the production environment.

The overall operation process is actually not difficult.

First, let's modify the my.cnf parameter.

[client] default-character-set=utf8mb4 [mysql] default-character-set=utf8mb4 [mysqld] character-set-server = utf8mb4collation-server = utf8mb4_unicode_ciinit_connect = 'SET NAMES utf8mb4'character-set-client-handshake = false

Second, modify the character set of the tables related to the database

Convert database to utf8mb4

Mysql > ALTER DATABASE erp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

Convert tables that have already been built into utf8mb4

Mysql > ALTER TABLE `erp_ comment` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

Set the field type that you need to use emoji to:

Mysql > ALTER TABLE `erp_ comment` MODIFY COLUMN `comment` varchar (100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

Three: restart the database server to make it effective

[root@HE3 ~] # / etc/init.d/mysqld restartShutting down MySQL.. SUCCESS! Starting MySQL.... SUCCESS!

Fourth, log in to the database to check whether the following:

Mysql > SHOW VARIABLES WHERE Variable_name LIKE 'character%' OR Variable_name LIKE' collation%' +-- +-+ | Variable_name | Value | +-- +-+ | 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 | | collation_connection | utf8mb4_unicode_ci | | collation_database | utf8mb4_unicode_ci | | collation_server | Utf8mb4_unicode_ci | +-- +-+ rows in set (0.00 sec)

Special note: if collation_connection/collation_database/collation_server is utf8mb4_general_ci, it doesn't matter. However, character_set_client/character_set_connection/character_set_database/character_set_results/character_set_server must be guaranteed to be utf8mb4.

Five: let the development side of the pom configuration, remove the characterEncoding parameter, and recompile it

If you are using a java server, upgrade or make sure your mysql connector version is higher than 5.1.13, otherwise you still cannot use utf8mb4

Finally, let the front-end app insert emoji emoticons, and that's fine.

A few small knowledge points:

It is easy to understand that character-set-server and collation-server are set to the utf8mb4 character set, that is, all the character sets related to the MySQL database are set to utf8mb4.

However, in order to realize that the utf8mb4 character set is also used after the client utf8 connects to the MySQL, init_connect='SET NAMES utf8mb4' is configured in the mysqld configuration to indicate that the initial connection is set to the utf8mb4 character set, and then configure a skip-character-set-client-handshake = true to ignore the client character set setting. No matter what the client character set is, it is used according to the settings in init_connect, which meets the needs of the application.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.