Reasons and solutions for failure to insert emoji expressions in MySQL

Failure Scenario

When calling JDBC to insert emoji expressions into the MySQL database, an exception java.sql.SQLException: Incorrect string value: '\xF0\x9F\x90\x9B' is thrown.

Cause of failure

A character in MySQL's utf8 encoding is at most 3 bytes, but an emoji expression is 4 bytes, so utf8 does not support the storage of emoji expressions. However, utf8mb4, a superset of utf8, can have a maximum of 4 bytes per character, so it can support the storage of emoji expressions.

Let’s take a look at the detailed introduction.

Solution

1. Modify the database, table, and column character sets

Modify the database character set

ALTER DATABASE database name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

Modify the table character set

ALTER TABLE table name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Modify the column character set

ALTER TABLE table name CHANGE field name field name the original data type of the field CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

The above three options can be modified according to your actual situation;

2. View MySQL configuration variables

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

Note whether the value of the character_set_server variable is equal to utf8mb4. If not, see step 3.

3. Modify the mysql configuration file

Here mysql is installed under centos, the configuration file name is my.cnf, and the path is based on the directory where you installed it.

vim /etc/my.cnf /etc/mysql/my.cnf 

my.cnf

Find character_set_server, set its value to utf8mb4, save and exit; try to insert again to see if there is an error; if there is an error, you may need to restart the MySQL service

service mysqld restart

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.