MySQL 需要支持 emoji 表情符號版本需要大于5.5.3，且字符集需要設置為utf8mb4 字符集。
utf8mb4和utf8到底有什么區別呢？原來以往的mysql的utf8一個字符最多3字節，而utf8mb4則擴展到一個字符最多能有4字節，所以能支持更多的字符集。
將Mysql的編碼從utf8轉換成utf8mb4。
需要 >= MySQL 5.5.3版本、從庫也必須是5.5的了、低版本不支持這個字符集、復制報錯
停止MySQL Server服務
修改 my.cnf或者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'
重啟 MySQL Server、檢查字符集。
查看服務器字符集設置
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','long 
