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

发布时间:2020-07-24 浏览次数:244

相信大家通过前几篇文章,已经了解了 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 如果不兼容,写入的数据就直接乱码。
来看下数据写入过程乱码情况:

  1. -- 我的终端字符集是 utf8


  2. root@ytt-pc:/home/ytt# locale

  3. LANG=zh_CN.UTF-8

  4. LANGUAGE=zh_CN:zh

  5. LC_CTYPE="zh_CN.UTF-8"

  6. ...

  7. LC_IDENTIFICATION="zh_CN.UTF-8"

  8. LC_ALL=


  9. -- 新建立一个连接,客户端这边字符集为 gb2312


  10. root@ytt-pc:/home/ytt# mysql -S /tmp/mysqld_3305.sock --default-character-set=gb2312

  11. ...

  12. mysql> create database ytt_new10;

  13. Query OK, 1 row affected (0.02 sec)


  14. mysql> use ytt_new10;

  15. Database changed


  16. -- 表的字符集为 utf8

  17. mysql> create table t1(a1 varchar(100)) charset utf8mb4;

  18. Query OK, 0 rows affected (0.04 sec)


  19. -- 插入一条数据,有两条警告信息

  20. mysql> insert into t1 values ("病毒滚吧!");

  21. Query OK, 1 row affected, 2 warnings (0.01 sec)


  22. -- 两条警告的内容, 对于字段 a1,内容不正确,但是依然写入了。

  23. mysql> show warnings\G

  24. *************************** 1. row ***************************

  25.  Level: Warning

  26.   Code: 1300

  27. Message: Invalid gb2312 character string: 'E79785'

  28. *************************** 2. row ***************************

  29.  Level: Warning

  30.   Code: 1366

  31. Message: Incorrect string value: '\xE7\x97\x85\xE6\xAF\x92...' for column 'a1' at row 1

  32. 2 rows in set (0.00 sec)


  33. -- 那检索出来看到,数据已经不可逆的乱码了。

  34. mysql> select * from t1;

  35. +-----------+

  36. | a1        |

  37. +-----------+

  38. | ???▒??▒ |

  39. +-----------+

  40. 1 row in set (0.00 sec)

那如何防止这种情形出现呢?方法有两种:
1)把客户端编码设置成和表编码一致或者兼容的编码

  1. mysql> truncate t1;

  2. Query OK, 0 rows affected (0.06 sec)


  3. -- 把客户端字符集设置为 utf8mb4    

  4. mysql> set names utf8mb4;

  5. Query OK, 0 rows affected (0.00 sec)


  6. -- 数据正常写入

  7. mysql> insert into t1 values ("病毒滚吧!");

  8. Query OK, 1 row affected (0.01 sec)


  9. -- 数据正常检索

  10. mysql> select * from t1;

  11. +-----------------+

  12. | a1              |

  13. +-----------------+

  14. | 病毒滚吧! |

  15. +-----------------+

  16. 1 row in set (0.00 sec)

2)设置合适的 SQL_MODE 强制避免不兼容的编码插入数据。

  1. -- 设置 SQL_MODE 为严格事务表模式

  2. mysql> set sql_mode = 'STRICT_TRANS_TABLES';

  3. Query OK, 0 rows affected, 1 warning (0.00 sec)


  4. -- 报错信息由 warnings 变为 error 拒绝插入

  5. mysql> insert into t1(a1) values ("病毒滚吧!");

  6. 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。那由于编码不兼容,检索出来的数据肯定为乱码:

  1. -- SESSION 1

  2. root@ytt-pc:/home/ytt# mysql -S /tmp/mysqld_3305.sock --default-character-set=gbk;

  3. ...

  4. mysql> use ytt_new10;

  5. Database changed


  6. mysql> show create table t3\G

  7. *************************** 1. row ***************************

  8.       Table: t3

  9. Create Table: CREATE TABLE `t3` (

  10.  `a1` varchar(10) DEFAULT NULL,

  11.  `a2` varchar(10) DEFAULT NULL

  12. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

  13. 1 row in set (0.00 sec)


  14. mysql> select * from t3;

  15. +--------------+--------------+

  16. | a1           | a2           |

  17. +--------------+--------------+

  18. | ▒▒▒▒▒▒▒▒     | ▒▒▒▒▒▒▒▒     |

  19. | ▒▒▒▒▒▒▒▒     | ▒▒▒▒▒▒▒˹▒▒▒ |

  20. | ▒▒▒▒▒▒▒߹▒▒▒ | ▒▒▒▒▒▒▒˹▒▒▒ |

  21. +--------------+--------------+

  22. 3 rows in set (0.00 sec)

接下来把 SESSION 1 的编码重置为默认 utf8mb4,那查出来的数据一定就是对的。

  1. mysql> set names default;

  2. Query OK, 0 rows affected (0.01 sec)


  3. mysql> select * from t3;

  4. +--------------------+--------------------+

  5. | a1                 | a2                 |

  6. +--------------------+--------------------+

  7. | 病毒快走       | 病毒走了       |

  8. | 病毒快走       | 病毒走了哈哈 |

  9. | 病毒快走哈哈 | 病毒走了哈哈 |

  10. +--------------------+--------------------+

  11. 3 rows in set (0.00 sec)

2)表编码和客户端的编码一致,但是记录之间编码存在不一致的情形

