Shulou(Shulou.com)05/31 Report--

How to store emoji in MySQL? for this question, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

When you use erlang to store some special strings to MySQL, you can't read them out. Upon inspection, the binary format of these strings is as follows:

What is the binary unicode?

14 > unicode:characters_to_list (). [128518 1F606 128528] 15 > erlang:integer_to_list (12851881).% view hexadecimal "1F606" 16 > erlang:integer_to_list (128528). "1F610"

Obviously, 1F606 and 1F610 go beyond the expression range of the common unicode string 0000-FFFF.

Here is a quote: http://cenalulu.github.io/linux/character-encoding/

What do 1F606 and 1F610 stand for?

According to the description on unicode's official website, this is a kind of emoji (emoji), and the specific display form is related to the manufacturer.

Reasons for storage failure on MySQL

Currently, most MySQL is stored in utf8. Note that the utf8 of MySQL is a little different from what we call traditional utf8. The utf8 encoding of MySQL is limited to a maximum of 3 bytes, while the unicode of emoji has exceeded 3 bytes, so the utf8 of MySQL cannot recognize and store emoji.

For this reason, you can refer to the information on unicode support in MySQL, from which you can see that MySQL began to support utf8mb4 after 5.5. this encoding supports 1-4 bytes, and this encoding can represent emoji.

In addition, utf8mb4 is backward compatible with utf8.

For more information on unicode and utf8, please see: http://www.ruanyifeng.com/blog/2007/10/ascii_unicode_and_utf-8.html

Solution

Change the corresponding field in MySQL to utf8mb4 encoding:

ALTER TABLE T1 DEFAULT CHARACTER SET utf8mb4, MODIFY col1 CHAR (10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, MODIFY col2 CHAR (10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL

Reference: http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-upgrading.html

Change the connection code of the connection library to utf8mb4, and take emysql of erlang as an example:

Emysql:add_pool (pool, [{size,1}, {user, "user"}, {password, "password"}, {host, "host"}, {port,3306}, {encoding,utf8mb4}]). % or emysql:add_pool (pool,1, user, password, host, 3306 undefined, utf8mb4).

To make it easier to build a table, you can modify my.cnf:

# Server character set [mysql] default-character-set=utf8mb4 [mysqld] character-set-server=utf8mb4 's answer to the question on how to store emoji in MySQL is shared here. I hope the above content can be of some help to you. If you still have a lot of questions to solve, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.