测试人员在测试时输入emoji表情时出现报错,查看日志看到是查询一个sql时报错Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE)。
这是数据库编码问题,进入数据库查看数据库的编码。
一、查看当前数据库的编码
mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | utf8_general_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+--------------------------+----------------------------+
11 rows in set (0.01 sec)
可以看到character_set_database,character_set_server ,collation_database,collation_server 的编码都是不支持emoji表情的。mysql utf8 是最多支持3个字节、utf8mb4 最多支持4个字节、emoji表情是四个字节。所以我们要把编码改成utf8mb4。
二、修改配置文件
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
修改完配置文件后重启
systemctl restart msqld
三、验证数据库编码是否改正
mysql> show variables where variable_name like "character_set%" or variable_name like "collation%";
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | utf8_general_ci |
| collation_database | utf8mb4_general_ci |
| collation_server | utf8mb4_general_ci |
+--------------------------+----------------------------+
11 rows in set (0.01 sec)
可以看到数据库编码已经改正了。