MySQL字符集编码总结
之前内部博客上凯哥分享了一篇关于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),当然一个字符集也可能对应多个校对规则,但是两个不同的字符集不能对应同一个规则.由于我平时都用的默认校对规则,所以就忽略不谈了,后续有新的发现我会补上.
下面来看看上面命令列出的字符集相关变量的含义
- character_set_client:服务器解析客户端sql语句的字符集.(The character set for statements that arrive from the client. The session value of this variable is set using the character set requested by the client when the client connects to the server).
- character_set_connection:字符串字面值（literal strings）的字符集.
- character_set_results:服务器返回给客户端的查询结果或者错误提示的字符集编码.(The character set used for returning query results such as result sets or error messages to the client)
- character_set_system:这是mysql服务器用来存储元数据的编码,通常就是utf8,不要去修改它.
- character_sets_dir:这是mysql字符集编码存储目录.
- character_set_filesystem:这是文件系统字符集编码,主要用于解析用于文件名的字符串字面值,如LOAD DATA INFILE和SELECT ...INTO OUTFILE等语句以及LOAD_FILE()函数.在打开文件之前,文件名会从character_set_client转换为character_set_filesystem指定的编码.默认值为binary,也就是说不会进行转换.例如我们设置的character_set_client=GBK,而character_set_filesystem为默认值的话,则采用SELECT...INTO OUTFILE "文件名",文件名为GBK编码.反之,如果我们设置了character_set_filesystem=UTF8,则导出的文件名为UTF8编码. 例如:我的终端编码是UTF8,系统默认语言和编码为zh_CN.UTF8.我有一个数据库名为test,test中有个表名为t1,编码为latin1,另外,我在mysql客户端执行了SET NAMES GBK,如果我不修改character_set_filesystem的值,执行SELECT * FROM t1 INTO OUTFILE ‘文件1‘, 可以发现对应的目录下面生成了一个名为"文件1"的文件,那文件名编码是什么呢?其实这里有几个地方需要注意,首先,我们的sql语句里面的"文件1"原生编码就是终端编码UTF8,也就是‘\xe6\x96\x87\xe4\xbb\xb61‘,而导出数据的语句SELECT * FROM t1 INTO OUTFILE ‘测试文件‘,按照前面的说法,因为character_set_filesystem为binary,因此‘\xe6\x96\x87\xe4\xbb\xb61‘会按照GBK编码进行解码在进行编码,这样最终还是‘\xe6\x96\x87\xe4\xbb\xb61‘,这样在zh_CN.UTF8的系统中文件名不会乱码.而如果我们设置了character_set_filesystem=UTF8,则原生的‘\xe6\x96\x87\xe4\xbb\xb61‘会先按照GBK解码,然后用UTF8编码,最后的结果是"\xe9\x8f\x82\xe5\x9b\xa6\xe6\xac\xa21",这样文件名就会乱码了.所以这个变量也最好不要修改,用默认值就OK.
- character_set_server:服务器默认字符集编码,如果创建数据库的时候没有指定编码,则采用character_set_server指定编码.
- character_set_database:默认数据库的字符集编码.如果没有默认数据库,则该变量值与character_set_server相同.其实这个值代表的就是你当前数据库的编码而已,比如使用"use test",而test数据库的编码为latin1的话,这个值就是latin1.而你切换的时候"use test2",则character_set_database的值就是数据库test2的编码.
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源码,具体的原理还是请大神们指教.
从文档中的解释来看,mysql连接字符集转换主要包括下面三个步骤:
- 1.character_set_client是客户端发送过来的sql语句的编码,因为服务端本身并不知道客户端的sql语句的编码是什么,所以是以这个变量作为客户端sql语句的初始编码.而服务端接收到sql语句后,则会将sql语句转换为character_set_connection指定的编码(注意,对于字面值字符串,如果前面有introducer标记如latin1或utf8,则不会进行这一步转换).转换完成,才会真正执行sql语句.
- 2.进行内部操作前将sql语句中的数据从character_set_connection转换为数据表中相应字段的编码.
- 3.将操作结果从内部字符集编码转换为character_set_results编码.
更加详细的转换过程如下:
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字段存储的内容有问题. 出现这个问题的原因就是编码转换过程出了错,按照之前的原理来分析下整个编码转换过程:
- 首先我们mysql客户端发送插入语句insert into test values("中文", "中文", "中文");,注意到"中文"的编码是跟我们的环境相关的,我这里是zh_CN.UTF-8,因此"中文"字节表示为\xE4\xB8\xAD\xE6\x96\x87.
- 服务器端接收到该语句会当作utf8编码,因为character_set_client=utf8,接下来是会进行第一步转换,即将语句从character_set_client转成character_set_connection的编码,由于我们这里这2个编码相同,实际就不会转换(此外,如果插入的数据前面有latin1或者utf8等introducer标记,也不会转换,因为introducer标记已经指明了字面值字符的编码).
- 接下来,数据要存储到数据库了,这个时候实际要插入的三个字段的编码都是原始编码\xE4\xB8\xAD\xE6\x96\x87,这个时候发生第二次编码转换,即由character_set_connection编码转换为数据表字段指定的编码.那么接下来,我们可以看到,由本身的UTF8编码与字段utf8相同,不需要进行转换.接下来看gbk字段,它的编码是gbk,这时会将原始编码s="\xE4\xB8\xAD\xE6\x96\x87"按照utf8编码转换为GBK编码,即执行s.decode(‘utf8‘).encode(‘gbk‘),所以存储的是D6D0CEC4,也没有问题. 最后,看latin_utf8字段,同样需要转换编码,由于latin1表示不了utf8编码的范围,所以s.decode(‘utf8‘).encode(‘latin1‘)这个转换过程会出错,导致的结果就是latin_utf8字段存储的是??,即3F3F.
- 最后就是select语句返回的结果分析,这是第三个需要转换编码的地方,即将字段从字段编码转换为character_set_results指定的编码.这也是我们上面为什么gbk字段和utf8字段都能正常显示中文的原因,因为在返回结果的时候,gbk字段会经过‘\xD6\xD0\xCE\xC4‘.decode(‘gbk‘).encode(‘utf8‘)返回,这样我们在utf8编码的mysql客户端能够正常显示gbk字段.同理,由于utf8字段本身与character_set_results,所以不会发生编码转换,原样返回\xE4\xB8\xAD\xE6\x96\x87,因此也是能正常显示的.而latin_utf8字段本身存储的就是3F3F,再经过编码转换,虽然utf8编码能够兼容latin1,但是本身的编码是3F3F,所以最终结果就是"??".
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(‘gbk‘),从而导致在查询的时候会乱码.
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搜索会有一些问题,最好大家依照自己需求来设定合理的字段编码了.
我总结了这些地方,时间也很仓促,可能也有理解不到位的地方,还请大家指出.当然,最后要致谢凯哥,是凯哥最初的博客让我去研究了下mysql的编码,后续有新的认识我会再继续更新该文章.