MySQLmysqlMySQL学习

MySQL字符集编码解析

2015-03-16  本文已影响1035人  __七把刀__

前段时间公司内部博客上凯哥分享了一篇关于mysql字符集编码的文章,之前我对mysql字符集一块基本没有深究过,看到凯哥文章后有些地方有点疑惑,遂自己去看了mysql的官方文档,并参考了凯哥的文章,总结了这篇博文.本文主要是对mysql常见的字符集问题进行整理,如有错误,请大家指正.

1.MySQL字符集编码简介

谈到字符集,总会跟编码扯上关系,有关字符集和编码的理论知识请参见我之前的文章.MySQL内部是支持多种字符集的,这里就不再严格区分字符集和编码的概念了.同时,MySQL中不同层次有不同的字符集编码格式,主要有四个层次:服务器,数据库,表和列.字符集编码不仅影响数据存储,还影响客户端程序和数据库之间的交互.在mysql中输入命令show session variables like '%character%'可以看到如下一些字符集:

+--------------------------+--------------------------------------------------------+
| Variable_name            | Value                                                  |
+--------------------------+--------------------------------------------------------+
| character_set_client     | utf8                                                   |
| character_set_connection | utf8                                                   |
| character_set_database   | latin1                                                 |
| character_set_filesystem | binary                                                 |
| character_set_results    | utf8                                                   |
| character_set_server     | latin1                                                 |
| character_set_system     | utf8                                                   |
| character_sets_dir       | /usr/local/mysql-5.6.15-osx10.7-x86_64/share/charsets/

mysql中的字符集都对应着一个默认的校对规则(COLLATION),当然一个字符集也可能对应多个校对规则,但是两个不同的字符集不能对应同一个规则。校对规则不指定就是使用默认的，比如utf8字符集对应的默认校对规则就是utf8_general_ci。校对规则后缀如_cs,_ci,_bin分别表示是大小写相关/大小写无关/以字符串编码的二进制值来比较大小。如果比较的两个字符集不同，则mysql在比较前会先将其转换到同一个字符集再比较，如果两个字符集不兼容，则会报错Illegal mix of collations。

需要注意的是，校对规则可能会影响查询。比如数据表的一个字段本身设置的校对规则为utf8_general_ci，且在title字段有索引，而你查询的时候使用了SELECT xx FROM test ORDER BY title COLLATE utf8_bin来用另外校对规则进行排序，则此时就用不了索引，转而使用filesort。在实际项目中，一般不去显示指定的校对规则。

下面来看看上面命令列出的字符集相关变量的含义

2.MySQL字符集编码层次

第一部分主要是归纳了MySQL文档中关于字符集编码的说明.这部分主要说明下MySQL字符集编码层次:服务器-数据库-表-字段.

简单来说,服务器编码就是character_set_server来指定的.当我们创建数据库的时候可以指定编码,如果没有指定,采用的就是character_set_server指定的编码.例如:我们使用"create database t1 character set gbk",这里我们指定了数据库t1的编码为gbk,所以不会采用character_set_server指定的编码.而如果我们使用"create database t2",则通过"show create database t2"可以看到t2的编码为character_set_server定的编码.

同理,mysql表也可以有自己独立的编码,在创建表的时候可以指定,如果没有指定,则默认采用数据库的编码.比如我们再之前的数据库t1创建表t11,create table t11(i int) character set utf8,则表t11的编码为utf8,如果不指定编码则编码为数据库t1的编码gbk.

此外,mysql表中的字段也可以有自己的编码,如果不指定字段编码,则字段编码与表的编码一致.

3.MySQL连接字符集

前面谈到的编码内容基本都不会产生乱码问题,mysql中容易产生乱码的地方在character_set_client, character_set_connection, character_set_results这三个变量的设定.可以简单的通过set names utf8或者charset utf8命令来一次设置这三个参数.

刚刚接触这几个变量的时候我完全没有看懂,后来查找了不少资料,姑且算是理解了一点,如有错误，请大家指正。

从文档中的解释来看,mysql连接字符集转换主要包括下面三个步骤:

更加详细的转换过程如下:

Client program sends SQL statement
   |
   | Encoding: A, defined as "character_set_client"
   v
MySQL server - Convertion from encoding A to encoding B
   |
   | Encoding: B, defined as "character_set_connection"
   v
MySQL server - Execution to store data
MySQL server - Conversion from encoding B to encoding C
   |
   | Encoding: C, defined by text column encoding 
   v
MySQL server - Storage
...
MySQL server - Storage
   |
   | Encoding: C, defined by text column encoding
   v
MySQL server - Execution to fetch data
MySQL server - Convertion from encoding C to encoding D
   |  
   | Encoding: D, defined as "character_set_results"
   v
Client program receives result set

接下来就实例分析下mysql可能乱码的情况以及我认为的原因,不对之处请指出.

4.MySQL乱码实例分析

4.1 问题实例

这个测试例子跟凯哥的一样，我们创建一个测试的数据库db1,数据库编码为latin1,注意当前我的机器的终端编码为zh_CN.UTF-8,数据库的编码设定如第1部分所示，然后在db1中创建一个表test,sql语句如下:

