专栏首页后台开发随笔MySQL 编码和解码
原创

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 删除。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • DFS中的奇偶剪枝学习笔记

    奇偶剪枝学习笔记 描述 现假设起点为(sx,sy),终点为(ex,ey),给定t步恰好走到终点, s | ...

    Angel_Kitty
  • 浅谈乱码原因及解决方案

    其实作为程序猿来讲,中国的程序猿遇到的问题可能会比国外的程序猿遇到的问题多很多。 一个原因是因为各种标准的制定、各种IDE的编写,都是由老外来完成的,制定出...

    roobtyan
  • 绝对不能错过的7篇MySQL必读文章

    MySQL专题那些事儿 马哥Linux运维公众号持续推出有质感的技术文章,志在成为大家手中的运维技术百宝箱,为了方便大家温习,小编特回顾了近一段时间比较受欢迎的...

    小小科
  • 基础设施服务k8s快速部署之HA篇

    实战部署 Kubernetes,助力应用开发人员升级成为 DevOps。成就你全栈工程师之路,让你的薪资倍增。

    程序猿Damon
  • 10分钟让你明白MySQL是如何利用索引的

    一、前言 在MySQL中进行SQL优化的时候,经常会在一些情况下,对MySQL能否利用索引有一些迷惑。 譬如: MySQL 在遇到范围查询条件的时候就停止匹配了...

    用户1263954
  • 大型网站架构体系的演变(下)

    在做扩展满足了基本的性能需求后,我们会逐渐关注“可用性”(也就是我们通常听别人吹牛时说的SLA、几个9)。如何保证真正“高可用”,也是个难题。

    哲洛不闹
  • springboot项目代码如何使用Disconf

    因为是springboot的项目,不同于springmvc,不需要写那么多的xml配置文件。

    算法之名
  • MySQL是如何利用索引的

    在MySQL中进行SQL优化的时候,经常会在一些情况下,对MySQL能否利用索引有一些迷惑。例如:

    用户1516716
  • AndroidStdio1_2

    Android库在结构上与Android应用模块相同。它可以提供构建应用所需的一切内容,包括源代码、资源文件和Android清单。不过,Android库将编译到...

    云深无际

扫码关注云+社区

领取腾讯云代金券