• MySQL数据库字符集由utf8修改为utf8mb4。
  • 微信nickname乱码(emoji)及mysql编码格式(utf8mb4)设置解决的过程。

utf8mb4兼容utf8,且比utf8能表示更多的字符,是utf8字符集的超集。所以现在一些新的业务,比如ISO, Android等,会将MySQL数据库的字符集设置为utf8mb4。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[root@localhost ~]# find / -name my.cnf
/etc/my.cnf
[root@localhost ~]# vim /etc/my.cnf
# 添加如下配置后重启
[client]
default-character-set=utf8mb4

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
skip-character-set-client-handshake=false

[mysql]
default-character-set=utf8mb4

重启:systemctl restart mysqld
检查配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8mb4_unicode_ci |
| collation_database | utf8mb4_unicode_ci |
| collation_server | utf8mb4_unicode_ci |
+----------------------+--------------------+
3 rows in set (0.01 sec)

mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+----------------------------+
| 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 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql> ALTER DATABASE apg CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql> ALTER TABLE apg.member CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
Query OK, 7 rows affected (0.39 sec)
Records: 7 Duplicates: 0 Warnings: 0

SHOW VARIABLES LIKE ‘character_set%’;
SHOW VARIABLES LIKE ‘collation%’;

更改数据库编码:ALTER DATABASE apg CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
更改表编码:ALTER TABLE apg.member CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

查看数据库字符集:
select * from information_schema.SCHEMATA where SCHEMA_NAME=’apg’;

查看表字段字符集:
select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLLATION_NAME from information_schema.COLUMNS where TABLE_SCHEMA=’apg’ and TABLE_NAME=’member’;

查看表字符集:
select TABLE_SCHEMA,TABLE_NAME,TABLE_COLLATION from information_schema.TABLES where TABLE_SCHEMA=’apg’;

经测试,已经正确存入数据库了,因为取出来的时候是还原了的。只是在查询分析器里查出来是?,在 secureCRT 终端查询出来没有显示。

如果需要显示在页面上,参考:
https://www.cnblogs.com/leechenxiang/p/6699724.html