使MySQL能够存储emoji表情字符的设置教程

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