MySQL stores emoji emoticons in two different ways

Source: Internet
Author: User

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

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.