MySQL 5.5.3 and later versions support the storage of emoji

Some chat versions of emoji are commonly used in office-based communication software, and it is found that they cannot be stored in mysql.

Because the four bytes (\ xF0 \ x9F \ x90 \ xAC) of the emoji during storage );
Because the database uses the utf8_general_ci character set utf8_ci, this proofreading rule (collation) supports a maximum of three bytes, so the four bytes of emoji are not fully stored, and thus cannot be displayed.

Brief description

The utf8_general_ci and ut8_unicode_ci mentioned above are two character encoding methods of UTF8. the difference is the sorting and comparison of characters ).

MySQL 5.5.3 and later versions support the utf8mb4 character set. It enhances the processing capability of the bitwise (code point) based on the same processing performance as the utf8 data format. Utf8mb4 corresponds to utf8mb4_general_ci and utf8mb4_general_ci.

Utf8mb4_general_ci is based on Unicode standard sorting and comparison and supports more languages.
Utf8mb4_general_ci cannot parse all Unicode classification rules. Some special language or character processing problems exist. However, in terms of performance, it can sorting and comparison faster, because it adopts a set of performance-related shortcuts (performance-related shortcuts ).
Solution

We have known a solution through the above, but there is a hard condition that is your database version. What if your database version does not reach 5.5.3... To sum up, mysql supports at least two ways to store emoji expressions.

1. Modify the database code to utf8mb4, provided that your mysql database version must be 5.5.3 or later.
2. Convert the text with emoji into base64 for storage, and perform corresponding decoding and return when returning
The following describes how to use the first method:

Change the database encoding from utf8 to utf8mb4

Set character_set_client = utf8mb4;
Set character_set_connection = utf8mb4;
Set character_set_database = utf8mb4;
Set character_set_results = utf8mb4;
Set character_set_server = utf8mb4;
Convert the generated table to utf8mb4.

Alter table TABLE_NAME convert to character set utf8mb4 COLLATE utf8mb4_bin;
Note: this solution saves much convenience in code transfer and processing.