How to insert Emoji expressions into MySQL

Preface

Today, when I was designing a feedback form for an open source project, I encountered the problem of failing to insert emoji expressions. I looked for many solutions online, but the answers were varied and I didn’t find any that worked.

After some trouble, I finally successfully inserted the emoji expression. This article will share with you my implementation process. Developers who are interested are welcome to read this article.

Written in front

My server is a Mac system, and MySQL is installed using brew. The location of its configuration file may be slightly different in Windows/Linux, depending on the actual situation.

Let me show you its error message first:

chat_system> UPDATE chat_system.feedback t SET t.comments = 'Feedback information test 😂' WHERE t.id = 1
[2020-12-01 21:36:08] [HY000][1366] Incorrect string value: '\xF0\x9F\x98\x82' for column 'comments' at row 1
[2020-12-01 21:36:08] [HY000][1366] Incorrect string value: '\xF0\x9F\x98\x82' for column 'comments' at row 1

Implementation ideas

Because the database uses UTF-8 encoding format by default, ordinary strings take up 3 bytes and emoticons take up 4 bytes. At this time, UTF-8 is not enough. The utf8mb4 character set can solve this problem.

Note: The utf8mb4 character set requires the database version to be higher than 5.5.3.

So, what we have to do is as follows:

• Modify the mysql configuration file and set its encoding format
• Modify the database character set encoding
• Modify the database table character set encoding

Implementation process

The default order in which MySQL reads configuration is: /etc/my.cnf, /etc/mysql/my.cnf, /usr/local/etc/my.cnf, ~/.my.cnf. If these configuration files do not exist in the corresponding directory, you need to create a new one.

• Modify the database configuration file my.cnf. My file location is: /usr/local/etc. Add the following code

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'
character-set-client-handshake = false
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4

• Change the database character set encoding and execute the following SQL statement after logging into MySQL.

# Set the database character set encoding, chat_system is the database name, according to your actual situation ALTER DATABASE chat_system CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

• Change the character set encoding of the database table and execute the following SQL statement after logging into MySQL.

# Set the character set encoding of the database table. chat_system.feedback_comment_reply is the corresponding table name in my database. ALTER TABLE chat_system.feedback_comment_reply CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

After completing the above operations, let's check whether the modification is successful. Log in to MySQL and execute the following SQL statement.

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

If subordinate information is displayed, it means that we have completed the modification.

Test Cases

Let's insert an emoji expression to test it:

UPDATE chat_system.feedback t SET t.comments = 'Feedback information test 😂' WHERE t.id = 1;

As shown below, no error is reported and the insertion is successful.

We use dataGrap to view the data in the database table. As shown below, it shows a ?, which means the software cannot recognize it.

Logically speaking, the insertion should be successful. We try it with the postman request interface and it is successfully displayed 😄.

The above is the details of how to insert Emoji emoticons in MySQL. For more information about inserting Emoji emoticons in MySQL, please pay attention to other related articles on 123WORDPRESS.COM!