背景:目前正在进行业务重构,需要对使用MySQL的业务库表进行重新设计,在迁移时,遇到了中文字符乱码问题(源库表的默认编码是LATIN1,新库表的默认编码为UTF8),故重新学习了下MySQL编码和解码相关知识,并整理了在遭遇乱码时的一些常用技巧。(本文发布于云+社区:https://cloud.tencent.com/developer/article/1370123)
一个字符集包含三个关键元素:字库表(character repertoire)、编码字符集(coded character set)、字符编码(character encoding form)。
字库表:所有可读或者可显示字符的数据库,字库表决定了整个字符集能够展示的所有字符的范围。
编码字符集:即用一个编码值code point来表示一个字符在字库中的位置。
字符编码:将编码字符集和实际存储数值之间的转换关系。
常见的字符集包括以下几种:
UTF-8是 Unicode字符集的一种字符编码。Unicode的编号从0000开始一直到10FFFF共分为16个Plane,每个Plane中有65536个字符。而UTF-8则只实现了第一个Plane,可见UTF-8虽然是一个当今接受度最广的字符集编码,但是它并没有涵盖整个Unicode的字库,这也造成了它在某些场景下对于特殊字符的处理困难。
MySQL内部支持多种字符集,而字符集和编码可以等同。同一时候,MySQL中不同层次有不同的字符集编码格式,主要有四个层次:server,数据库,表和列。字符集编码不仅影响数据存储,还影响client程序和数据库之间的交互。在mysql中输入命令
SHOW SESSION VARIABLES LIKE '%character%'
能够看到例如以下一些关于字符集的参数:
还有其他相关变量collation_connection、collation_database、collation_server,用来描述字符序;
1、MySQL Client根据字符编码转换成二进制流,并传输到MySQL Server; 2、MySQL Server收到请求时将请求数据data从character_set_client转换为character_set_connection; 3、进行内部操作前将请求数据从character_set_connection转换为内部操作字符集,其确定方法如下:
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;
存入和取出时对应环节的编码不一致会造成乱码。
比如向默认字符集为utf8的数据表插入utf8编码的数据前没有设置连接字符集,查询时设置连接字符集为utf8;
单个流程中编码不一致,且差异的两个字符集之间无法进行无损编码转换,也会出现乱码。
比如shell是UTF8编码,MySQL的character_set_client配置成了GBK,而表结构却又是charset=utf8,那么毫无疑问的一定会出现乱码。
客户端(web或shell)的字符编码和最终表的字符编码格式不同,但是只要保证存和取两次的字符集编码一致,且能进行无损编码转换时,此时不会产生乱码的现象。
在编码为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个关于字符值的警告,这里分析下编码转换过程:
此时通过设置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 …;
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。