MySQL 5.5.3 and later versions support the storage of emoji

Source: Internet
Author: User
Tags character set comparison versions


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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.