Let MySQL support emoji emoticons

I. Problems and Causes

The app wanted to support emoji, but found that the MySQL database was unable to write emoticons. The reason is that our MySQL database is UTF8 encoded by default, the UTF8 encoding is stored with three bytes, but the emoji expression is 4 bytes, resulting in a data insertion exception.

The reason to find it is good to do, after the mysql5.5.3 version of the support UTF8MB4 encoding, which is UTF8 version of the upgrade, with 4 bytes to store. So what needs to be done is to upgrade the MySQL character set from UTF8 to UTF8MB4.

Second, upgrade step 1, back up the database

Although UTF8MB4 is compatible with UTF8, it is best to make a database backup.

2, upgrade MySQL version to 5.5.3 or more

This will not say, according to your server system to do the MySQL upgrade.

3, modify the MySQL default code on the server, modify the database, table, column character set is UTF8MB4

Modify the MySQL configuration file on the server my.conf (for example, Ubuntu, typically in/etc/mysql/my.conf):

[client] #xxx already configured default-character-set = Utf8mb4[mysql] #xxx already configured default-character-set =  UTF8MB4[MYSQLD] #xxx already configured character-set-client-handshake = falsecharacter-set-server =  Utf8mb4collation-server = utf8mb4_unicode_ciinit_connect='SET NAMES utf8mb4' 

Restart mysql After you changeit.

To modify the database, table, column character set:

ALTER DATABASE CHARACTER SET = = utf8mb4_unicode_ci; ALTER TABLE CONVERT  to CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Here we see the character set of the column is not modified, because after I have tried to find that the character set of the table is changed, the character set of the column in the table is automatically modified to UTF8MB4.

You can check it out:

Here, make sure that the variable value in the Red box is UTF8MB4.

You can see that the character set of the column has been changed.

4. Maximum length and index of check column

This is on the basis of the situation of self-examination adjustment.

5. Modify the Client connector code

Set the character set to UTF8MB4 in your code when you connect to the database

Reference article:

1. How to insert Utf-8 mb4 character (emoji inch ios5) in MySQL?

2, 1190000000616820

Let MySQL support emoji emoticons