MySQL stores emoji emoticons in two different ways

Recently in the data migration, there was a field of type Clob, after the migrated field type of Varchar,blob converted to string data, found that there is a emoji expression exists, resulting in data migration failure. The reason for the failure is that the Utf-8 type set in the MySQL database is 3 bytes, and the emoji expression Utf-8 is 4 bytes, so it cannot be passed in.

To enable MySQL to store emoji emoticons, you need a database version above 5.5.

There are two main ways to solve the problem:

The first way is to modify the encoding of the field.

The second way is to modify the configuration file.

The first way: Pro-Test available

1) Modify the encoding type of the corresponding table field

ALTER TABLE table_name MODIFY COLUMN Mark varchar (CHARACTER)

SET utf8mb4 COLLATE Utf8mb4_unicode_ci

DEFAULT NULL COMMENT ' remarks ';

Then make the following settings:

SET NAMES utf8mb4;

Then turn the CLOB type to char type [CONVERT (Mark,char)] To insert the field in the table.

Second approach: not tested because of a lack of test environment

1) Stop MySQL Database

2) Modify MY.CNF or Mysql.ini

[Client]

Default-character-set = Utf8mb4

[MySQL]

Default-character-set = Utf8mb4

[Mysqld]

Character-set-client-handshake = FALSE

Character-set-server = Utf8mb4

Collation-server = Utf8mb4_unicode_ci

init_connect= ' SET NAMES utf8mb4 '

3) Restart MySQL database

4) Check the character set and change the other character set

SHOW VARIABLES WHERE variable_name like ' character\_set\_% ' OR variable_name like ' collation% ';

To modify the database character set:

ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

To modify the character set of a table:

ALTER TABLE table_name CONVERT to CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

To modify the character set of a field:

ALTER TABLE table_name Change column_name column_name VARCHAR (191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

This article is from the "three countries Cold jokes" blog, please be sure to keep this source http://myhwj.blog.51cto.com/9763975/1869042

MySQL stores emoji emoticons in two different ways