Detailed explanation of MySQL/Java server support for emoji and problem solving

This article describes the support and problem solving methods of MySQL Java server for emoji. Share with you for your reference, the details are as follows:

Problem Description

The microblog data captured by the bottom layer is stored in MySQL. Some data fails to be stored. After checking Tomcat, the core error information is as follows:

// Throw an exception if the character set is not supported
sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x97\xF0\x9F...' for column 'CONTENT' at row 1

Cause Analysis

• Before MYSQL 5.5, utf8 encoding only supports 1 to 3 bytes, that is, the unicode encoding area of ​​​​the BMP part (basically the area 0000 to FFFF).
• Emoji expressions are 4 bytes, and MySQL's utf8 encoding is at most 3 bytes, so the data cannot be inserted
• After 5.5, MySQL added utf8mb4 encoding, which can support 4 bytes, that is, a character can have a maximum of 4 characters, so utf8mb4 encoding can support more character sets.
• Therefore, if the project wants to store user texts for mobile products, the DB character set must be upgraded from traditional character sets such as UTF8/GBK to utf8mb4

【Note】

• utf8mb4 is compatible with utf8 and can represent more characters than utf8. Therefore, you do not need to worry about character compatibility issues when upgrading the DB character set from traditional character sets such as UTF8 or GBK to utf8mb4.

Workaround

Filter special emoticons

content = content.replaceAll("[\\x{10000}-\\x{10FFFF}]", ""); // Simple and crude, not humane

Upgrading the database character set

1. The MySQL version supported by utf8mb4 is 5.5.3+. If MySQL is lower than this version, please upgrade it first.

2. Modify the database, table and column character set

3. Modify the MySQL configuration file my.cnf (my.ini for Windows). my.cnf is generally in the etc/mysql/ directory. After finding it, 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 and check the character set

Enter in the mysql command line:

SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';

Character set before restart

Character set after restart

【Note】

• The following fields must be utf8mb4
  • character_set_client
  • character_set_connection
  • character_set_database
  • character_set_results
  • character_set_server

After completing the above 4 steps, use the MySQL client navicat to manually add Emoji expressions ⛄ to the table. If it can be saved successfully, it means that the database has been upgraded successfully.

For the specific differences between these character set configurations, refer to In-depth MySQL character set settings

5. Check the server-side db configuration file

// The links used when configuring spring are as follows:
url="jdbc:mysql://localhost:3306/gzhou?allowMultiQueries=true&useUnicode=true&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&autoReconnect=true&autoReconnectForPools=true&failOverReadOnly=false&maxReconnects=10"
// If characterEncoding=utf8mb4, an error message will be displayed when inserting data. Caused by: java.sql.SQLException: Unsupported character encoding 'utf8mb4'
// Remove characterEncoding or characterEncoding=utf8

Emoji

In the Android system, Sogou and Baidu input methods come with built-in Emoji expressions.

【Note】

If the Emoji symbols between systems are incompatible, only squares will be seen;

Readers who are interested in more MySQL-related content can check out the following topics: "Summary of MySQL Common Functions", "Summary of MySQL Log Operation Skills", "Summary of MySQL Transaction Operation Skills", "Summary of MySQL Stored Procedure Skills" and "Summary of MySQL Database Lock-Related Skills".

I hope this article will be helpful to everyone's MySQL database design.