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