Linux.中国 - 开源社区

 找回密码
 骑士注册

QQ登录

微博登录


理解和解决 MySQL 乱码问题

2015-3-12 14:41    评论: 18 收藏: 2 分享: 41    

本文将详细介绍MySQL乱码的成因和具体的解决方案

在阅读本文之前,强烈建议对字符集编码概念还比较模糊的同学 阅读下博主之前对相关概念的一篇科普:十分钟搞清字符集和字符编码

MySQL出现乱码的原因

要了解为什么会出现乱码,我们就先要理解:从客户端发起请求,到MySQL存储数据,再到下次从表取回客户端的过程中,哪些环节会有编码/解码的行为。为了更好的解释这个过程,博主制作了两张流程图,分别对应存入和取出两个阶段。

存入MySQL经历的编码转换过程

mysqlflow

上图中有3次编码/解码的过程(红色箭头)。三个红色箭头分别对应:客户端编码,MySQL Server解码,Client编码向表编码的转换。其中Terminal可以是一个Bash,一个web页面又或者是一个APP。本文中我们假定Bash是我们的Terminal,即用户端的输入和展示界面。图中每一个框格对应的行为如下:

  • 在terminal中使用输入法输入
  • terminal根据字符编码转换成二进制流
  • 二进制流通过MySQL客户端传输到MySQL Server
  • Server通过character-set-client解码
  • 判断character-set-client和目标表的charset是否一致
  • 如果不一致则进行一次从client-charset到table-charset的一次字符编码转换
  • 将转换后的字符编码二进制流存入文件中

从MySQL表中取出数据经历的编码转换过程

mysqlflow
上图有3次编码/解码的过程(红色箭头)。上图中三个红色箭头分别对应:客户端解码展示,MySQL Server根据character-set-client编码,表编码向character-set-client编码的转换。

  • 从文件读出二进制数据流
  • 用表字符集编码进行解码
  • 将数据转换为character-set-client的编码
  • 使用character-set-client编码为二进制流
  • Server通过网络传输到远端client
  • client通过bash配置的字符编码展示查询结果

造成MySQL乱码的原因

1. 存入和取出时对应环节的编码不一致

这个会造成乱码是显而易见的。我们把存入阶段的三次编解码使用的字符集编号为C1,C2,C3(图一从左到右);取出时的三个字符集依次编号为C1’,C2’,C3’(从左到右)。那么存入的时候bash C1用的是UTF-8编码,取出的时候,C1'我们却使用了windows终端(默认是GBK编码),那么结果几乎一定是乱码。又或者存入MySQL的时候set names utf8(C2),而取出的时候却使用了set names gbk(C2'),那么结果也必然是乱码

2. 单个流程中三步的编码不一致

即上面任意一幅图中的同方向的三步中,只要两步或者两部以上的编码有不一致就有可能出现编解码错误。如果差异的两个字符集之间无法进行无损编码转换(下文会详细介绍),那么就一定会出现乱码。例如:我们的shell是UTF8编码,MySQL的character-set-client配置成了GBK,而表结构却又是charset=utf8,那么毫无疑问的一定会出现乱码。

这里我们就简单演示下这种情况:

master [localhost] {msandbox} (test) > create table charset_test_utf8 (id int primary key auto_increment, char_col varchar(50)) charset = utf8;
Query OK, 0 rows affected (0.04 sec)

master [localhost] {msandbox} (test) > set names gbk;
Query OK, 0 rows affected (0.00 sec)

master [localhost] {msandbox} (test) > insert into charset_test_utf8 (char_col) values ('中文');
Query OK, 1 row affected, 1 warning (0.01 sec)

master [localhost] {msandbox} (test) > show warnings;
+---------+------+---------------------------------------------------------------------------+
| Level   | Code | Message                                                                   |
+---------+------+---------------------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xAD\xE6\x96\x87' for column 'char_col' at row 1 |
+---------+------+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

master [localhost] {msandbox} (test) > select id,hex(char_col),char_col from charset_test_utf8;
+----+----------------+----------+
| id | hex(char_col)  | char_col |
+----+----------------+----------+
|  1 | E6B6933FE69E83 | �?��        |
+----+----------------+----------+
1 row in set (0.01 sec)

