Detailed steps to store emoji expressions in MySQL

Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x94\xA5' for column An error occurs when storing Emoji expressions in Mysql.

iOS emoticons (emoji emoticons), although this emoticon is utf8 encoded, one character takes up 4 bytes, while MySQL utf8 encoding can only store 3-byte characters. In MySQL 5.6, you can set the encoding to utf8mb4, which is a superset of utf8.

1. Modify the MYSQL character set

Mysql sets the character encoding set to utf8mb4, and the corresponding database, table, and field are set to utf8mb4. Note that if the utf8 encoding set of an existing table is changed to utf8mb4, then the database, table, and field must be set and checked once. If only the database is changed, the corresponding table and field will not change, and the same is true for changing the table. You may also need to restart the database if you changed the configuration.

character_set_server=utf8mb4

2. Upgrade JDBC

Lower versions of JDBC will display garbled characters, garbled expressions, and even garbled Chinese characters.

The following are some corresponding situations of JDBC versions. The details are as follows: JDBC version supports EMOJI. The picture is found on the Internet. My 5.1.6 test inserts and displays Chinese and Emoji expressions normally.

3. Inherit the server configuration jdbc:mysql://xxx.xxx.xxx.xxx:3306/dbwww58com_chrcsm?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true. The parameters after the connection address are set to UTF-8 or can be automatically recognized. If not, try to remove the bold part to let the connection automatically recognize the server configuration. The specific situation depends on the JDBC version. Inherit the server configuration jdbc:mysql://xxx.xxx.xxx.xxx:3306/dbwww58com_chrcsm?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true. The parameters after the connection address are set to UTF-8 or can be automatically recognized. If not, try to remove the bold part to let the connection automatically recognize the server configuration. The specific situation depends on the JDBC version.

4. Set the init_connect parameter of MYSQL

After setting, you can verify it by showing variables like 'init_connect';, as shown in Figure 2 init_connect parameter query:

5. The encoding set is set in the code. Perhaps it is because of the JDBC version. I use this method, and both insertion and query can proceed normally. connection.prepareStatement("set names utf8mb4").executeQuery();Then insert it through connection.prepareStatement. The encoding set is set in the code perhaps because of the JDBC version. I use this method, and both insertion and query can proceed normally. connection.prepareStatement("set names utf8mb4").executeQuery();Then insert it through connection.prepareStatement.