MySQL字符编码问题详细解释

和许多⼈⼀样，我也是在转移blog时才发现这个问题。虽然是⼀个很⽼的问题了，为避免沉痛教训，这⾥就把相关知识做⼀总结，以⽅便后⼈。【现象】

本来我的blog是放在家⾥的服务器的，最近因为要迁到租⽤的主机上，就开始了搬家⼯作。⾸先是⽂件的拷贝，⼀切顺利；接下来是把数据库从家⾥的MySQL中导出，然后导⼊到主机提供商的MySQL上去。由于两边虽然MySQL版本不同（家⾥是5.x，租⽤的主机那边是4.1x），但是由于都安装有PMA（PHPMyAdmin），应该没什么问题。

这么想着，等我在家⾥的PMA⾥执⾏“导出”之后，就犯嘀咕了——怎么打开⽣成的sql⽂件⼀看，wp_posts的贴⼦内容都是乱码啊？通过查看了⼀下数据库、各个表，发现collation⼀栏⾥⾯写的都是utf8_general_ci……虽然不⼤懂collation，但是应该字符编码都是utf-8，怎么会是乱码呢？不管三七⼆⼗⼀，先导⼊到租⽤的主机上再说！——结果，导⼊到租⽤主机上，仍然是乱码。【预备知识】

为了解决问题，有必要学习和复习⼀下相关的基础知识。⾸先是MySQL⾥⾯关于。

Character set顾名思义，就是字符、以及字符对应的编码的集合。例如简体中⽂字符集gb2312就包括简体中⽂中的所有规定汉字，以及每个汉字对应的代码。

Collation，是指⽐较字符的规则的集合。有了⽐较规则，才能够将⼀组数据排序——例如按照英⽂字母顺序排序、汉字按照拼⾳顺序排序等等。显然，针对同样⼀组字符集可以有不同的排序标准、规则。例如汉字可以按照拼⾳排序，也可以按照笔画多少排序。尤其是Unicode的字符集，由于其可以包含不同种类的语⾔，所以可以按照各种语⾔的排序⽅法排序。此外，完全按照字符在字符集⾥的编码进⾏⽐较的⽅式称为binary⽐较。

到了这⾥我们就容易理解了。举例来说，MySQL⽀持的gb2312字符集中，有gb2312_bin和gb2312_general_ci两种collation。很显然前者是binary⽐较规则，后者是⼀般的中⽂字符⽐较规则。

每种字符集都有其默认的collation。对于utf8字符集来说，其默认collation是utf8_general_ci。要获得MySQL⾥⾯⽀持的字符集和默认collation列表，可以使⽤语句：

mysql> SHOW CHARACTER SET;

+----------+-----------------------------+---------------------+

| Charset | Description | Default collation |+----------+-----------------------------+---------------------+

| big5 | Big5 Traditional Chinese | big5_chinese_ci || dec8 | DEC West European | dec8_swedish_ci || cp850 | DOS West European | cp850_general_ci |...

其次，是MySQL中，在哪些地⽅需要这些字符集和collation。总体上分，在MySQL的体系中有三处字符集和collation：服务器（数据），连接，客户端。乍⼀看体系清楚明了，其实并不是这样。下⾯就⼀⼀介绍。

[1] 服务器（数据）端的字符集和collation，可以分成四级逐层指定——server, database, table, column。当MySQL存取位于某⼀列

（column）的数据时，如果column的字符集和collation没有指定，就会向上追溯table的；如果table也没有指定字符集和collation，就以database的字符集和collation作为默认值；如果database仍旧没有指定，那么就以服务器的字符集和collation作为默认值。

那么server的字符集和collation的默认值⼜是从哪⾥来的呢？答案是，配置⽂件（my.ini）和mysqld（或者mysqld-nt）的命令⾏参数中都可以指定。如果不幸的，你根本没有在my.ini或者命令⾏中指定，那么MySQL就会使⽤编译MySQL时指定的默认字符集——latin1。但是，需要注意的是，如果安装MySQL时选择了多语⾔⽀持（⼀般⽤中⽂的都会选择吧），安装程序会⾃动在配置⽂件中设置default-character-set=utf8

这样，所有创建的数据库、表，除⾮明确指出使⽤其它字符集，都会默认的使⽤utf作为数据的字符集（同时使⽤utf8_general_ci作为默认collation，因为它是utf8的默认collation）。相关系统变量

character_set_server：服务器的字符集collation_server：服务器的collationcharacter_set_database：数据库字符集collation_database：数据库的collation

[2] 客户端。对于客户端传送来的literal string（例如INSERT，UPDATE语句当中的值），MySQL需要知道它们是什么编码。同时，MySQL返回给客户端的值（例如SELECT语句的返回值），也可以按照指定的编码返回。

相关系统变量

character_set_client：客户端发送过来⽂字的字符集

character_set_results：发送给客户端的结果所使⽤的字符集

[3] 连接。⽤于连接的字符集和collation，是指MySQL在接受到客户端发送来的⽂本之后，转换成何种字符集，⽤什么规则进⾏⽐较。需要注意的是，如果是将⽂本和数据库中某个column的值⽐较，将优先使⽤该column的字符集和collation。相关系统变量

character_set_connection：⽤于连接的字符集collation_connection：⽤于连接的collation

【问题的分析】

有了上⾯的预备知识，我们就开始分析最初的问题：本来是应该作为UTF-8字符保存的数据，为什么到了数据库中就变成了“乱码”？⽽且这些乱码居然还能毫⽆问题地被wordpress显⽰？为什么⼀旦导⼊到租⽤的主机那⾥就不能正常显⽰了呢？⾸先让我们来看⼀下，我家⾥的服务器上，MySQL的系统变量（System Variables）是如何设置的。