CREATE TABLE `test` (
  `gbk` varchar(2) CHARACTER SET gbk DEFAULT NULL,
  `utf8` varchar(2) CHARACTER SET utf8 DEFAULT NULL,
  `latin_utf8` varchar(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

注意到我们的表的编码是latin1,而表中三个字段的编码各不相同,分别为gbk编码,utf8编码以及latin1编码.之所以这样创建正是为了验证mysql字符集编码的转换过程.好了,重点来了,现在我们在mysql客户端执行:

mysql> insert into test values("中文", "中文", "中文");
Query OK, 1 row affected, 1 warning (0.00 sec)

安装了mysql的筒子可以测试下,在mysql没有开启strict模式的时候,这个插入语句会报一个警告,内容如下:

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                             |
+---------+------+-------------------------------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\\xE4\\xB8\\xAD\\xE6\\x96\\x87' for column 'latin_utf8' at row 1 |
+---------+------+-------------------------------------------------------------------------------------+

我们可以先select看看test表中的内容:

mysql> select * from test;
+--------+--------+------------+
| gbk    | utf8   | latin_utf8 |
+--------+--------+------------+
| 中文   | 中文   | ??         |
+--------+--------+------------+

我们还可以查看下test表中实际存储的内容:

mysql> select hex(gbk), hex(utf8), hex(latin_utf8) from test;
+----------+--------------+-----------------+
| hex(gbk) | hex(utf8)    | hex(latin_utf8) |
+----------+--------------+-----------------+
| D6D0CEC4 | E4B8ADE69687 | 3F3F            |
+----------+--------------+-----------------+

可以发现直接select查看的时候latin_utf8字段乱码了,而通过hex函数查看发现原来latin_utf8字段存储的内容有问题. 出现这个问题的原因就是编码转换过程出了错,按照之前的原理来分析下整个编码转换过程:

4.2 解决方案

这一小节就来说说4.1中的问题,根据上面的分析,为了表test中的latin_utf8字段能够正常的插入内容,我们不重新设置character_set_client和character_set_connection的情况下,那么有个好的方法就是加入introducer,关于introducer可以参见mysql官方文档.那么我们的插入语句改为

mysql> insert into test values("中文", "中文", _latin1"中文");
Query OK, 1 row affected (0.02 sec)

由于指定了latin_utf8字段的introducer为_latin1,这样在第一次由character_set_client转换为character_set_connection的时候会忽略latin_utf8的转换,所以还是保持原来的utf8字符,接下来将其存入到latin1字段中,亦不会有问题,因为编码相同,不需要转换,所以latin_utf8字段实际存储的还是\\xE4\\xB8\\xAD\\xE6\\x96\\x87.这点可以通过下面的命令来验证:

mysql> select hex(gbk), hex(utf8), hex(latin_utf8) from test;
+----------+--------------+-----------------+
| hex(gbk) | hex(utf8)    | hex(latin_utf8) |
+----------+--------------+-----------------+
| D6D0CEC4 | E4B8ADE69687 | 3F3F            |
| D6D0CEC4 | E4B8ADE69687 | E4B8ADE69687    |
+----------+--------------+-----------------+

那么我们如果直接select查询,还会出错么呢?答案是会的,因为如前所说,查询的时候会将字段编码转换为character_set_results编码的,显然gbk和utf8字段都没有问题,但是对于latin_utf8字段,其值会通过s.decode('latin1').encode('utf8'),从而导致在查询的时候会乱码。

mysql> select * from test;
+--------+--------+----------------+
| gbk    | utf8   | latin_utf8     |
+--------+--------+----------------+
| 中文   | 中文   | ??             |
| 中文   | 中文   | 中文         |
+--------+--------+----------------+
2 rows in set (0.01 sec)

那么解决的方法也比较简单,就是中select语句中的字段前面加上binary标识,表示该字段查询结果不需要经过character_set_results的转换.如下:

mysql> select gbk, utf8, binary latin_utf8 from test;
+--------+--------+-------------------+
| gbk    | utf8   | binary latin_utf8 |
+--------+--------+-------------------+
| 中文   | 中文   | ??                |
| 中文   | 中文   | 中文              |
+--------+--------+-------------------+
2 rows in set (0.00 sec)

5.总结

mysql编码系统复杂,依照原理和测试的结果来看,character_set_client一定要与传入的数据编码一致,不然就会容易出现乱码问题,character_set_connection可以与character_set_client不同,但是个人建议一样最好,免得出现其他问题.此外,如果对结果编码有要求,就设置下character_set_results编码,当然我个人觉得这三个编码一致是最省事的.此外,数据表字段编码如果用latin1编码,对于like搜索会有一些问题。最好是utf8编码省时省力，如果用gbk一定要注意宽字节注入问题。

UPDATED：
之前有个疑问是为什么有了character_set_client了还要加上character_set_connection，多出来的这次转换的意义在哪里。看官方文档描述：

也就是说，character_set_connection的应用情况基本就是不带introducer的字符串字面值，或者更确切的说这是mysql代码内部所用编码（不是数据存储编码，数据存储编码由表的定义指定）。在前面的试验中，如果将character_set_connection设置为GBK，则执行 SELECT length('中文')返回为4，而如果设置character_set_connection为UTF8，则执行结果为6。这里的中文编码就是依据的character_set_connection。

此外，比如字符串比较SELECT '中' > '哈’（中的GBK编码为D6D0，UTF8编码为E4B8AD，哈的GBK编码为B9FE，UTF8编码为E59388），在两者都是字面值的字符串的情况下，比较的时候字符集是以character_set_connection为准的，当该值为GBK时，我们发现结果为1;而如果设置character_set_connection为UTF8时，则结果为0。而如果查询语句是跟列的值相比的， SELECT * from test WHERE gbk>'哈'，则此时会将字面值字符串'哈'转换为该列对应的编码GBK进行比较。

我总结了这些地方,时间也很仓促,可能也有理解不到位的地方,还请大家指出。

6.参考资料

上一篇 下一篇

猜你喜欢

热点阅读