MySQL 编码和解码

原创

skylerxu

修改于 2019-01-01 22:51:22

5.6K0

修改于 2019-01-01 22:51:22

背景：目前正在进行业务重构，需要对使用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_connection、collation_database、collation_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接收到数据后，根据本地配置的字符编码展示查询结果；

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个关于字符值的警告，这里分析下编码转换过程：

MySQL客户端发送的插入语句含有"中文"字符串，其编码是跟我们的环境相关的，由于终端编码为UTF-8，因此"中文"的字节表示为"\xE4\xB8\xAD\xE6\x96\x87"。
MySQL Server端收到数据后，会按照character_set_client设置的编码转化为character_set_connection指定的编码，这里2个编码相同，实际不会发生转换（当插入的数据前面有latin1或者utf8等introducer标记指明了字面值字符的编码时，此时也不会转换）。
数据在存储到数据库之前，实际要插入的三个字段的编码都是原始编码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_client、character_set_connection、character_set_results与库表字段字符集定义相同，插入的数据时，Server底层存储与MySQL客户端侧的字节表示一致，此时查询肯定不会产生乱码。那如何在固定character_set_results值的情况，尽量多的恢复test表中的数据呢？

假设character_set_results为utf8，可以通过如下编码转换：

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_client、character_set_connection、character_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设置的影响，对于比较之类的操作可能产生完全不同的结果，需要小心。