MySQL 编码和解码

背景:目前正在进行业务重构,需要对使用MySQL的业务库表进行重新设计,在迁移时,遇到了中文字符乱码问题(源库表的默认编码是LATIN1,新库表的默认编码为UTF8),故重新学习了下MySQL编码和解码相关知识,并整理了在遭遇乱码时的一些常用技巧。(本文发布于云+社区:https://cloud.tencent.com/developer/article/1370123)

字符集

一个字符集包含三个关键元素:字库表(character repertoire)、编码字符集(coded character set)、字符编码(character encoding form)。

字库表:所有可读或者可显示字符的数据库,字库表决定了整个字符集能够展示的所有字符的范围。

编码字符集:即用一个编码值code point来表示一个字符在字库中的位置。

字符编码:将编码字符集和实际存储数值之间的转换关系。

常见的字符集包括以下几种:

  • ASCII(American StandardCode for Information Interchange,美国信息互换标准编码)是基于罗马字母表的一套电脑编码系统。7位(bits)表示一个字符,共128字符,字符值从0到127,其中32到126是可打印字符。
  • ASCII扩展字符集是从ASCII字符集扩充出来的,扩充后的符号增加了表格符号、计算符号、希腊字母和特殊的拉丁符号。
  • GB2312又称为GB2312-80字符集,全称为《信息交换用汉字编码字符集·基本集》,是中国国家标准的简体中文字符集。它所收录的汉字已经覆盖99.75%的使用频率,基本满足了汉字的计算机处理需要。在中国大陆和新加坡获广泛使用。
  • BIG5又称大五码或五大码,1984年由中国台湾财团法人信息工业策进会和五家软件公司宏碁 (Acer)、神通 (MiTAC)、佳佳、零壹 (Zero One)、大众 (FIC)创立,故称大五码。
  • GBK编码字符集,是在GB2312-80标准基础上的内码扩展规范,使用了双字节编码方案,其编码范围从8140至FEFE(剔除xx7F),共23940个码位,共收录了21003个汉字,完全兼容GB2312-80标准,支持国际标准ISO/IEC10646-1和国家标准GB13000-1中的全部中日韩汉字,并包含了BIG5编码中的所有汉字。
  • GB 18030全称是GB18030-2000《信息交换用汉字编码字符集基本集的扩充》,是我国政府于2000年3月17日发布的新的汉字编码国家标准,2001年8月31日后在中国市场上发布的软件必须符合本标准。
  • Unicode字符集(UniversalMultiple-Octet Coded Character Set,通用多八位编码字符集),一种在计算机上使用的字符编码,它为每种语言中的每个字符设定了统一并且唯一的二进制编码,以满足跨语言、跨平台进行文本转换、处理的要求。

UTF-8是 Unicode字符集的一种字符编码。Unicode的编号从0000开始一直到10FFFF共分为16个Plane,每个Plane中有65536个字符。而UTF-8则只实现了第一个Plane,可见UTF-8虽然是一个当今接受度最广的字符集编码,但是它并没有涵盖整个Unicode的字库,这也造成了它在某些场景下对于特殊字符的处理困难。

MySQL字符集编码简介

MySQL内部支持多种字符集,而字符集和编码可以等同。同一时候,MySQL中不同层次有不同的字符集编码格式,主要有四个层次:server,数据库,表和列。字符集编码不仅影响数据存储,还影响client程序和数据库之间的交互。在mysql中输入命令

SHOW SESSION VARIABLES LIKE '%character%'

能够看到例如以下一些关于字符集的参数:

  • character_set_client:server解析客户端sql语句的字符集。
  • character_set_results:server返回给客户端的查询结果或者错误提示的字符集编码。
  • character_set_system:这是mysql server用来存储元数据的编码,通常就是utf8,不要去改动它。
  • character_sets_dir:这是mysql字符集编码存储文件夹。
  • character_set_connection:字符串字面值(literal strings)的字符集。
  • character_set_server:服务器默认字符集编码,假设创建数据库的时候没有指定编码,则采用character_set_server指定编码。
  • character_set_database:默认数据库的字符集编码。假设没有默认数据库,则该变量值与character_set_server同样。事实上这个值代表的就是你当前数据库的编码而已,比方使用"use test",而test数据库的编码为latin1的话,这个值就是latin1。而你切换的时候"use test2",则character_set_database的值就是数据库test2的编码。
  • character_set_filesystem:这是文件系统字符集编码,主要用于解析文件名称的字符串字面值,如LOAD DATA INFILE和SELECT ...INTO OUTFILE等语句以及LOAD_FILE()函数。在打开文件之前,文件名称会从character_set_client转换为character_set_filesystem指定的编码。默认值为binary,也就是说不会进行转换。

还有其他相关变量collation_connectioncollation_databasecollation_server,用来描述字符序;

MySQL中的字符集转换过程

1、MySQL Client根据字符编码转换成二进制流,并传输到MySQL Server; 2、MySQL Server收到请求时将请求数据data从character_set_client转换为character_set_connection; 3、进行内部操作前将请求数据从character_set_connection转换为内部操作字符集,其确定方法如下:

  • 使用每个数据字段的CHARACTER SET设定值;
  • 若上述值不存在,则使用对应数据表的DEFAULT CHARACTER SET设定值(MySQL扩展,非SQL标准);
  • 若上述值不存在,则使用对应数据库的DEFAULT CHARACTER SET设定值;
  • 若上述值不存在,则使用character_set_server设定值。

4、引擎层读写存储文件,涉及内部操作字符集与二进制流之间的相互转换;

5、将操作结果从内部操作字符集转换为character_set_results; 6、MySQL Client接收到数据后,根据本地配置的字符编码展示查询结果;

MySQL编码解码过程

Client to Server的编解码:MySQL需要对传来的二进制流做语法和词法解析。如果不做编码解析和校验,我们甚至没法知道传来的一串二进制流是insert还是update。

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


introducer修饰的文本字符串在请求过程中不经过多余的转码,直接转换为内部字符集处理:

– 格式为:[_charset] ‘string’ [COLLATE collation]

– 例如:

SELECT _latin1 ‘string’;

SELECT _utf8 ‘你好’ COLLATE utf8_general_ci;

MySQL中的数据乱码

存入和取出时对应环节的编码不一致会造成乱码。

比如向默认字符集为utf8的数据表插入utf8编码的数据前没有设置连接字符集,查询时设置连接字符集为utf8;

  • 插入时根据MySQL服务器的默认设置,character_set_client、character_set_connection和character_set_results均为latin1;
  • 插入操作的数据将经过latin1==>latin1==>utf8的字符集转换过程,这一过程中每个插入的汉字都会从原始的3个字节变成6个字节保存;
  • 查询时的结果将经过utf8==>utf8的字符集转换过程,将保存的6个字节原封不动返回,产生乱码;

单个流程中编码不一致,且差异的两个字符集之间无法进行无损编码转换,也会出现乱码。

比如shell是UTF8编码,MySQL的character_set_client配置成了GBK,而表结构却又是charset=utf8,那么毫无疑问的一定会出现乱码。

客户端(web或shell)的字符编码和最终表的字符编码格式不同,但是只要保证存和取两次的字符集编码一致,且能进行无损编码转换时,此时不会产生乱码的现象。

MySQL乱码示例

在编码为zh_CN.UTF-8的终端下,按照以下方式创建一个table:

CREATE TABLE `test` (
  `set_names` varchar(10) COLLATE latin1_general_ci NOT NULL,
  `gbk` varchar(10) CHARACTER SET gbk NOT NULL,
  `utf8` varchar(10) CHARACTER SET utf8 NOT NULL,
  `latin1` varchar(10) COLLATE latin1_general_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

再按照以下方式插入3条数据

SET NAMES gbk;
INSERT `test` VALUES('gbk','中文','中文','中文');

SET NAMES utf8;
INSERT `test` VALUES('utf8','中文','中文','中文');

SET NAMES latin1;
INSERT `test` VALUES('latin1','中文','中文','中文');

执行结果如下:

6 queries executed, 6 success, 0 errors, 3 warnings

查询:set names gbk

共 0 行受到影响

执行耗时   : 0.038 sec
传送时间   : 0 sec
总耗时      : 0.039 sec
--------------------------------------------------

查询:insert `test` values('gbk','中文','中文','中文')

共 1 行受到影响, 2 个警告

执行耗时   : 0.039 sec
传送时间   : 0 sec
总耗时      : 0.039 sec

Warning Code : 1366
Incorrect string value: '\xAD\xE6\x96\x87' for column 'utf8' at row 1

Warning Code : 1366
Incorrect string value: '\xE4\xB8\xAD\xE6\x96\x87' for column 'latin1' at row 1
--------------------------------------------------

查询:SET NAMES utf8

共 0 行受到影响

执行耗时   : 0.038 sec
传送时间   : 0.001 sec
总耗时      : 0.039 sec
--------------------------------------------------

查询:INSERT `test` VALUES('utf8','中文','中文','中文')

共 1 行受到影响, 1 个警告

执行耗时   : 0.038 sec
传送时间   : 0 sec
总耗时      : 0.039 sec

Warning Code : 1366
Incorrect string value: '\xE4\xB8\xAD\xE6\x96\x87' for column 'latin1' at row 1
--------------------------------------------------

查询:SET NAMES latin1

共 0 行受到影响

执行耗时   : 0.038 sec
传送时间   : 0 sec
总耗时      : 0.039 sec
--------------------------------------------------

查询:INSERT `test` VALUES('latin1','中文','中文','中文')

共 1 行受到影响, 1 个警告

执行耗时   : 0.038 sec
传送时间   : 0 sec
总耗时      : 0.039 sec

Warning Code : 1366
Incorrect string value: '\xE4\xB8\xAD\xE6\x96\x87' for column 'gbk' at row 1

从执行结果可以看出,插入的数据出现了4个关于字符值的警告,这里分析下编码转换过程:

  1. MySQL客户端发送的插入语句含有"中文"字符串,其编码是跟我们的环境相关的,由于终端编码为UTF-8,因此"中文"的字节表示为"\xE4\xB8\xAD\xE6\x96\x87"。
  2. MySQL Server端收到数据后,会按照character_set_client设置的编码转化为character_set_connection指定的编码,这里2个编码相同,实际不会发生转换(当插入的数据前面有latin1或者utf8introducer标记指明了字面值字符的编码时,此时也不会转换)。
  3. 数据在存储到数据库之前,实际要插入的三个字段的编码都是原始编码s="\xE4\xB8\xAD\xE6\x96\x87",为将数据存储到文件,需要由character_set_connection编码转换为数据表字段指定的编码,即s.decode(character_set_connection).encode(字段指定编码)。此时,当原始数据不能按照character_set_connection指定的字符集解码,或者解码后的字符是不存在于数据表字段指定的字符集中,就会出现上文告警,并使用用错误标识替代,即0x3F

此时通过设置character_set_results分别为gbk、utf8、latin1时,查看下test表中的数据:

MySQL [CCDB4_SRC]> set names gbk;
MySQL [CCDB4_SRC]> select * from test;
+-----------+---------+---------+---------+
| set_names | gbk     | utf8    | latin1  |
+-----------+---------+---------+---------+
| gbk       | 中文    | 䶿       | ???     |
| utf8      | אτ        | אτ        | ??      |
| latin1    | ????¨C?  | ????¨C?  | ????¨C?  |
+-----------+---------+---------+---------+


MySQL [CCDB4_SRC]> set names utf8;
MySQL [CCDB4_SRC]> select * from test;
+-----------+----------+----------------+----------------+
| set_names | gbk      | utf8           | latin1         |
+-----------+----------+----------------+----------------+
| gbk       | 涓?枃    | 涓?枃          | ???            |
| utf8      | 中文     | 中文           | ??             |
| latin1    | ????–?   | 中文         | 中文         |
+-----------+----------+----------------+----------------+


MySQL [CCDB4_SRC]> set names latin1;
MySQL [CCDB4_SRC]> select * from test;
+-----------+--------+--------+--------+
| set_names | gbk    | utf8   | latin1 |
+-----------+--------+--------+--------+
| gbk       | ???    | ???    | ???    |
| utf8      | ??     | ??     | ??     |
| latin1    | ?????  | 中文   | 中文   |
+-----------+--------+--------+--------+

通过设置不同的character_set_results,结果集展示的乱码情况并不相同,此时可以通过SQL查询test表实际存储内容:

MySQL [CCDB4_SRC]> SELECT set_names,HEX(gbk),HEX(utf8),HEX(latin1) FROM test;
+-----------+----------------+------------------------------+--------------+
| set_names | hex(gbk)       | hex(utf8)                    | hex(latin1)  |
+-----------+----------------+------------------------------+--------------+
| gbk       | E4B8ADE69687   | E6B6933FE69E83               | 3F3F3F       |
| utf8      | D6D0CEC4       | E4B8ADE69687                 | 3F3F         |
| latin1    | 3F3F3F3FA8433F | C3A4C2B8C2ADC3A6E28093E280A1 | E4B8ADE69687 |
+-----------+----------------+------------------------------+--------------+

可以看出:当character_set_clientcharacter_set_connectioncharacter_set_results与库表字段字符集定义相同,插入的数据时,Server底层存储与MySQL客户端侧的字节表示一致,此时查询肯定不会产生乱码。那如何在固定character_set_results值的情况,尽量多的恢复test表中的数据呢?

假设character_set_resultsutf8,可以通过如下编码转换:

MySQL [CCDB4_SRC]> SELECT 'gbk',BINARY CONVERT(gbk USING gbk) gbk,BINARY CONVERT(utf8 USING gbk) utf8,BINARY CONVERT(latin1 USING gbk) latin1 FROM test WHERE set_names='gbk'
    -> UNION
    -> SELECT 'utf8',BINARY CONVERT(gbk USING utf8) gbk,BINARY CONVERT(utf8 USING utf8) utf8,BINARY CONVERT(latin1 USING utf8) latin1 FROM test WHERE set_names='utf8'
    -> UNION
    -> SELECT 'latin1',BINARY CONVERT(gbk USING latin1) gbk,BINARY CONVERT(utf8 USING latin1) utf8,BINARY CONVERT(latin1 USING latin1) latin1 FROM test WHERE set_names='latin1'
    -> ;
+--------+--------+--------+--------+
| gbk    | gbk    | utf8   | latin1 |
+--------+--------+--------+--------+
| gbk    | 中文   | 䶿      | ???    |
| utf8   | 中文   | 中文   | ??     |
| latin1 | ?????  | 中文   | 中文   |
+--------+--------+--------+--------+

修改字符集

当库表并未出现乱码而需要修改库表字符集时,可以通过ALTER TABLE … CONVERT TO CHARACTER SET …方式实现,而当查询数据出现乱码问题,可通过以下方法修复已经损坏的数据:

Dump & Reload

通过错进错出的方法,导出到文件

用正确的字符集修改新表

将之前导出的文件导回到新表中

Convert to Binary & Convert Back

ALTER TABLE … MODIFY COLUMN … VARBINARY(…);

ALTER TABLE … MODIFY COLUMN … varchar(…) character set …;

注意事项

  • 建立数据库/表和进行数据库操作时尽量显式指出使用的字符集,且character_set_clientcharacter_set_connectioncharacter_set_results与库表字段字符集定义相同,而不是依赖于MySQL的默认设置,否则MySQL升级时可能带来很大困扰;
  • 数据库和连接字符集都使用latin1时虽然大部分情况下都可以解决乱码问题,但缺点是无法以字符为单位来进行SQL操作,一般情况下将数据库和连接字符集都置为utf8是较好的选择;
  • 使用MySQL C API时,初始化数据库句柄后马上用mysql_options设定MYSQL_SET_CHARSET_NAME属性为utf8,这样就不用显式地用 SET NAMES语句指定连接字符集,且用mysql_ping重连断开的长连接时也会把连接字符集重置为utf8
  • 对于MySQL PHP API,一般页面级的PHP程序总运行时间较短,在连接到数据库以后显式用SET NAMES语句设置一次连接字符集即可;
  • 但当使用长连接时,请注意保持连接通畅并在断开重连后用SET NAMES语句显式重置连接字符集。
  • my.cnf中的default_character_set设置只影响mysql命令连接服务器时的连接字符集,不会对使用libmysqlclient库的应用程序产生任何作用。
  • 对字段进行的SQL函数操作通常都是以内部操作字符集进行的,不受连接字符集设置的影响。
  • SQL语句中的裸字符串会受到连接字符集或introducer设置的影响,对于比较之类的操作可能产生完全不同的结果,需要小心。

参考文章

  1. http://blog.jobbole.com/84903/
  2. http://cenalulu.github.io/mysql/mysql-mojibake/
  3. https://www.cnblogs.com/bhlsheji/p/5176746.html
  4. https://blog.csdn.net/fdipzone/article/details/18180325
  5. https://blog.csdn.net/baiwfg2/article/details/38701495

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

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

编辑于

后台开发随笔

1 篇文章1 人订阅

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏高性能分布式系统设计

避免Goroutine频繁创建来提高性能

频繁的创建和销毁goroutine带来的开销不仅仅是goroutine创建和销毁本身. 更大的开销是由于goroutine的Stack一开始只有2KB, 而大部...

40960
来自专栏自由而无用的灵魂的碎碎念

系统上是否可以只共存多个版本visual c++可再发行包最新版的验证结果

最近在添加与删除程序中发现,系统中Microsoft Visual C++ Redistributable Package存在很多版本的,从2005、2008...

18620
来自专栏IT可乐

深入理解计算机系统(3.1)------汇编语言和机器语言

  《深入理解计算机系统》第三章——程序的机器级表示。作者首先讲解了汇编代码和机器代码的关系,阐述了汇编承上启下的作用;接着从机器语言IA32着手,分别讲述了如...

42690
来自专栏程序员互动联盟

【答疑释惑第四十三讲】到底什么是可执行文件?

疑惑一 到底什么是可执行文件? 可执行文件由一系列段(section)组成,text段用于存储代码,data段存储变量等数据,bss段用于存储未初始化的数据,比...

33660
来自专栏JarvanMo的IT专栏

[译]Flutter响应式编程:Streams和BLoC

本文主要介绍Streams,Bloc和Reactive Programming(响应式编程)的概念。 理论和实践范例。

88190
来自专栏FreeBuf

深入分析IE地址栏内容泄露漏洞

? 前言 在本文中,我们探讨的对象是IE浏览器,尽管该浏览器略显老态,但是其用户还是很多的,所以不容忽视。我最近对MSRC感到很欣喜,因为他们正在将工作重心移...

255100
来自专栏码神联盟

碎片化 | 第四阶段-31-Struts2参数传出传入值-视频

如清晰度低,可转PC网页观看高清版本: http://v.qq.com/x/page/g0566v55efc.html Hello Struts2 1:St...

36560
来自专栏java一日一条

Java Fork/Join 框架

响应式编程(Reactive Programming / RP)作为一种范式在整个业界正在逐步受到认可和落地,是对过往系统的业务需求理解梳理之后对系统技术设计/...

31610
来自专栏更流畅、简洁的软件开发方式

面向对象最重要的是“抽象”,三层最重要的也是“抽象”,没有抽象就不是真正的面向对象、三层。

  只用class的,那叫做“基于对象”,比如当初的vb6.0;只是分了三个项目,把以前写在一起的代码分成了三份,所谓的业务逻辑层就是一个传声筒,这一类自称三层...

30160
来自专栏Golang语言社区

论Go语言中goroutine的使用

go中的goroutine是go语言在语言级别支持并发的一种特性。初接触go的时候对go的goroutine的欢喜至极,实现并发简便到简直bt的地步。但是在项目...

52480

扫码关注云+社区

领取腾讯云代金券