The cause and solution of the failure of inserting emoji expression in mysql

Failure scenario

There is an emoji emoji in the user's nickname. When you call jdbc to insert into the mysql database, throw an exception java.sql.SQLException: Incorrect string value:'\ xF0\ x9F\ x90\ x9B'

Cause of failure

Mysql's utf8 encodes up to 3 bytes of a character, but an emoji emoji is 4 bytes, so utf8 does not support storing emoji emoticons. But utf8's superset utf8mb4 can have up to 4 bytes in a character, so it can support the storage of emoji emoticons.

I won't say much. Let's take a look at the detailed introduction.

Solution

one。 Modify database, table, column character sets

Modify database character set

ALTER DATABASE database name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci

Modify table character set

ALTER TABLE table name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci

Modify column character set

ALTER TABLE table name CHANGE field name field name the field's original data type CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci

The above three kinds can be modified according to their own actual situation.

two。 View mysql configuration variables

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

Notice whether the value of the variable character_set_server is equal to utf8mb4, if not, look at step 3.

three。 Modify 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, and save exit; try to re-insert to see if there is an error; if you report an error, you may need to restart the mysql service

Service mysqld restart

Summary

The above is the whole content of this article, I hope that the content of this article has a certain reference and learning value for your study or work, if you have any questions, you can leave a message and exchange, thank you for your support.