Mysql/Java server support for emoji, mysqlemoji

Mysql/Java server support for emoji, mysqlemoji
Preface:

Recently developed iOS projects require the storage of user text, and naturally encounter the problem of how emojis such as emoji are supported by mysql DB. After several days, the switch and migration were finally completed at the edge of desperate abandonment. This area is specially analyzed and organized to facilitate more people.

 

Problem description:

If the UTF8 character set is a Java server, an exception similar to the following will be thrown when the storage contains emoji:

java.sql.SQLException: Incorrect string value: '\xF0\x9F\x92\x94' for column 'name' at row 1      at 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 an exception that the character set does not support. Because the UTF-8 encoding may be two, three, four bytes, the Emoji expression is 4 bytes, and Mysql utf8 encoding up to 3 bytes, so the data cannot be inserted.

Considerations before upgrading:

If your project needs to store the user text of mobile products, it is 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 at the application level to achieve compatibility with the original DB. I think it is feasible, but you may have taken a detour.

As the super set of utf8, utf8mb4 is fully backward compatible, so you don't have to worry about character compatibility issues. The main concern during the switchover is that mysql needs to be restarted (although the mysql official documentation says that the configuration can be dynamically modified, it still takes effect after several tests ), the impact on service availability is a major issue that needs to be considered. We will not discuss it here.

Upgrade procedure:

1. The minimum mysql version of utf8mb4 supports version 5.5.3 +. If not, upgrade it to a newer version.

For details about how to view the mysql version command, see four methods for viewing the mysql version. For details about how to install mysql, see how to upgrade Mysql to the latest Mysql version in Linux.
2. Modify the database, table, and column character sets. Refer to the following statements:
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 (my. ini for windows)

My. cnf is generally located at etc/mysql/my. cnf. Add the following content in the following three parts:

[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 and check Character Set

1) restart command reference:/etc/init. d/mysql restart

2) enter the command mysql to enter the mysql command line (if you are prompted that you do not have the permission, you can try to enter your mysql-uroot-p password)

3) In the mysql command line, enter: show variables where Variable_name LIKE 'character _ set _ % 'OR Variable_name LIKE 'collation % ';

Check whether:

+ -------------------------- + -------------------- +
| 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)

Note: it does not matter if collation_connection/collation_database/collation_server is utf8mb4_general_ci. However, ensure that character_set_client/character_set_connection/character_set_database/character_set_results/character_set_server is utf8mb4. For more information about how to use these character set configurations, see: Mysql Character Set settings

5. If you are using a java Server, upgrade or make sure that your mysql connector version is later than 5.1.13. Otherwise, you still cannot 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 we provide one: mysql-connector-java-5.1.31-bin.jar
Remember to modify the pom configuration ~

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

 

In particular, jdbc. url configuration: If you have upgraded mysql-connector ctor, The characterEncoding = utf8 can be automatically recognized as utf8mb4 (of course compatible with the original utf8 ), I strongly recommend that you configure autoReconnect. I ignored this attribute before. As a result, the latest DB configuration is not read due to cache, And the utf8mb4 character set cannot be used, what a painful understanding !!

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.