mysql 可逆编码_第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 的技术内容，你们还有什么想知道的吗？赶紧留言告诉小编吧！