MySQL 存储 emoji 表情符号字符集

MySQL 需要支持 emoji 表情符号版本需要大于5.5.3，且字符集需要设置为utf8mb4 字符集。

查看服务器字符集设置

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 |
+--------------------------+--------------------+

查看数据库字符集

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     |
+--------------+-------------+----------------------------+------------------------+----------+

查看表字符集

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

查看列字符集

mysql> select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLLATION_NAME from COLUMNS;
+--------------------+----------------------------------------------------+--------------------------------------------+--------------------+
| TABLE_SCHEMA       | TABLE_NAME                                         | COLUMN_NAME                                | COLLATION_NAME     |
+--------------------+----------------------------------------------------+--------------------------------------------+--------------------+

转换字符集语句

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','longtext');

然后执行这些语句，将字符集转换为utf8mb4_unicode_ci。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-storage-emoji-emoticons-character-set/ 文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-storage-emoji-emoticons-character-set/