MySQL Support emoji Emoticons

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:

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 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 the 1.UTF8MB4 supports version 5.5.3+, if not, upgrade to a newer version.

mysql version view commands see: Four ways to view the MySQL version; MySQL installation steps see: How to upgrade MySQL to MySQL latest version of 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 My.ini)

MY.CNF generally in the etc/mysql/my.cnf position. Once found, add the following in 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 character set

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

2.) Enter the command: MySQL, enter the MySQL command line (if prompted without permission, you 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, upgrading or ensuring that your MySQL connector version is higher than 5.1.13, 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 is a: Mysql-connector-java-5.1.31-bin.jar
also 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 (also compatible with 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!!

MySQL Support emoji Emoticons