• 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’;

查看某数据库下某表字符集：
select TABLE_SCHEMA,TABLE_NAME,TABLE_COLLATION from information_schema.TABLES where TABLE_SCHEMA=’apg’ AND TABLE_NAME=’member’;

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

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

批量修改数据表字符集

  • 执行以下语句，生成所有需要执行的 sql 语句，执行前，需要把下面的 schemaName 替换成需要修改的数据库名。执行成功之后，表的字符类型字段也会相应的改变为 utf8mb4

    1
    2
    3
    SELECT CONCAT("alter table `schemaName`.`", TABLE_NAME,"` convert to character set utf8mb4;") AS target_tables FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA="schemaName"
    AND TABLE_TYPE="BASE TABLE";

  • 仅修改表的字符集，执行 ALTER TABLE table_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;，执行完成后，表下的字符类型字段不会被改变