首页 > 资讯 > 数据库 >第09期：有关 MySQL 字符集的乱码问题

980

分享到

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

第09期：有关 MySQL 字符集的乱码问题 2018-01-21 06:01:37 980人浏览才女

摘要

相信大家通过前几篇文章，已经了解了 Mysql 字符集使用相关注意事项。那么数据乱码问题在这儿显得就非常简单了，或许说可能不会出现这样的问题。数据之所以会乱码，在 mysql 里无非有以下几类情况：一、转码失败在数据写入到表的过程中

相信大家通过前几篇文章，已经了解了 Mysql 字符集使用相关注意事项。那么数据乱码问题在这儿显得就非常简单了，或许说可能不会出现这样的问题。

数据之所以会乱码，在 mysql 里无非有以下几类情况：

一、转码失败

在数据写入到表的过程中转码失败，数据库端也没有进行恰当的处理，导致存放在表里的数据乱码。

针对这种情况，前几篇文章介绍过客户端发送请求到服务端。

其中任意一个编码不一致，都会导致表里的数据存入不正确的编码而产生乱码。

比如下面简单一条语句：

set @a = "文本字符串";
insert into t1 values(@a);

变量 @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 warningsG
*************************** 1. row ***************************
  Level: Warning
   Code: 1300
Message: Invalid gb2312 character string: "E79785"
*************************** 2. row ***************************
  Level: Warning
   Code: 1366
Message: Incorrect string value: "xE7x97x85xE6xAFx92..." 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: "xE7x97x85xE6xAFx92..." 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 t3G
*************************** 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)