How do I store emoji in MySQL?

Problem restore

When you use Erlang to store some special strings to MySQL , you can't read them. After checking, the binary format of these strings is as follows:

<<240,159,152,134,240,159,152,144>>

What do you see in binary Unicode ?

14> unicode:characters_to_list (<<240,159,152,134,240,159,152,144>>).

[128518,128528]

15> erlang:integer_to_list (128518,16). Percent View Hex

"1f606"

16> erlang:integer_to_list (128528,16).

"1f610"

Obviously,1f606 and 1f610 exceed The expression range of the common Unicode string 0000-ffff .

What do 1f606 and 1f610 stand for?

According to the instructions on the Unicode official website, this is a emoji(emoji), and the specific display form is related to the manufacturer.

reasons for storage failure on MySQL

For now, mostMySQLare all based onUTF8Storage, noteMySQLof theUTF8and what we call tradition.UTF8a little different. MySQLof theUTF8the code stipulates that there can be at most3bytes, and aemojiof theUnicodeit's over.3bytes, soMySQLof theUTF8unable to identify and storeemoji.

MYSQL&NBSP; UNICODE&NBSP; support the information from which you can learn   MYSQL&NBSP; 5.5&NBSP; utf8mb4 1 ~ 4  emoji

In addition UTF8MB4 backwards compatible with UTF8.

Solution Solutions

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;

&NBSP;

utf8mb4 The brothers under the link ( www.lampbrother.net ) ERLANG&NBSP; connection library   EMYSQL&NBSP;

Emysql:add_pool (Pool,[{size,1},{user, "User"},{password, "password"},{host, "host"},{port,3306},{encoding,utf8mb4 }]).

Percent or

Emysql:add_pool (pool,1, "User", "password", "host", 3306,undefined, UTF8MB4).

To make the table easy, you can modify the my.cnf:

# Server Character Set

[MySQL]

Default-character-set=utf8mb4

[Mysqld]

Character-set-server=utf8mb4

How do I store emoji in MySQL?