MySQL运维实战(5.3) MySQL数据乱码的一些情况

俊达12个月前技术文章372

表数据乱码

当数据的真实编码和相关参数(常见的包括character_set_client, character_set_result, 字段的编码,终端的编码)不一致时,会产生乱码。


测试1 - 表中的数据正常,但是character_set_result设置和终端的实际字符集不匹配

终端的字符编码为utf8


term-utf8.png

mysql> show variables like '%char%';
+--------------------------+------------------------------------------------------+
| Variable_name            | Value                                                |
+--------------------------+------------------------------------------------------+
| character_set_client     | utf8mb3                                              |
| character_set_connection | utf8mb3                                              |
| character_set_database   | utf8mb4                                              |
| character_set_filesystem | binary                                               |
| character_set_results    | utf8mb3                                              |
| character_set_server     | latin1                                               |
| character_set_system     | utf8mb3                                              |
| character_sets_dir       | /usr/local/Cellar/mysql/8.0.31/share/mysql/charsets/ |
+--------------------------+------------------------------------------------------+
8 rows in set (0.00 sec)

mysql> create table test_charset(a varchar(100)) default charset utf8;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> insert into test_charset values('列列列列列AAA');
Query OK, 1 row affected (0.01 sec)

mysql> select * from test_charset;
+--------------------+
| a                  |
+--------------------+
| 列列列列列AAA      |
+--------------------+
1 row in set (0.00 sec)


将character_set_results设置为gbk,则无法正常显示返回的数据:

mysql> set character_set_results=gbk;
Query OK, 0 rows affected (0.00 sec)

mysql> select *,hex(a) from test_charset;
+---------------+--------------------------------------+
| a             | hex(a)                               |
+---------------+--------------------------------------+
| ����������AAA           | E58897E58897E58897E58897E58897414141 |
+---------------+--------------------------------------+



在另外一个GBK编码的终端下:

term-gbk.png


mysql> set character_set_results=gbk;
Query OK, 0 rows affected (0.00 sec)

mysql> select *, hex(a) from test_charset;
+---------------+--------------------------------------+
| a             | hex(a)                               |
+---------------+--------------------------------------+
| 列列列列列AAA | E58897E58897E58897E58897E58897414141 |
+---------------+--------------------------------------+
1 row in set (0.00 sec)



mysql> set character_set_results=utf8;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select *, hex(a) from test_charset;
+--------------------+--------------------------------------+
| a                  | hex(a)                               |
+--------------------+--------------------------------------+
| 鍒楀垪鍒楀垪鍒桝AA | E58897E58897E58897E58897E58897414141 |
+--------------------+--------------------------------------+
1 row in set (0.01 sec)


上面这几个例子中,数据库中存储的数据没有问题(使用hex函数查看真实的数据,和字符的字符集(UTF8)编码一致),但是因为客户端的字符编码设置不对,导致无法正常查看字符。



测试2 - character_set_client和终端的字符集不匹配


2.1 utf8终端,character_set_client设置为gbk,写入数据

create table test_charset2(a varchar(100), b varchar(100)) default charset utf8;

-- utf8终端
mysql> set character_set_client='gbk';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test_charset2 values('term: utf8, charset client: gbk', '列列列列列AAA');
Query OK, 1 row affected (0.00 sec)


mysql> select *, hex(b) from test_charset2;
+---------------------------------+----------------------------+------------------------------------------------------+
| a                               | b                          | hex(b)                                               |
+---------------------------------+----------------------------+------------------------------------------------------+
| term: utf8, charset client: gbk | 鍒楀垪鍒楀垪鍒桝AA         | E98D92E6A580E59EAAE98D92E6A580E59EAAE98D92E6A19D4141 |
+---------------------------------+----------------------------+------------------------------------------------------+
1 row in set (0.00 sec)


汉字“列”的utf8编码应该是E58897,但是表中实际存储的数据不对。

>>> u"列".encode('utf8')
'\xe5\x88\x97'


测试2.1中,由于character_set_client和实际的数据的字符集编码不匹配,导致表里存储的数据本身就是有问题的(汉字“列”的utf8编码为e58897)。

使用python模拟这个过程:

-- 本来是utf8编码的数据,使用gbk编码来解码,然后再以utf8(表的字符集)方式编码
>>> u"列列列列列AAA".encode('utf8').decode('gbk').encode('utf8')
'\xe9\x8d\x92\xe6\xa5\x80\xe5\x9e\xaa\xe9\x8d\x92\xe6\xa5\x80\xe5\x9e\xaa\xe9\x8d\x92\xe6\xa1\x9dAA'