比如表编码是 utf8mb4,应用端编码也是 utf8mb4,但是表里的数据可能一半编码是 utf8mb4,另外一半是 gbk。那么此时表的数据也是正常的,不过此时采用哪种编码都读不到所有完整的数据。这样数据产生的原因很多,比如其中一种可能性就是表编码多次变更而且每次变更不彻底导致(变更不彻底,我之前的篇章里有介绍)。举个例子,表 t3 的编码之前是 utf8mb4,现在是 gbk,而且两次编码期间都被写入了正常的数据。下面两次 select 查询的结果只有一半是正确的:

  1. -- 前三条数据编码为 utf8mb4.

  2. mysql> set names utf8mb4;

  3. Query OK, 0 rows affected (0.00 sec)


  4. mysql> select * from t3;

  5. +-----------+-----------+

  6. | a1        | a2        |

  7. +-----------+-----------+

  8. | 编码1   | 编码1   |

  9. | 编码1   | 编码2   |

  10. | 编码1   | 编码3   |

  11. | 缂栫爜 | 缂栫爜 |

  12. | 缂栫爜 | 缂栫爜 |

  13. | 缂栫爜 | 缂栫爜 |

  14. +-----------+-----------+

  15. 6 rows in set (0.00 sec)


  16. -- 后三条数据编码为 gbk.

  17. mysql> set names gbk;

  18. Query OK, 0 rows affected (0.00 sec)


  19. mysql> select * from t3;

  20. +--------+--------+

  21. | a1     | a2     |

  22. +--------+--------+

  23. | ▒▒▒▒1  | ▒▒▒▒1  |

  24. | ▒▒▒▒1  | ▒▒▒▒2  |

  25. | ▒▒▒▒1  | ▒▒▒▒3  |

  26. | 编码 | 编码 |

  27. | 编码 | 编码 |

  28. | 编码 | 编码 |

  29. +--------+--------+

  30. 6 rows in set (0.01 sec)

那这样的问题该如何解决呢?

