前段时间公司内部博客上凯哥分享了一篇关于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。在实际项目中，一般不去显示指定的校对规则。
下面来看看上面命令列出的字符集相关变量的含义
-
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 '文件1',按照前面的说法,因为character_set_filesystem为binary,因此'\xe6\x96\x87\xe4\xbb\xb61'不会转换编码,这样最终还是'\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的编码.LOAD DATA INFILE的时候，数据库总是将文件中的字符按照character_set_database解析，在5.0之后的版本中，可以在LOAD的时候用character set指定字符集。
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连接字符集转换主要包括下面三个步骤:
-
1.character_set_client是客户端发送过来的sql语句的编码,因为服务端本身并不知道客户端的sql语句的编码是什么,所以是以这个变量作为客户端sql语句的初始编码.而服务端接收到sql语句后,则会将sql语句从character_set_client转换为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('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，多出来的这次转换的意义在哪里。看官方文档描述：
-
What character set should the server translate a statement to after receiving it?
For this, the server uses the character_set_connection
and collation_connection system variables. It converts statements sent by the client from character_set_client
to character_set_connection (except for string literals that have an introducer such as _latin1 or _utf8). collation_connection
is important for comparisons of literal strings. For comparisons of strings with column values,collation_connection
does not matter because columns have their own collation, which has a higher collation precedence.Every character string literal has a character set and a collation.
For the simple statement SELECT 'string', the string has the connection default character set and collation defined by the character_set_connection and collation_connection system variables.
也就是说，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进行比较。
我总结了这些地方,时间也很仓促,可能也有理解不到位的地方,还请大家指出。
网友评论