>>> print u"列列列列列AAA".encode('utf8').decode('gbk').encode('utf8')
鍒楀垪鍒楀垪鍒桝AA


2.2 gbk终端,character_set_client设置为utf8,写入数据

-- gbk终端

mysql> set character_set_client='utf8';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into test_charset2 values('term: gbk, charset client: utf8', '列列列列列AAA');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------+
| Level   | Code | Message                                                                 |
+---------+------+-------------------------------------------------------------------------+
| Warning | 1300 | Cannot convert string '\xC1\xD0\xC1\xD0\xC1\xD0...' from utf8mb3 to gbk |
+---------+------+-------------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> select *, hex(b) from test_charset2;
+---------------------------------+--------------------+------------------------------------------------------+
| a                               | b                  | hex(b)                                               |
+---------------------------------+--------------------+------------------------------------------------------+
| term: utf8, charset client: gbk | 鍒楀垪鍒楀垪鍒桝AA | E98D92E6A580E59EAAE98D92E6A580E59EAAE98D92E6A19D4141 |
| term: gbk, charset client: utf8 | ??????????AAA      | 3F3F3F3F3F3F3F3F3F3F414141                           |
+---------------------------------+--------------------+------------------------------------------------------+
2 rows in set (0.00 sec)


原因解析

本来时gbk方式编码的数据,使用utf8编码无法解码,使用替换字符(3F) 替换无法解码的数据。


2.3 utf8终端,character_set_results设置为gbk

如果我们将错就错,在utf8终端上,将character_set_results设置为gbk。

查询返回看起来正常的数据:

-- utf8编码的终端

mysql> set character_set_results=gbk;
Query OK, 0 rows affected (0.00 sec)

mysql> select *, hex(b) from test_charset2;
+---------------------------------+--------------------+------------------------------------------------------+
| a                               | b                  | hex(b)                                               |
+---------------------------------+--------------------+------------------------------------------------------+
| term: utf8, charset client: gbk | 列列列列列AAA      | E98D92E6A580E59EAAE98D92E6A580E59EAAE98D92E6A19D4141 |
| term: gbk, charset client: utf8 | ??????????AAA      | 3F3F3F3F3F3F3F3F3F3F414141                           |
+---------------------------------+--------------------+------------------------------------------------------+
2 rows in set (0.00 sec)


原因解析
数据库中实际存储的数据:
>>> u"列列列列列AAA".encode('utf8').decode('gbk').encode('utf8')
'\xe9\x8d\x92\xe6\xa5\x80\xe5\x9e\xaa\xe9\x8d\x92\xe6\xa5\x80\xe5\x9e\xaa\xe9\x8d\x92\xe6\xa1\x9dAA'

-- character_set_results 设置为gbk,则mysql以gbk编码返回数据,
>>> u"列列列列列AAA".encode('utf8').decode('gbk').encode('gbk')
'\xe5\x88\x97\xe5\x88\x97\xe5\x88\x97\xe5\x88\x97\xe5\x88\x97AAA'

-- 由于终端字符集为utf8,以utf8编码解释返回的数据
>>> u"列列列列列AAA".encode('utf8').decode('gbk').encode('gbk').decode('utf8')
u'\u5217\u5217\u5217\u5217\u5217AAA'

u5217就是汉字“列“的unicode编码


2.4 gbk终端,character_set_results设置为utf8

在gbk终端上,将character_set_results设置为utf8:


-- gbk编码的终端

mysql> set character_set_results=utf8;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select *, hex(b) from test_charset2;
+---------------------------------+----------------------------+------------------------------------------------------+
| a                               | b                          | hex(b)                                               |
+---------------------------------+----------------------------+------------------------------------------------------+
| term: utf8, charset client: gbk | 閸掓鍨崚妤鍨崚妗滱A | E98D92E6A580E59EAAE98D92E6A580E59EAAE98D92E6A19D4141 |
| term: gbk, charset client: utf8 | ??????????AAA              | 3F3F3F3F3F3F3F3F3F3F414141                           |
+---------------------------------+----------------------------+------------------------------------------------------+
2 rows in set (0.00 sec)


