MySQL字符集中文乱码剖析

作者:龙述兵

问题描述

假设有三个表test_gbk,test_utf8,test_latin1,创建的时候字符集分别为gbk,utf8,latin1。表结构为

Field

Type

Null

Key

Default

Extra

name

varchar(512)

YES

NULL

"中"字的gbk十六进制表示为:0xd6 d0utf8 16进制表示为:0xe4 b8 ad

问题1

执行下列语句:

set names 'latin1'; 

insert into test_latin1 values( '中');   //此处'中'为gbk格式

select name from test_latin1;

结果是乱码,还是正常显示?

问题2

执行下列语句:

set names 'gbk'; 

insert into test_latin1 values( '中');  //此处'中'为gbk格式

select name from test_latin1;

结果是乱码,还是正常显示?

问题3

执行下列语句:

set names 'latin1'; 

insert into test_utf8 values( '中');  //此处'中'为gbk格式

select name from test_utf8;

结果是乱码,还是正常显示?

原理篇

字符集介绍

为了解释上述问题,首先要了解字符集为何物。字符集也叫字符编码,就是将字符集合一一映射成一个数。以下简单介绍一下几种字符集:

基础ASCII编码:

0x00-0x7F表示所有的大写和小写字母,数字0 到9、标点符号, 以及在美式英语中使用的特殊控制字符。

latin1编码:

单字节编码,编码范围是0x00-0xFF0x00-0x7F,和ASCII保持一致,0x80-0x9F之间是控制字符,0xA0-0xFF之间是文字符号。

gbk编码:

使用一字节和双字节编码,0x00–0x7F范围内是一位,和 ASCII 保持一致。双字节的第一字节范围是0x81-0xFE(不含0x800xFF)。

utf8编码:

使用一至四字节编码,0x00–0x7F范围内是一位,和 ASCII 保持一致。其它字符用二至四个字节变长表示。

字符集编码转换举例:

0xB1(latin-1) ->'±'-> 0xC2 B1 (utf8)

两个重要的点

  1. 0x00-0x7F区间,上述字符集是一致的,也就是说英文字符无需转码。
  2. 不同编码,字符集合不完全一样,存在某字符集的字符无法映射到另外一个字符集。

比如gbk编码中的中文字符,转成latin-1编码时,就找不到对应的二进制编码。MySQL做字符集转换的时候,gbk中文字符->latin-1,很多就转成'?'号(0x3f),这种大集合转成小集合,基本是不可逆的。

MySQL执行过程

对一个 MySQL 的执行过程,字符集转换,一般涉及到一下三个步骤:

  1. 收到请求,将请求数据从 character_set_client ->character_set_connection
  2. 内部操作,将数据从character_set_connection-> 表创建的字符集。
  3. 结果输出,将数据从表创建的字符集 -> character_set_results

当执行set names "charset"; 相当于把character_set_client, character_set_connection,character_set_results 统一设置为"charset"

终端显示字符集

此外如果你用securecrt终端来显示的话,如果不想乱码的话,appearance->character encoding也需要设置成正确的字符集。

问题详解

问题1

执行下列语句:

set names 'latin1'; 

insert into test_latin1 values( '中');   //此处'中'为gbk格式

select name from test_latin1;

结果是乱码,还是正常显示?

答:结果是正常显示。

执行流程如下:

  1. set names 'latin1';相当于把character_set_client, character_set_connection,character_set_results 统一设置为'latin1'
  2. Character_set_client告诉MySQL Server,传入的是一个latin1编码的,也就是单字节流,'中'这个输入,其实当作了0xD6 D0传入。
  3. 因为character_set_client -> character_set_connection-> table charset -> character_set_resultslatin1 ->latin1 -> latin1 -> latin1, 编码完全一致,数据没有做任何转换,所以输入是0xD6 D0,最后的输出也还原为0xD6 D0。
  4. 如果你的securecrt的显示字符集设置为gbk,那么最后的输出0xD6 D0就会显示成'中'。

问题2

执行下列语句:

set names 'gbk'; 

insert into test_latin1 values( '中');  //此处'中'为gbk格式

select name from test_latin1;

结果是乱码,还是正常显示?

答:结果是乱码。