前提是找到两种不同编码记录的分界点!
比如表 t3 的记录前三条编码和后三条的编码不一致,那可以把两种数据分别导出,再导入到一张改好的表 t4 里。

  1. -- utf8mb4 的编码数据,前三条导出

  2. mysql> set names default;select *  from t3 limit 0,3 into outfile '/var/lib/mysql-files/tx.txt';

  3. Query OK, 0 rows affected (0.00 sec)


  4. Query OK, 3 rows affected (0.00 sec)


  5. -- GBK 编码的数据,后三条导出

  6. mysql> set names gbk;select *  from t3 limit 3,3 into outfile '/var/lib/mysql-files/ty.txt';

  7. Query OK, 0 rows affected (0.00 sec)


  8. Query OK, 3 rows affected (0.00 sec)


  9. -- 建立一张新表 t4,编码改为统一的 utf8mb4

  10. mysql> create table t4 (a1 varchar(10),a2 varchar(10)) charset utf8mb4;

  11. Query OK, 0 rows affected (0.04 sec)


  12. -- 分别导入两部分数据

  13. mysql> load data infile '/var/lib/mysql-files/tx.txt' into table t4 character set gbk;

  14. Query OK, 3 rows affected (0.01 sec)

  15. Records: 3  Deleted: 0  Skipped: 0  Warnings: 0


  16. mysql> load data infile '/var/lib/mysql-files/ty.txt' into table t4 ;

  17. Query OK, 3 rows affected (0.01 sec)

  18. Records: 3  Deleted: 0  Skipped: 0  Warnings: 0


  19. -- 接下来看结果,一切正常

  20. mysql> set names default;

  21. Query OK, 0 rows affected (0.00 sec)


  22. mysql> select * from t4;

  23. +---------+---------+

  24. | a1      | a2      |

  25. +---------+---------+

  26. | 编码  | 编码  |

  27. | 编码  | 编码  |

  28. | 编码  | 编码  |

  29. | 编码1 | 编码1 |

  30. | 编码1 | 编码2 |

  31. | 编码1 | 编码3 |

  32. +---------+---------+

  33. 6 rows in set (0.00 sec)


  34. -- 完了把原来的表删掉,新表 t4 改名即可。

  35. mysql> drop table t3;

  36. Query OK, 0 rows affected (0.04 sec)


  37. mysql> alter table t4 rename to t3;

  38. Query OK, 0 rows affected (0.04 sec)


  39. -- 再次查看记录,一切正常

  40. mysql> select * from t3;

  41. +---------+---------+

  42. | a1      | a2      |

  43. +---------+---------+

  44. | 编码1 | 编码1 |

  45. | 编码1 | 编码2 |

  46. | 编码1 | 编码3 |

  47. | 编码  | 编码  |

  48. | 编码  | 编码  |

  49. | 编码  | 编码  |

  50. +---------+---------+

  51. 6 rows in set (0.00 sec)

3)每个字段的编码不一致,导致乱码

和第二点一样的场景。不同的是:非记录间的编码不统一,而是每个字段编码不统一。举个例子,表 c1 字段 a1,a2。a1 编码 gbk,a2 编码是 utf8mb4。那每个字段单独读出来数据是完整的,但是所有字段一起读出来,数据总会有一部分乱码。具体看下面的示例:

  1. -- 字段 a1 编码 GBK,读出来正常,字段 a2 不正常。

  2. mysql >set names gbk;

  3. Query OK, 0 rows affected (0.00 sec)


  4. mysql >select * from c1;

  5. +--------------+----------------+

  6. | a1           | a2             |

  7. +--------------+----------------+

  8. | 我在中国     | ▒▒▒▒▒й▒▒▒ã▒             |

  9. | 你在日本     | ▒▒▒▒▒й▒▒▒ã▒             |

  10. | 你在韩国     | ▒▒▒▒▒й▒▒▒ã▒             |

  11. | 你在美国     | ▒▒▒▒▒й▒▒▒ã▒             |

  12. | 中国太好     | ▒▒▒▒▒й▒▒▒ã▒             |

  13. | 中国太棒     | ▒▒▒▒▒й▒▒▒ã▒             |

  14. +--------------+----------------+

  15. 6 rows in set (0.00 sec)


  16. -- 以编码 utf8mb4 来获取字段 a1 的值,显示不正常,字段 a2 读出来正常。

  17. mysql >set names utf8mb4;

  18. Query OK, 0 rows affected (0.00 sec)


  19. mysql >select * from c1;

  20. +--------------------+-----------------------+

  21. | a1                 | a2                    |

  22. +--------------------+-----------------------+

  23. | 鎴戝湪涓?浗        | 还是中国最好!        |

  24. | 浣犲湪鏃ユ湰       | 还是中国最好!        |

  25. | 浣犲湪闊╁浗        | 还是中国最好!        |

  26. | 浣犲湪缇庡浗       | 还是中国最好!        |

  27. | 涓?浗澶?ソ         | 还是中国最好!        |

  28. | 涓?浗澶??          | 还是中国最好!        |

  29. +--------------------+-----------------------+

  30. 6 rows in set (0.00 sec)

以上结果怎么能一种编码的方式正常显示呢?也是类似第二种解决方式,把数据导出来,再导进去。由于 MySQL 处理数据是按照行的方式,按照列的方式会麻烦一点,我这里用 OS 层来合并导出的文件,再导入到 MySQL 表里。

  1. -- 分别按列导出两个文件

  2. mysql >select a2 from c1 into outfile '/var/lib/mysql-files/c1_a2.txt';

  3. Query OK, 6 rows affected (0.01 sec)


  4. mysql >select a1 from c1 into outfile '/var/lib/mysql-files/c1_a1.txt';

  5. Query OK, 6 rows affected (0.00 sec)


  6. -- OS 层用paste命令合并这两个文件

  7. [root@ytt-pc mysql-files]# paste c1_a1.txt c1_a2.txt  > c1.txt


  8. -- 创建表c2,编码统一。

  9. mysql >create table c2 (a1 varchar(10),a2 varchar(10)) charset utf8mb4;

  10. Query OK, 0 rows affected (0.02 sec)


  11. -- 导入合成后的文件到表c2

  12. mysql >load data infile '/var/lib/mysql-files/c1.txt' into table c2 ;

  13. Query OK, 6 rows affected (0.00 sec)

  14. Records: 6  Deleted: 0  Skipped: 0  Warnings: 0


  15. -- 删除表c1,重命名表c2为c1。

  16. mysql >drop table c1;

  17. Query OK, 0 rows affected (0.02 sec)


  18. mysql >alter table c2 rename to c1;

  19. Query OK, 0 rows affected (0.02 sec)


  20. -- 显示结果正常,问题得到解决。

  21. mysql >select * from c1;

  22. +--------------+-----------------------+

  23. | a1           | a2                    |

  24. +--------------+-----------------------+

  25. | 我在中国     | 还是中国最好!        |

  26. | 你在日本     | 还是中国最好!        |

  27. | 你在韩国     | 还是中国最好!        |

  28. | 你在美国     | 还是中国最好!        |

  29. | 中国太好     | 还是中国最好!        |

  30. | 中国太棒     | 还是中国最好!        |

  31. +--------------+-----------------------+

  32. 6 rows in set (0.00 sec)


三、LATIN1
还有一种情形就是以 LATIN1 的编码存储数据
估计大家都知道字符集 LATIN1,LATIN1 对所有字符都是单字节流处理,遇到不能处理的字节流,保持原样,那么在以上两种存入和检索的过程中都能保证数据一致,所以 MySQL 长期以来默认的编码都是 LATIN1。这种情形,看起来也没啥不对的点,数据也没乱码,那为什么还有选用其他的编码呢?原因就是对字符存储的字节数不一样,比如 emoji 字符 "❤",如果用 utf8mb4 存储,占用 3 个字节,那 varchar(12) 就能存放 12 个字符,但是换成 LATIN1,只能存 4 个字符。来看下这个例子就明白了。

  1. -- 更改数据库 ytt_new10 字符集为 LATIN1

  2. mysql> alter database ytt_new10 charset latin1;

  3. Query OK, 1 row affected (0.02 sec)


  4. mysql> set names latin1;

  5. Query OK, 0 rows affected (0.00 sec)


  6. mysql> use ytt_new10;

  7. Reading table information for completion of table and column names

  8. You can turn off this feature to get a quicker startup with -A


  9. Database changed


  10. -- 创建表 t2,默认字符集为 LATIN1

  11. mysql> create table t2(a1 varchar(12));

  12. Query OK, 0 rows affected (0.05 sec)


  13. -- 插入emoji字符,只能插入4个字符

  14. mysql> insert into t2 values ('❤❤❤❤');

  15. Query OK, 1 row affected (0.02 sec)


  16. -- 检索出来结果完全正确

  17. mysql> select * from t2;

  18. +--------------+

  19. | a1           |

  20. +--------------+

  21. | ❤❤❤❤         |

  22. +--------------+

  23. 1 row in set (0.00 sec)


  24. -- 但是在加一个字符,插入第五个字符报错。

  25. mysql> insert into t2 values ('❤❤❤❤❤');

  26. ERROR 1406 (22001): Data too long for column 'a1' at row 1


  27. -- 换张表t3,字符集为utf8mb4.

  28. mysql> create table t3 (a1 varchar(12)) charset utf8mb4;

  29. Query OK, 0 rows affected (0.06 sec)


  30. -- 结果集的字符集也设置为utf8mb4.

  31. mysql> set names utf8mb4;

  32. Query OK, 0 rows affected (0.00 sec)


  33. -- 插入12个'❤',也就是同样的表结构,存储的字符串比latin1多。

  34. mysql> insert into t3 values (rpad('❤',12,'❤'));

  35. Query OK, 1 row affected (0.01 sec)


  36. mysql> select * from t3;

  37. +--------------------------------------+

  38. | a1                                   |

  39. +--------------------------------------+

  40. | ❤❤❤❤❤❤❤❤❤❤❤❤                         |

  41. +--------------------------------------+

  42. 1 row in set (0.00 sec)

其实 MySQL 一直到发布了 8.0 才把默认字符集改为 utf8mb4。比如现在依然是表 t2,如果想把编码改为 utf8mb4。
那之前的数据必然没法正常显式:

  1. -- 改为 utf8mb4

  2. mysql> set names utf8mb4;

  3. Query OK, 0 rows affected (0.00 sec)


  4. -- 数据显式乱码

  5. mysql> select * from t2;

  6. +--------------------------+

  7. | a1                       |

  8. +--------------------------+

  9. | â�¤â�¤â�¤â�¤             |

  10. +--------------------------+

  11. 1 row in set (0.00 sec)

怎么解决这个问题。有两种方法:
1)把表 t2 的列 a1 先改为二进制类型,在改回来用 utf8mb4 的编码的字符类型。

  1. -- 现改为 binary 类型

  2. mysql> alter table t2 modify a1 binary(12);

  3. Query OK, 1 row affected (0.11 sec)

  4. Records: 1  Duplicates: 0  Warnings: 0


  5. mysql> select * from t2;

  6. +----------------------------+

  7. | a1                         |

  8. +----------------------------+

  9. | 0xE29DA4E29DA4E29DA4E29DA4 |

  10. +----------------------------+

  11. 1 row in set (0.00 sec)


  12. -- 再改为varchar(12) utf8mb4.

  13. mysql> alter table t2 modify a1 varchar(12) charset utf8mb4;

  14. Query OK, 1 row affected (0.15 sec)

  15. Records: 1  Duplicates: 0  Warnings: 0


  16. -- 数据就正常显式。

  17. mysql> select * from t2;

  18. +--------------+

  19. | a1           |

  20. +--------------+

  21. | ❤❤❤❤         |

  22. +--------------+

  23. 1 row in set (0.00 sec)


  24. -- 接下来,再把表的字符集改回UTF8MB4。

  25. mysql> alter table t2 charset utf8mb4;

  26. Query OK, 0 rows affected (0.02 sec)

  27. Records: 0  Duplicates: 0  Warnings: 0

2)还是用最土的方法,把数据导出来,把表编码修改好,再把数据导入到表里。

  1. -- 导出表t2数据。

  2. mysql> select * from t2 into outfile '/var/lib/mysql-files/t2.dat';

  3. Query OK, 1 row affected (0.00 sec)


  4. -- 删除表

  5. mysql> drop table t2;

  6. Query OK, 0 rows affected (0.07 sec)


  7. -- 重建表,编码为utf8mb4.

  8. mysql> create table t2(a1 varchar(12)) charset utf8mb4;

  9. Query OK, 0 rows affected (0.05 sec)


  10. mysql> set names utf8mb4;

  11. Query OK, 0 rows affected (0.00 sec)


  12. -- 导入之前导出来的数据

  13. mysql> load data infile '/var/lib/mysql-files/t2.dat' into table t2;

  14. Query OK, 1 row affected (0.01 sec)

  15. Records: 1  Deleted: 0  Skipped: 0  Warnings: 0


  16. -- 检索完全正常。

  17. mysql> select * from t2;

  18. +--------------+

  19. | a1           |

  20. +--------------+

  21. | ❤❤❤❤         |

  22. +--------------+

  23. 1 row in set (0.00 sec)


总结
通过上面的详细说明,相信对 MySQL 乱码问题已经有一个很好的了解了。那来回顾下本篇的内容。本篇主要列列举了 MySQL 乱码可能出现的场景,并对应给出详细的处理方法以及相关建议,希望以后大家永远不会出现乱码问题。


上一篇: 技术分享 | 是谁删了表?

下一篇: 技术分享 | 企业版监控工具 MEM 初探

咨询客服 在线咨询
400-820-6580 免费电话