关于MySQL的编/解码

既然系统之间是按照二进制流进行传输的,那直接把这串二进制流直接存入表文件就好啦。为什么在存储之前还要进行两次编解码的操作呢?

  • Client to Server的编解码的原因是MySQL需要对传来的二进制流做语法和词法解析。如果不做编码解析和校验,我们甚至没法知道传来的一串二进制流是insert还是update。
  • File to Engine的编解码是为知道二进制流内的分词情况。举个简单的例子:我们想要从表里取出某个字段的前两个字符,执行了一句形如select left(col,2) from table的语句,存储引擎从文件读入该column的值是E4B8ADE69687。那么这个时候如果我们按照GBK把这个值分割成E4B8,ADE6,9687三个字,并那么返回客户端的值就应该是E4B8ADE6;如果按照UTF8分割成E4B8AD,E69687,那么就应该返回E4B8ADE69687两个字。可见,如果在从数据文件读入数据后,不进行编解码的话在存储引擎内部是无法进行字符级别的操作的。
123下一页
查看其它分页:

发表评论


最新评论

我也要发表评论

湖南刘凯威 2015-3-15 14:03  新浪微博网友评论
[作揖]
回复
果果酱0o 2015-3-14 23:03  新浪微博网友评论
@我的印象笔记
回复
lyhabc 2015-3-13 15:01
收藏了
9 回复
周明智 2015-3-13 11:03  新浪微博网友评论
乱码应该是初学者必踩的坑吧
回复
nihaoxiongfei 2015-3-13 07:33  新浪微博网友评论
mark
6 回复
glson 2015-3-13 03:03  新浪微博网友评论
//@Linux中国:[赞]//@_小噗: 全文精华,也跟我N年的经验一样:“只要做到‘三位一体’,即客户端,MySQL character-set-client,table charset三个字符集完全一致就可以保证一定不会有乱码出现了。”
回复
cannshui 2015-3-12 23:03  新浪微博网友评论
MA
回复
宋万伟_song 2015-3-12 22:33  新浪微博网友评论
收藏之
回复
尉海立 2015-3-12 21:33  新浪微博网友评论
乱码真的很恼火,读了MySql的参考文档才搞定
回复
戊辰人王路 2015-3-12 19:33  新浪微博网友评论
马克
回复
ZetaZ 2015-3-12 18:33  新浪微博网友评论
……
回复
HuangBowei 2015-3-12 18:03  新浪微博网友评论
Repost
回复
超凡临时工 2015-3-12 18:03  新浪微博网友评论
//@Linux中国:[赞]//@_小噗: 全文精华,也跟我N年的经验一样:“只要做到‘三位一体’,即客户端,MySQL character-set-client,table charset三个字符集完全一致就可以保证一定不会有乱码出现了。”
回复
约瑟夫-双子座 2015-3-12 16:33  新浪微博网友评论
//@Linux中国:[赞]//@_小噗: 全文精华,也跟我N年的经验一样:“只要做到‘三位一体’,即客户端,MySQL character-set-client,table charset三个字符集完全一致就可以保证一定不会有乱码出现了。”
回复
我心飛揚Since2010 2015-3-12 15:47  新浪微博网友评论
Repost
回复
做个有bigger的男子 2015-3-12 15:33  新浪微博网友评论
@一只会说话的猪仔
回复
Linux中国 2015-3-12 15:33  新浪微博网友评论
[赞]//@_小噗: 全文精华,也跟我N年的经验一样:“只要做到‘三位一体’,即客户端,MySQL character-set-client,table charset三个字符集完全一致就可以保证一定不会有乱码出现了。”
回复
_小噗 2015-3-12 15:03  新浪微博网友评论
全文精华,也跟我N年的经验一样:“只要做到‘三位一体’,即客户端,MySQL character-set-client,table charset三个字符集完全一致就可以保证一定不会有乱码出现了。”
回复

热点评论

lyhabc 2015-3-13 15:01
收藏了
9
nihaoxiongfei 2015-3-13 07:33
mark
6
返回顶部

分享到微信朋友圈

打开微信,点击底部的“发现”,
使用“扫一扫”将网页分享至朋友圈。