Mysql/java service-side support for emoji

Objective:

The recently developed iOS project, because of the need for user text storage, naturally encountered an issue where emoticons such as emoji were supported by MySQL DB. Troubled for a few days, on the verge of desperate abandonment, finally completed the conversion and migration. In this special analysis and collation, convenient for more people.

Problem Description:

If the UTF8 character set is a Java server, when the store contains a emoji expression, it throws an exception similar to the following:

Java.sql.SQLException:Incorrectstring value:' \xf0\x9f\x92\x94 '  forColumn' name 'At row1At Com.mysql.jdbc.SQLError.createSQLException (Sqlerror.java:1073) at Com.mysql.jdbc.MysqlIO.checkErrorPacket (Mysqlio.java:3593) at Com.mysql.jdbc.MysqlIO.checkErrorPacket (Mysqlio.java:3525) at Com.mysql.jdbc.MysqlIO.sendCommand (Mysqlio.java:1986) at Com.mysql.jdbc.MysqlIO.sqlQueryDirect (Mysqlio.java:2140) at Com.mysql.jdbc.ConnectionImpl.execSQL (Connectionimpl.java:2620) at Com.mysql.jdbc.StatementImpl.executeUpdate (Statementimpl.java:1662) at Com.mysql.jdbc.StatementImpl.executeUpdate (Statementimpl.java:1581)

This is the exception that is not supported by the character set. Because the UTF-8 encoding can be two, three, four bytes, where the emoji expression is 4 bytes, and the MySQL UTF8 encoding up to 3 bytes, so the data is not plugged in.

Issues to consider before upgrading:

If your project is going to store user text for mobile products, it will be imperative to upgrade your DB character set from traditional character sets such as UTF8/GBK to UTF8MB4. You can convert emoji and other special characters through the application layer to achieve the original DB compatibility, which I think is feasible, but you may have gone a detour.

UTF8MB4 is completely backwards compatible as UTF8 's super set, so don't worry about character compatibility issues. A major concern in switching is that MySQL needs to be restarted (although the official MySQL document says it can be dynamically modified, but after several tests, or a reboot is required), the impact on the business availability rate is a big issue to consider, and it is not discussed for the time being.

Upgrade steps:

The minimum MySQL version of 1.UTF8MB4 supports version 5.5.3+, if not, upgrade to a newer version.

MySQL version view command see: Four ways to view the MySQL version; MySQL installation steps see: How to upgrade MySQL to the latest version of MySQL in Linux
2. Modify the database, table, and column character sets. refer to the following statement:
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
ALTER TABLE table_name CONVERT to CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE table_name Change column_name VARCHAR (191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
3. Modify the MySQL configuration file my.cnf (Windows is My.ini)

MY.CNF generally in the etc/mysql/my.cnf position. When you find it, add the following three sections:

[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. Restart MySQL Server, check the character set

1.) Restart Command reference:/etc/init.d/mysql restart

2.) Enter command: MySQL, go to MySQL command line (if prompt does not have permission, can try to enter mysql-uroot-p your password)

3.) in the MySQL command line, enter: SHOW VARIABLES where variable_name like ' character_set_% ' OR variable_name like ' collation% ';

Check whether the following:

+--------------------------+--------------------+
| 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 instructions under: Collation_connection/collation_database/collation_server If it is utf8mb4_general_ci, no relationship. But we must ensure that character_set_client/character_set_connection/character_set_database/character_set_results/character_set_ Server is UTF8MB4. For information on what these character set configurations are for, see: Drill down to MySQL character set settings

5. If you are using a Java server, upgrade or make sure that your MySQL connector version is higher than 5.1.13, or you will still not be able to use UTF8MB4
This is the official MySQL release note, you can view the instructions and download the latest MySQL connector for Java jar package.
Here is an offer for you: Mysql-connector-java-5.1.31-bin.jar
At the same time remember to modify the POM configuration Oh ~

6. Check the DB configuration file on your server:

Jdbc.driverclassname=com.mysql.jdbc.driver
jdbc.url=jdbc:mysql://localhost:3306/database?useunicode=true&characterencoding=utf8&autoreconnect= True&rewritebatchedstatements=true
Jdbc.username=root
Jdbc.password=password

Special description of the Jdbc.url configuration: If you have upgraded the Mysql-connector, Characterencoding=utf8 can be automatically recognized as UTF8MB4 (and of course the original UTF8), And the AutoReConnect configuration I strongly recommend matching, I just ignored this property, resulting from the cache because of the latest configuration, not read to the DB, resulting in the inability to use the UTF8MB4 character set, how painful the understanding!!

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Mysql/java service-side support for emoji