第09期：有关 MySQL 字符集的乱码问题
2020-07-22 本文已影响0人
爱可生开源社区
相信大家通过前几篇文章，已经了解了 MySQL 字符集使用相关注意事项。那么数据乱码问题在这儿显得就非常简单了，或许说可能不会出现这样的问题。
数据之所以会乱码，在 MySQL 里无非有以下几类情况：
一、转码失败
在数据写入到表的过程中转码失败，数据库端也没有进行恰当的处理，导致存放在表里的数据乱码。
针对这种情况，前几篇文章介绍过客户端发送请求到服务端。
其中任意一个编码不一致，都会导致表里的数据存入不正确的编码而产生乱码。
比如下面简单一条语句：
set @a = "文本字符串";
insert into t1 values(@a);
1.变量 @a 的字符编码是由参数 CHARACTER_SET_CLIENT 决定的，假设此时编码为 A，也就是变量 @a 的编码。
2.写入语句在发送到 MySQL 服务端之前的编码由 CHARACTER_SET_CONNECTION 决定，假设此时编码为 B。
3.经过 MySQL 一系列词法，语法解析等处理后，写入到表 t1，表 t1 的编码为 C。
那这里编码 A、编码 B、编码 C 如果不兼容，写入的数据就直接乱码。
来看下数据写入过程乱码情况：
-- 我的终端字符集是 utf8
root@ytt-pc:/home/ytt# locale
LANG=zh_CN.UTF-8
LANGUAGE=zh_CN:zh
LC_CTYPE="zh_CN.UTF-8"
...
LC_IDENTIFICATION="zh_CN.UTF-8"
LC_ALL=
-- 新建立一个连接，客户端这边字符集为 gb2312
root@ytt-pc:/home/ytt# mysql -S /tmp/mysqld_3305.sock --default-character-set=gb2312
...
mysql> create database ytt_new10;
Query OK, 1 row affected (0.02 sec)
mysql> use ytt_new10;
Database changed
-- 表的字符集为 utf8
mysql> create table t1(a1 varchar(100)) charset utf8mb4;
Query OK, 0 rows affected (0.04 sec)
-- 插入一条数据，有两条警告信息
mysql> insert into t1 values ("病毒滚吧！");
Query OK, 1 row affected, 2 warnings (0.01 sec)
-- 两条警告的内容， 对于字段 a1，内容不正确，但是依然写入了。
mysql> show warnings\G
*************************** 1. row ***************************
Level: Warning
Code: 1300
Message: Invalid gb2312 character string: 'E79785'
*************************** 2. row ***************************
Level: Warning
Code: 1366
Message: Incorrect string value: '\xE7\x97\x85\xE6\xAF\x92...' for column 'a1' at row 1
2 rows in set (0.00 sec)
-- 那检索出来看到，数据已经不可逆的乱码了。
mysql> select * from t1;
+-----------+
| a1 |
+-----------+
| ???▒??▒ |
+-----------+
1 row in set (0.00 sec)
那如何防止这种情形出现呢？方法有两种：
1、把客户端编码设置成和表编码一致或者兼容的编码
mysql> truncate t1;
Query OK, 0 rows affected (0.06 sec)
-- 把客户端字符集设置为 utf8mb4
mysql> set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)
-- 数据正常写入
mysql> insert into t1 values ("病毒滚吧！");
Query OK, 1 row affected (0.01 sec)
-- 数据正常检索
mysql> select * from t1;
+-----------------+
| a1 |
+-----------------+
| 病毒滚吧！ |
+-----------------+
1 row in set (0.00 sec)
2、设置合适的 SQL_MODE 强制避免不兼容的编码插入数据。
-- 设置 SQL_MODE 为严格事务表模式
mysql> set sql_mode = 'STRICT_TRANS_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)
-- 报错信息由 warnings 变为 error 拒绝插入
mysql> insert into t1(a1) values ("病毒滚吧！");
ERROR 1366 (HY000): Incorrect string value: '\xE7\x97\x85\xE6\xAF\x92...' for column 'a1' at row 1
二、客户端乱码
表数据正常，但是客户端展示后出现乱码。
这一类场景，指的是从 MySQL 表里拿数据出来返回到客户端，MySQL 里的数据本身没有问题。客户端发送请求到 MySQL，表的编码为 D，从 MySQL 拿到记录结果传输到客户端，此时记录编码为 E（CHARACTER_SET_RESULTS）。
那以上编码 E 和 D 如果不兼容，检索出来的数据就看起来乱码了。但是由于数据本身没有被破坏，所以换个兼容的编码就可以获取正确的结果。
这一类又分为以下三个不同的小类：
1、字段编码和表一致，客户端是不同的编码
比如下面例子， 表数据的编码是 utf8mb4，而 SESSION 1 发起的连接编码为 gbk。那由于编码不兼容，检索出来的数据肯定为乱码：
-- SESSION 1
root@ytt-pc:/home/ytt# mysql -S /tmp/mysqld_3305.sock --default-character-set=gbk;
...
mysql> use ytt_new10;
Database changed
mysql> show create table t3\G
*************************** 1. row ***************************
Table: t3
Create Table: CREATE TABLE `t3` (
`a1` varchar(10) DEFAULT NULL,
`a2` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql> select * from t3;
+--------------+--------------+
| a1 | a2 |
+--------------+--------------+
| ▒▒▒▒▒▒▒▒ | ▒▒▒▒▒▒▒▒ |
| ▒▒▒▒▒▒▒▒ | ▒▒▒▒▒▒▒˹▒▒▒ |
| ▒▒▒▒▒▒▒߹▒▒▒ | ▒▒▒▒▒▒▒˹▒▒▒ |
+--------------+--------------+
3 rows in set (0.00 sec)
接下来把 SESSION 1 的编码重置为默认 utf8mb4，那查出来的数据一定就是对的。
mysql> set names default;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from t3;
+--------------------+--------------------+
| a1 | a2 |
+--------------------+--------------------+
| 病毒快走 | 病毒走了 |
| 病毒快走 | 病毒走了哈哈 |
| 病毒快走哈哈 | 病毒走了哈哈 |
+--------------------+--------------------+
3 rows in set (0.00 sec)
2、表编码和客户端的编码一致，但是记录之间编码存在不一致的情形
比如表编码是 utf8mb4，应用端编码也是 utf8mb4，但是表里的数据可能一半编码是 utf8mb4，另外一半是 gbk。那么此时表的数据也是正常的，不过此时采用哪种编码都读不到所有完整的数据。这样数据产生的原因很多，比如其中一种可能性就是表编码多次变更而且每次变更不彻底导致（变更不彻底，我之前的篇章里有介绍）。举个例子，表 t3 的编码之前是 utf8mb4，现在是 gbk，而且两次编码期间都被写入了正常的数据。下面两次 select 查询的结果只有一半是正确的：
-- 前三条数据编码为 utf8mb4.
mysql> set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t3;
+-----------+-----------+
| a1 | a2 |
+-----------+-----------+
| 编码1 | 编码1 |
| 编码1 | 编码2 |
| 编码1 | 编码3 |
| 缂栫爜 | 缂栫爜 |
| 缂栫爜 | 缂栫爜 |
| 缂栫爜 | 缂栫爜 |
+-----------+-----------+
6 rows in set (0.00 sec)
-- 后三条数据编码为 gbk.
mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t3;
+--------+--------+
| a1 | a2 |
+--------+--------+
| ▒▒▒▒1 | ▒▒▒▒1 |
| ▒▒▒▒1 | ▒▒▒▒2 |
| ▒▒▒▒1 | ▒▒▒▒3 |
| 编码 | 编码 |
| 编码 | 编码 |
| 编码 | 编码 |
+--------+--------+
6 rows in set (0.01 sec)
那这样的问题该如何解决呢？
前提是找到两种不同编码记录的分界点！
比如表 t3 的记录前三条编码和后三条的编码不一致，那可以把两种数据分别导出，再导入到一张改好的表 t4 里。
-- utf8mb4 的编码数据，前三条导出
mysql> set names default;select * from t3 limit 0,3 into outfile '/var/lib/mysql-files/tx.txt';
Query OK, 0 rows affected (0.00 sec)
Query OK, 3 rows affected (0.00 sec)
-- GBK 编码的数据，后三条导出
mysql> set names gbk;select * from t3 limit 3,3 into outfile '/var/lib/mysql-files/ty.txt';
Query OK, 0 rows affected (0.00 sec)
Query OK, 3 rows affected (0.00 sec)
-- 建立一张新表 t4，编码改为统一的 utf8mb4
mysql> create table t4 (a1 varchar(10),a2 varchar(10)) charset utf8mb4;
Query OK, 0 rows affected (0.04 sec)
-- 分别导入两部分数据
mysql> load data infile '/var/lib/mysql-files/tx.txt' into table t4 character set gbk;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
mysql> load data infile '/var/lib/mysql-files/ty.txt' into table t4 ;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
-- 接下来看结果，一切正常
mysql> set names default;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t4;
+---------+---------+
| a1 | a2 |
+---------+---------+
| 编码 | 编码 |
| 编码 | 编码 |
| 编码 | 编码 |
| 编码1 | 编码1 |
| 编码1 | 编码2 |
| 编码1 | 编码3 |
+---------+---------+
6 rows in set (0.00 sec)
-- 完了把原来的表删掉，新表 t4 改名即可。
mysql> drop table t3;
Query OK, 0 rows affected (0.04 sec)
mysql> alter table t4 rename to t3;
Query OK, 0 rows affected (0.04 sec)
-- 再次查看记录，一切正常
mysql> select * from t3;
+---------+---------+
| a1 | a2 |
+---------+---------+
| 编码1 | 编码1 |
| 编码1 | 编码2 |
| 编码1 | 编码3 |
| 编码 | 编码 |
| 编码 | 编码 |
| 编码 | 编码 |
+---------+---------+
6 rows in set (0.00 sec)
3、每个字段的编码不一致，导致乱码
和第二点一样的场景。不同的是：非记录间的编码不统一，而是每个字段编码不统一。举个例子，表 c1 字段 a1,a2。a1 编码 gbk，a2 编码是 utf8mb4。那每个字段单独读出来数据是完整的，但是所有字段一起读出来，数据总会有一部分乱码。具体看下面的示例：
-- 字段 a1 编码 GBK，读出来正常，字段 a2 不正常。
mysql >set names gbk;
Query OK, 0 rows affected (0.00 sec)
mysql >select * from c1;
+--------------+----------------+
| a1 | a2 |
+--------------+----------------+
| 我在中国 | ▒▒▒▒▒й▒▒▒ã▒ |
| 你在日本 | ▒▒▒▒▒й▒▒▒ã▒ |
| 你在韩国 | ▒▒▒▒▒й▒▒▒ã▒ |
| 你在美国 | ▒▒▒▒▒й▒▒▒ã▒ |
| 中国太好 | ▒▒▒▒▒й▒▒▒ã▒ |
| 中国太棒 | ▒▒▒▒▒й▒▒▒ã▒ |
+--------------+----------------+
6 rows in set (0.00 sec)
-- 以编码 utf8mb4 来获取字段 a1 的值，显示不正常，字段 a2 读出来正常。
mysql >set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)
mysql >select * from c1;
+--------------------+-----------------------+
| a1 | a2 |
+--------------------+-----------------------+
| 鎴戝湪涓?浗 | 还是中国最好！ |
| 浣犲湪鏃ユ湰 | 还是中国最好！ |
| 浣犲湪闊╁浗 | 还是中国最好！ |
| 浣犲湪缇庡浗 | 还是中国最好！ |
| 涓?浗澶?ソ | 还是中国最好！ |
| 涓?浗澶?? | 还是中国最好！ |
+--------------------+-----------------------+
6 rows in set (0.00 sec)
以上结果怎么能一种编码的方式正常显示呢？也是类似第二种解决方式，把数据导出来，再导进去。由于 MySQL 处理数据是按照行的方式，按照列的方式会麻烦一点，我这里用 OS 层来合并导出的文件，再导入到 MySQL 表里。
-- 分别按列导出两个文件
mysql >select a2 from c1 into outfile '/var/lib/mysql-files/c1_a2.txt';
Query OK, 6 rows affected (0.01 sec)
mysql >select a1 from c1 into outfile '/var/lib/mysql-files/c1_a1.txt';
Query OK, 6 rows affected (0.00 sec)
-- OS 层用paste命令合并这两个文件
[root@ytt-pc mysql-files]# paste c1_a1.txt c1_a2.txt > c1.txt
-- 创建表c2，编码统一。
mysql >create table c2 (a1 varchar(10),a2 varchar(10)) charset utf8mb4;
Query OK, 0 rows affected (0.02 sec)
-- 导入合成后的文件到表c2
mysql >load data infile '/var/lib/mysql-files/c1.txt' into table c2 ;
Query OK, 6 rows affected (0.00 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 0
-- 删除表c1，重命名表c2为c1。
mysql >drop table c1;
Query OK, 0 rows affected (0.02 sec)
mysql >alter table c2 rename to c1;
Query OK, 0 rows affected (0.02 sec)
-- 显示结果正常，问题得到解决。
mysql >select * from c1;
+--------------+-----------------------+
| a1 | a2 |
+--------------+-----------------------+
| 我在中国 | 还是中国最好！ |
| 你在日本 | 还是中国最好！ |
| 你在韩国 | 还是中国最好！ |
| 你在美国 | 还是中国最好！ |
| 中国太好 | 还是中国最好！ |
| 中国太棒 | 还是中国最好！ |
+--------------+-----------------------+
6 rows in set (0.00 sec)
三、LATIN1
还有一种情形就是以 LATIN1 的编码存储数据
估计大家都知道字符集 LATIN1，LATIN1 对所有字符都是单字节流处理，遇到不能处理的字节流，保持原样，那么在以上两种存入和检索的过程中都能保证数据一致，所以 MySQL 长期以来默认的编码都是 LATIN1。这种情形，看起来也没啥不对的点，数据也没乱码，那为什么还有选用其他的编码呢？原因就是对字符存储的字节数不一样，比如 emoji 字符 "❤"，如果用 utf8mb4 存储，占用 3 个字节，那 varchar(12) 就能存放 12 个字符，但是换成 LATIN1，只能存 4 个字符。来看下这个例子就明白了。
-- 更改数据库 ytt_new10 字符集为 LATIN1
mysql> alter database ytt_new10 charset latin1;
Query OK, 1 row affected (0.02 sec)
mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)
mysql> use ytt_new10;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
-- 创建表 t2，默认字符集为 LATIN1
mysql> create table t2(a1 varchar(12));
Query OK, 0 rows affected (0.05 sec)
-- 插入emoji字符，只能插入4个字符
mysql> insert into t2 values ('❤❤❤❤');
Query OK, 1 row affected (0.02 sec)
-- 检索出来结果完全正确
mysql> select * from t2;
+--------------+
| a1 |
+--------------+
| ❤❤❤❤ |
+--------------+
1 row in set (0.00 sec)
-- 但是在加一个字符，插入第五个字符报错。
mysql> insert into t2 values ('❤❤❤❤❤');
ERROR 1406 (22001): Data too long for column 'a1' at row 1
-- 换张表t3,字符集为utf8mb4.
mysql> create table t3 (a1 varchar(12)) charset utf8mb4;
Query OK, 0 rows affected (0.06 sec)
-- 结果集的字符集也设置为utf8mb4.
mysql> set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)
-- 插入12个'❤'，也就是同样的表结构，存储的字符串比latin1多。
mysql> insert into t3 values (rpad('❤',12,'❤'));
Query OK, 1 row affected (0.01 sec)
mysql> select * from t3;
+--------------------------------------+
| a1 |
+--------------------------------------+
| ❤❤❤❤❤❤❤❤❤❤❤❤ |
+--------------------------------------+
1 row in set (0.00 sec)
其实 MySQL 一直到发布了 8.0 才把默认字符集改为 utf8mb4。比如现在依然是表 t2，如果想把编码改为 utf8mb4。那之前的数据必然没法正常显式：
-- 改为 utf8mb4
mysql> set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)
-- 数据显式乱码
mysql> select * from t2;
+--------------------------+
| a1 |
+--------------------------+
| ���� |
+--------------------------+
1 row in set (0.00 sec)
怎么解决这个问题。有两种方法：
1、把表 t2 的列 a1 先改为二进制类型，在改回来用 utf8mb4 的编码的字符类型。
-- 现改为 binary 类型
mysql> alter table t2 modify a1 binary(12);
Query OK, 1 row affected (0.11 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from t2;
+----------------------------+
| a1 |
+----------------------------+
| 0xE29DA4E29DA4E29DA4E29DA4 |
+----------------------------+
1 row in set (0.00 sec)
-- 再改为varchar(12) utf8mb4.
mysql> alter table t2 modify a1 varchar(12) charset utf8mb4;
Query OK, 1 row affected (0.15 sec)
Records: 1 Duplicates: 0 Warnings: 0
-- 数据就正常显式。
mysql> select * from t2;
+--------------+
| a1 |
+--------------+
| ❤❤❤❤ |
+--------------+
1 row in set (0.00 sec)
-- 接下来，再把表的字符集改回UTF8MB4。
mysql> alter table t2 charset utf8mb4;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
2、还是用最土的方法，把数据导出来，把表编码修改好，再把数据导入到表里。
-- 导出表t2数据。
mysql> select * from t2 into outfile '/var/lib/mysql-files/t2.dat';
Query OK, 1 row affected (0.00 sec)
-- 删除表
mysql> drop table t2;
Query OK, 0 rows affected (0.07 sec)
-- 重建表，编码为utf8mb4.
mysql> create table t2(a1 varchar(12)) charset utf8mb4;
Query OK, 0 rows affected (0.05 sec)
mysql> set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)
-- 导入之前导出来的数据
mysql> load data infile '/var/lib/mysql-files/t2.dat' into table t2;
Query OK, 1 row affected (0.01 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
-- 检索完全正常。
mysql> select * from t2;
+--------------+
| a1 |
+--------------+
| ❤❤❤❤ |
+--------------+
1 row in set (0.00 sec)
总结
通过上面的详细说明，相信对 MySQL 乱码问题已经有一个很好的了解了。那来回顾下本篇的内容。本篇主要列列举了 MySQL 乱码可能出现的场景，并对应给出详细的处理方法以及相关建议，希望以后大家永远不会出现乱码问题。
关于 MySQL 的技术内容，你们还有什么想知道的吗？赶紧留言告诉小编吧！
