第09期：有关 MySQL 字符集的乱码问题

相信大家通过前几篇文章，已经了解了 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 的技术内容，你们还有什么想知道的吗？赶紧留言告诉小编吧！

第09期：有关 MySQL 字符集的乱码问题

于7月 22, 2020由杨涛涛发布

MySQL 调优

第45期：一条 SQL 语句优化的基本思路

MySQL 调优

第44期：无主键分区表该不该使用

MySQL 调优

第43期：多表关联场景下如何用好分区表

第09期：有关 MySQL 字符集的乱码问题

于7月 22, 2020由杨涛涛发布

相关文章

MySQL 调优

第45期：一条 SQL 语句优化的基本思路

MySQL 调优

第44期：无主键分区表该不该使用

MySQL 调优

第43期：多表关联场景下如何用好分区表