How to enable MySQL to store emoji characters,

How to enable MySQL to store emoji characters,

MySQL must support emoji Versions later than 5.5.3, and set the character set to utf8mb4.

What is the difference between utf8mb4 and utf8? In the past, mysql utf8 had a maximum of 3 characters, while utf8mb4 was extended to a single character with a maximum of 4 bytes. Therefore, more character sets are supported.

Converts the Mysql code from utf8 to utf8mb4.

Required> = MySQL 5.5.3 and slave database must also be 5.5. Earlier versions do not support this character set or duplicate error.

Stop the MySQL Server Service

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'

Restart MySQL Server and check the character set.

View server Character Set settings

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character%' OR Variable_name LIKE 'collation%';

+--------------------------+--------------------+| Variable_name      | Value       |+--------------------------+--------------------+| character_set_client   | utf8mb4      || character_set_connection | utf8mb4      || character_set_database  | utf8mb4      || character_set_filesystem | binary       || character_set_results  | utf8mb4      || character_set_server   | utf8mb4      || character_set_system   | utf8        || collation_connection   | utf8mb4_unicode_ci || collation_database    | utf8mb4_unicode_ci || collation_server     | utf8mb4_unicode_ci |+--------------------------+--------------------+

View database character sets

mysql> select * from SCHEMATA where SCHEMA_NAME='ttlsa';

+--------------+-------------+----------------------------+------------------------+----------+| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |+--------------+-------------+----------------------------+------------------------+----------+| def     | ttlsa   | utf8mb4          | utf8mb4_unicode_ci   | NULL   |+--------------+-------------+----------------------------+------------------------+----------+

View table character sets

mysql> select TABLE_SCHEMA,TABLE_NAME,TABLE_COLLATION from information_schema.TABLES;

+--------------------+----------------------------------------------------+--------------------+| TABLE_SCHEMA    | TABLE_NAME                     | TABLE_COLLATION  |+--------------------+----------------------------------------------------+--------------------+

View column character sets

mysql> select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLLATION_NAME from COLUMNS;

+--------------------+----------------------------------------------------+--------------------------------------------+--------------------+| TABLE_SCHEMA    | TABLE_NAME                     | COLUMN_NAME                | COLLATION_NAME   |+--------------------+----------------------------------------------------+--------------------------------------------+--------------------+

Character Set conversion statement

use information_schema;SELECT concat("ALTER DATABASE `",table_schema,"` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;") as _sql FROM `TABLES` where table_schema like "DB_NAME" group by table_schema;SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name,"` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as _sql FROM `TABLES` where table_schema like "DB_NAME" group by table_schema, table_name;SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name, "` CHANGE `",column_name,"` `",column_name,"` ",data_type,"(",character_maximum_length,") CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as _sql FROM `COLUMNS` where table_schema like "DB_NAME" and data_type in ('varchar');SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name, "` CHANGE `",column_name,"` `",column_name,"` ",data_type," CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as _sql FROM `COLUMNS` where table_schema like "DB_NAME" and data_type in ('text','tinytext','mediumtext','long

Articles you may be interested in:

• Mysql Character Set and collation (Mysql collation)
• Perfect conversion of MySQL character set to solve the problem of checking garbled characters in utf8 source files