MySQL字符集编码总结
之前内部博客上凯哥分享了一篇关于mysql字符集的文章,之前我对mysql字符集一块基本没有深究过,看到凯哥文章后有些地方有点疑惑,遂自己去看了mysql的官方文档,并參考了凯哥的文章,总结了这篇博文.本文主要是对mysql常见的字符集问题进行整理,如有错误,请大家指正.
1.MySQL字符集编码简单介绍
谈到字符集,总会跟编码扯上关系,有关字符集和编码的理论知识请參见我之前的文章.MySQL内部是支持多种字符集的,这里就不再严格区分字符集和编码的概念了.同一时候,MySQL中不同层次有不同的字符集编码格式,主要有四个层次:server,数据库,表和列.字符集编码不仅影响数据存储,还影响client程序和数据库之间的交互.在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:server解析客户端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:server返回给客户端的查询结果或者错误提示的字符集编码.(The character set used for returning query results such as result sets or error messages to the client)
- character_set_system:这是mysqlserver用来存储元数据的编码,通常就是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,另外,我在mysqlclient运行了SET NAMES GBK,假设我不改动character_set_filesystem的值,运行SELECT * FROM t1 INTO OUTFILE '文件1', 能够发现相应的文件夹以下生成了一个名为"文件1"的文件,那文件名称编码是什么呢?事实上这里有几个地方须要注意,首先,我们的sql语句里面的"文件1"原生编码就是终端编码UTF8,也就是'xe6x96x87xe4xbbxb61',而导出数据的语句SELECT * FROM t1 INTO OUTFILE '文件1',依照前面的说法,由于character_set_filesystem为binary,因此'xe6x96x87xe4xbbxb61'不会转换,这样终于还是'xe6x96x87xe4xbbxb61',这样在zh_CN.UTF8的系统中文件名称不会乱码.而假设我们设置了character_set_filesystem=UTF8,则原生的'xe6x96x87xe4xbbxb61'会先依照GBK解码,然后用UTF8编码,最后的结果是"xe9x8fx82xe5x9bxa6xe6xacxa21",这样文件名称就会乱码了.所以这个变量也最好不要改动,用默认值就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字符集编码层次:server-数据库-表-字段.
简单来说,服务器编码就是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中easy产生乱码的地方在character_set_client, character_set_connection, character_set_results这三个变量的设定.能够简单的通过set names utf8或者charset utf8命令来一次设置这三个參数.
刚刚接触这几个变量的时候我全然没有看懂,后来查找了不少资料,姑且算是理解了一点,当然也可能是错的,由于没有看过mysql源代码,详细的原理还是请大神们不吝赐教.
从文档中的解释来看,mysql连接字符集转换主要包含以下三个步骤:
- 1.character_set_client是client发送过来的sql语句的编码,由于服务端本身并不知道client的sql语句的编码是什么,所以是以这个变量作为clientsql语句的初始编码.而服务端接收到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字符集编码的转换过程.好了,重点来了,如今我们在mysqlclient运行:
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: 'xE4xB8xADxE6x96x87' 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,因此"中文"字节表示为xE4xB8xADxE6x96x87.
- server端接收到该语句会当作utf8编码,由于character_set_client=utf8,接下来是会进行第一步转换,即将语句从character_set_client转成character_set_connection的编码,由于我们这里这2个编码同样,实际就不会转换(此外,假设插入的数据前面有latin1或者utf8等introducer标记,也不会转换,由于introducer标记已经指明了字面值字符的编码).
- 接下来,数据要存储到数据库了,这个时候实际要插入的三个字段的编码都是原始编码xE4xB8xADxE6x96x87,这个时候发生第二次编码转换,即由character_set_connection编码转换为数据表字段指定的编码.那么接下来,我们能够看到,由本身的UTF8编码与字段utf8同样,不须要进行转换.接下来看gbk字段,它的编码是gbk,这时会将原始编码s="xE4xB8xADxE6x96x87"依照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字段会经过'xD6xD0xCExC4'.decode('gbk').encode('utf8')返回,这样我们在utf8编码的mysqlclient可以正常显示gbk字段.同理,因为utf8字段本身与character_set_results,所以不会发生编码转换,原样返回xE4xB8xADxE6x96x87,因此也是能正常显示的.而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字段实际存储的还是xE4xB8xADxE6x96x87.这点能够通过以下的命令来验证:
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.番外篇
在charset utf8情况下,显示正常是
mysql> charset utf8;
Charset changed
mysql> select "中文";
+--------+
| 中文 |
+--------+
| 中文 |
+--------+
由于我们终端本身是UTF8编码,这个也非常好解释,由于三个编码都同样,所以值本身不会发生转换,有一点要注意的是,对于column名称,是会转成character_set_results指定的编码的,由于这里本身就是UTF8,所以显示正常.mysql> charset gbk;
Charset changed
mysql> select "中文";
+--------+
| ??
?? |
+--------+
| 中文 |
+--------+
能够发现值本身没有变化,可是column名称变成乱码了,由于由utf8转成gbk编码导致的.
而假设charset latin1,则能够发现结果也是正确的,由于UTF8编码转换成latin1是能够正常显示的.
mysql> charset latin1;
Charset changed
mysql> select "中文";
+--------+
| 中文 |
+--------+
| 中文 |
6.总结
mysql编码系统复杂,按照原理和測试的结果来看,character_set_client一定要与传入的数据编码一致,不然就会easy出现乱码问题,character_set_connection能够与character_set_client不同,可是个人建议一样最好,免得出现其它问题.此外,假设对结果编码有要求,就设置下character_set_results编码,当然我个人认为这三个编码一致是最省事的.此外,数据表字段编码假设用latin1编码,对于like搜索会有一些问题,最好大家按照自己需求来设定合理的字段编码了.
我总结了这些地方,时间也非常仓促,可能也有理解不到位的地方,还请大家指出.当然,最后要致谢凯哥,是凯哥最初的博客让我去研究了下mysql的编码,兴许有新的认识我会再继续更新该文章.