执行流程如下:

  1. set names 'gbk';相当于把character_set_client, character_set_connection,character_set_results统一设置为'gbk'
  2. Character_set_client告诉MySQL Server,传入的是一个 gbk 编码的,'中'这个输入,当作了0xD6 D0传入。
  3. 因为character_set_client -> character_set_connection -> table charset-> character_set_resultsgbk-> gbk-> latin1 -> gbk, 其中gbk-> latin1的时候,因为'中'这个字符在latin1字符集里找不到,就会转换成'?'号(0x3F),然后latin1->gbk,'?'号在gbk字符集里面也是0x3F,最后输出就是0x3F,即'?'号。

问题3

执行下列语句:

set names 'latin1'; 

insert into test_utf8 values( '中');  //此处'中'为gbk格式

select name from test_utf8;

结果是乱码,还是正常显示?

答:正常显示。

执行流程如下:

  1. set names 'latin1';相当于把character_set_client, character_set_connection,character_set_results 统一设置为'latin1'
  2. Character_set_client告诉MySQL Server,传入的是一个latin1编码的,'中'这个输入,当作了0xD6 D0传入。
  3. 因为character_set_client -> character_set_connection -> table charset -> character_set_resultslatin1-> latin1-> utf8 -> latin1, 其中latin1-> utf8的时候,输入'中' (0xD6 D0)会当作两个字符进行utf8转换,转换为0xC3 96 C3 90,然后utf8->latin1的时候,会把0xC3 96转换成0xD6, 0xC3 90转成0x D0,最后输出0xD6 D0。负负得正,之所以数据没有失真的原因是因为小集合往大集合转,再转回来,操作可逆。
  4. 如果你的securecrt的显示字符集设置为gbk,那么最后的输出0xD6 D0就会显示成'中'。

终极解决方案

从上面的问题执行流程来看,有没有终极解决方案呢?其实很简单,表创建的字符集和set names都设置成同一个字符集,就基本可以满足输入数据不会在转换过程中失真,也就是说输入是什么,输出就是什么。建议有中文的都设置成utf8字符集,一劳永逸。

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

编辑于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏前端迷

很实用的前端开发规范

1.标准变量命名使用驼峰式命名 eg. let thisIsMyName; 2.常量全部大写,并使用下划线连接 eg. const MAX_COUNT = 10...

652
来自专栏程序员的知识天地

Python基础为重,成就月薪过万

傻瓜式,傻瓜式的你可以直接点开进行下载,但是智能下载这版本,有的人愿意下载别的版本所以就要用到另外的方法

492
来自专栏Nian糕的私人厨房

Emmet 常用语法

Emmet 是一个能大幅度提高前端开发效率的一个工具,通过在编辑器中输入 HTML 或 CSS 的代码缩写,按 Tab 键即可拓展为完整的代码片段,本文主要...

703
来自专栏Python小屋

详解Python中的序列解包(2)

8个月前曾经发过一篇关于序列解包的文章,见详解Python序列解包,本文再稍作补充。 可以说,序列解包的本质就是把一个序列或可迭代对象中的元素同时赋值给多个变量...

3225
来自专栏ShaoYL

预处理指令--宏定义

2737
来自专栏WebDeveloper

跟我学习php字符串常用函数-下篇

1> mixed parse_url ( string $url [, int $component = -1 ] )

622
来自专栏aCloudDeveloper

C++primer笔记之关联容器

在这一章中,有以下的几点收获: 1、pair类型的使用相当频繁,如果需要定义多个相同的pair类型对象,可考虑利用typedef简化其声明: typedef p...

1879
来自专栏前端小叙

js操作DOM在父元素中的结尾添加子节点注意

所以js是不能直接传入字符串的,但是jquery的append可以直接传入html字符串。

834
来自专栏企鹅号快讯

看完这篇文章我知道至少85%的人是没有入门Python的!花两周整理

以前刚学编程的时候就对Python略有耳闻,不过学校只有C,C++,Java,C#。和PHP有句"PHP是最好的语言" 这种家喻户晓的骚话一样,Python也有...

2317
来自专栏决胜机器学习

《Redis设计与实现》读书笔记(三十三) ——Redis排序命令sort的实现

《Redis设计与实现》读书笔记(三十三) ——Redis排序命令sort的实现 (原创内容,转载请注明来源,谢谢) 一、基本功能 redis的sort命令,可...

3555

扫码关注云+社区