MySQL运维实战(5.3) MySQL数据乱码的一些情况
表数据乱码
当数据的真实编码和相关参数（常见的包括character_set_client, character_set_result, 字段的编码，终端的编码）不一致时，会产生乱码。
测试1 - 表中的数据正常，但是character_set_result设置和终端的实际字符集不匹配
终端的字符编码为utf8
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编码的终端下：
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')
鍒楀垪鍒楀垪鍒桝AA2.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设置不匹配时，容易产生乱码。
有的时候，数据库存储的数据没有问题，但是客户端编码设置不对，导致看上去有乱码，这种情况下只需要将客户端编码设置正确，就可以了。
有的时候，存储到数据库的数据就不对了，甚至原来的数据无法编码，会被“？”等替换字符替换，这时可能就无法得到原先的数据了。