原因解析
数据库中实际存储的数据:
>>> u"列列列列列AAA".encode('utf8').decode('gbk').encode('utf8')
'\xe9\x8d\x92\xe6\xa5\x80\xe5\x9e\xaa\xe9\x8d\x92\xe6\xa5\x80\xe5\x9e\xaa\xe9\x8d\x92\xe6\xa1\x9dAA'

-- character_set_results 设置为utf8,则mysql以utf8编码返回数据,
>>> u"列列列列列AAA".encode('utf8').decode('gbk').encode('utf8')
'\xe9\x8d\x92\xe6\xa5\x80\xe5\x9e\xaa\xe9\x8d\x92\xe6\xa5\x80\xe5\x9e\xaa\xe9\x8d\x92\xe6\xa1\x9dAA'

-- 由于终端字符集为gbk,以gbk编码解释返回的数据。
>>> u"列列列列列AAA".encode('utf8').decode('gbk').encode('utf8').decode('gbk')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
UnicodeDecodeError: 'gbk' codec can't decode bytes in position 4-5: illegal multibyte sequence

-- 加上error='replace'
>>> u"列列列列列AAA".encode('utf8').decode('gbk').encode('utf8').decode('gbk', errors='replace')
u'\u95b8\u6393\ufffd\u9368\ufffd\u5d1a\u59a4\ufffd\u7059\u95b8\u6393\ufffdAA'

>>> print u"列列列列列AAA".encode('utf8').decode('gbk').encode('utf8').decode('gbk', errors='replace')
閸掓�鍨�崚妤�灙閸掓�AA


-- 使gb18030,
>>> u"列列列列列AAA".encode('utf8').decode('gbk').encode('utf8').decode('gb18030', errors='replace')
u'\u95b8\u6393\ue685\u9368\ue048\u5d1a\u59a4\ufffd\u7059\u95b8\u6393\ue522AA'

>>> print u"列列列列列AAA".encode('utf8').decode('gbk').encode('utf8').decode('gb18030', errors='replace')
閸掓鍨崚妤�灙閸掓AA

-- 上面的数据和mysql终端显示的数据(閸掓鍨崚妤鍨崚妗滱A )有一些差异。原因可能是他们对gbk无法解码的数据处理方式有差异。


总结

当客户端字符编码方式和MySQL参数character_set_client、character_set_results设置不匹配时,容易产生乱码。

有的时候,数据库存储的数据没有问题,但是客户端编码设置不对,导致看上去有乱码,这种情况下只需要将客户端编码设置正确,就可以了。

有的时候,存储到数据库的数据就不对了,甚至原来的数据无法编码,会被“?”等替换字符替换,这时可能就无法得到原先的数据了。



相关文章

MySQL优化器特性(三)表关联之BKA(Batched Key Access)优化

MySQL优化器特性(三)表关联之BKA(Batched Key Access)优化

单表range查询时,可以使用MRR优化,先对rowid进行排序,然后再回表查询数据。在表关联的时候,也可以使用类似的优化方法,先根据关联条件取出被关联表的rowid,将rowid缓存在join bu...

MySQL运维实战之备份和恢复(8.1)xtrabackup全量备份

xtrabackup是percona开源的mysql物理备份工具。xtrabackup 8.0支持mysql 8.0版本的备份和恢复。xtrabackup 2.4支持mysql 5.7及以下版本的备份...

MySQL运维实战(5.6) 字符集设置对mysqldump的影响

mysqldump不指定字符集不指定字符集时,默认使用了utf8。可能和环境有关系。mysqldump -uroot test test_load >&n...

MySQL运维实战(7.2) MySQL复制server_id相关问题

MySQL运维实战(7.2) MySQL复制server_id相关问题

主库server_id没有设置主库没有设置server_idGot fatal error 1236 from master when&nb...

MySQL运维实战(4.6) SQL_MODE之NO_BACKSLASH_ESCAPES

设置NO_BACKSLASH_ESCAPES后,反斜杠(\)不作为转义符。不设置NO_BACKSLASH_ESCAPES时,反斜杠(\)用来转义后一个字符mysql> create&n...

MySQL运维实战之备份和恢复(8.2)xtrabackup备份到云端(OSS)

xtrabackup工具中有一个xbcloud程序,可以将数据库直接备份到S3对象存储中,本地不落盘。这里介绍将数据库直接备份到OSS的一种方法。具体方法如下:1、准备OSS我们使用ossutil工具...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。