注意：因为⼀些系统变量是根据客户端不同⽽不同的，所以⽤mysql命令⾏登陆所看到的和PHP下看到的并不相同。此外，似乎也不能⽤PMA查看——似乎在PMA中也已经更改了默认的系统变量。因此，要查看PHP作为客户端时的默认系统变量，可以编写⼀个类似下⾯的PHP⼩程序：

(localhost,$user, $pass);$query=\"\";

$result=($query);

其中$result就包含着所有系统变量。在我家⾥的服务器上得到了如下结果（以下只列出跟字符集有关的系统变量）：character_set_client latin1

character_set_connection latin1character_set_database utf8character_set_filesystem binarycharacter_set_results latin1character_set_server utf8character_set_system utf8

collation_connection latin1_swedish_cicollation_database utf8_general_cicollation_server utf8_general_ci

可见，默认的客户端编码、默认的连接编码是latin1——这也就是说，虽然实际上wordpress传递给MySQL的⽂本都是⽤UTF-8编码的，但是由于上述系统变量设置不当，这些UTF-8编码的⽂本被MySQL当作是latin1编码的，并且由于数据库本⾝是utf8，因此把这些“latin1⽂本”⼜转换成了utf8。这样，⼀个汉字居然需要6bytes（⼀个汉字作为UTF-8是3bytes，被当作latin1进⾏了转换，每个latin1字符转换成2bytes的UTF-8编码）。这就不难理解为什么数据库存储的是“乱码”了。

那么为什么这些“乱码”在wordpress显⽰时没问题呢？这是因为，character_set_result也是latin1，也就是说MySQL在取出数据交给wordpress时，把这些数据从utf8转换回latin1，然后wordpress将这些latin1⼜当作了utf8——正好是上⾯的逆过程。那么，为什么到另⼀台服务器上⾯就⽆法正常显⽰了呢？请看看那台租⽤主机的系统变量设置：character_set_client ujis

character_set_connection ujischaracter_set_database ujischaracter_set_results ujischaracter_set_server ujischaracter_set_system utf8

collation_connection ujis_japanese_cicollation_database ujis_japanese_cicollation_server ujis_japanese_ci

可见，其默认的客户端编码是ujis。也就是说，MySQL把utf8数据取出后，将会转换成ujis并传递给wordpress。这经历了latin1 - utf8 - ujis转换的原本是utf8的字符，早已⾯⽬全⾮了……【解决⽅案】

解决⽅案在很多论坛、⽹页上已经有提到了，在也已经有⼈提出过。

但是在解决问题之前，我却很想知道⼀个问题的答案，那就是：这到底是MySQL的问题，还是PHP（特别是php_mysql extension）的问

题，还是wordpress的问题？甚⾄是⽤户配置的问题？我倾向于认为这是⼀个wordpress的问题。因为⽆论MySQL还是PHP都不知道

wordpress使⽤了什么字符编码，所以⽆法更改客户端字符集；⽽作为⼀般的wordpress⽤户，要求他们设置字符编码——可以，但是必须要提供⼀个⽤户界⾯，⽽不是直接修改源程序。

那么解决⽅案（或者说只是⼀个workaround）就是，修改wordpress的\\wp-uncludes\\wp-db.php。在第40多⾏的function wpdb中，在$this->select($dbname);之前添加⼀句$this->query(\"SET NAMES latin1\");

语句的功能就是，执⾏了SET NAMES 'x'相当于下⾯三条语句的功能。SET character_set_client = x;SET character_set_results = x;SET character_set_connection = x;

这样，在默认客户端字符集是ujis的租⽤主机上，导⼊的wordpress⽂章也能正常显⽰了。当然，这不是彻底的解决⽅案——这只是“将错就错”，反正数据库⾥⾯存储的已经是被当作latin1⽽转换成utf8的utf8了，那么就将其转换回所谓的latin1就是了。这样做将使其他程序⽆法读取wordpress的数据，并且更重要的是，数据库中存储的“utf8数据”⽆法真正按照utf8应有的排序规则来排序。那么最彻底的做法，就是在安装wordpress时就添加上⾯所说的SET NAMES语句，并且设置客户端的字符集为utf8：$this->query(\"SET NAMES utf8\");

但是这样做的话，已经被当作latin1写到数据库⾥⾯的⽂章就会⽆法正常显⽰了。要让他们正常显⽰，必须经过utf8 - latin1的转换。如果数量较多，可以考虑编写⼀个程序进⾏转换；数量较少的话……⼿动转换吧。

BTW，国内⾼⼿们汉化的中⽂版的wordpress中已经添加好这⼀句了，上⾯的信息只适⽤于那些使⽤英⽂wordpress的朋友，以及喜欢追根问底的朋友。

最后推荐⼀篇参考⽂章：

查看字符编码命令：

mysql> SHOW VARIABLES LIKE 'character_set_%'; +--------------------------+----------------------------+ | Variable_name | Value |

+--------------------------+----------------------------+ | character_set_client | latin1 |

| character_set_connection | latin1 | | character_set_database | latin1 | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 |

| character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 7 rows in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'collation_%'; +----------------------+-------------------+ | Variable_name | Value |

+----------------------+-------------------+

| collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +----------------------+-------------------+ 3 rows in set (0.00 sec)

GBK: create database test2 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;UTF8: CREATE DATABASE `test2` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci