第09期:有关 MySQL 字符集的乱码问题「终于解决」

第09期:有关 MySQL 字符集的乱码问题「终于解决」相信大家通过前几篇文章,已经了解了 MySQL 字符集使用相关注意事项。那么数据乱码问题在这儿显得就非常简单了,或许说可能不会出现这样的问题。 数据之所以会乱码,在 MySQL 里无非有以下几类情况…

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

第09期:有关 MySQL 字符集的乱码问题「终于解决」

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

数据之所以会乱码,在 MySQL 里无非有以下几类情况:

一、转码失败

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

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

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

比如下面简单一条语句:

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

代码100分

1.变量 @a 的字符编码是由参数 CHARACTER_SET_CLIENT 决定的,假设此时编码为 A,也就是变量 @a 的编码。 2.写入语句在发送到 MySQL 服务端之前的编码由 CHARACTER_SET_CONNECTION 决定,假设此时编码为 B。 3.经过 MySQL 一系列词法,语法解析等处理后,写入到表 t1,表 t1 的编码为 C。

那这里编码 A、编码 B、编码 C 如果不兼容,写入的数据就直接乱码。

来看下数据写入过程乱码情况:

代码100分-- 我的终端字符集是 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 强制避免不兼容的编码插入数据。

代码100分-- 设置 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)

总结

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


关于 MySQL 的技术内容,你们还有什么想知道的吗?赶紧留言告诉小编吧! 第09期:有关 MySQL 字符集的乱码问题「终于解决」

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
转载请注明出处: https://daima100.com/7178.html

(0)
上一篇 2023-03-25
下一篇 2023-03-25

相关推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注