- A+
所属分类：MySQL
MySQL 需要支持 emoji 表情符号版本需要大于5.5.3，且字符集需要设置为utf8mb4 字符集。
查看服务器字符集设置
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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 | +--------------------------+--------------------+ |
查看数据库字符集
|
1 2 3 4 5 6 |
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 | +--------------+-------------+----------------------------+------------------------+----------+ |
查看表字符集
|
1 2 3 4 |
mysql> select TABLE_SCHEMA,TABLE_NAME,TABLE_COLLATION from information_schema.TABLES; +--------------------+----------------------------------------------------+--------------------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_COLLATION | +--------------------+----------------------------------------------------+--------------------+ |
查看列字符集
|
1 2 3 4 |
mysql> select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLLATION_NAME from COLUMNS; +--------------------+----------------------------------------------------+--------------------------------------------+--------------------+ | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | COLLATION_NAME | +--------------------+----------------------------------------------------+--------------------------------------------+--------------------+ |
转换字符集语句
|
1 2 3 4 5 6 7 8 9 |
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。
微信公众号
扫一扫关注运维生存时间公众号，获取最新技术文章~
来自外部的引用： 2