MySQL数据库字符集问题小结
这两天尝试着帮COS中文论坛做一个SQL同步脚本，以便将phpwind论坛的数据导入bbpress论坛。由于两个论坛的数据库中使用了不同的字符集，折腾了好久都只得到一堆乱码。因此仔细研究了一番MySQL处理不同字符集的机制，所幸终于解决问题，于是将研究结果在此做一小结。
本文只简单总结有关字符集（Character Set）的配置，对于校对规则（Collation）的相关问题，暂不考虑。
为了使用的灵活，MySQL数据库在处理字符集问题时，允许对多个不同层次和环节分别进行字符集的定义。从数据库结构的层次上看，包括：服务器（server）、数据库（database）、表（table）、列（column）。从一次SQL查询的处理环节上看，又包括：客户端（Client）、连接（Connection）、结果（Result）。
数据库结构的四个层次的字符集定义，是在创建（数据库或表）时指定的。若未指定，则将缺省使用上一层次的字符集。例如，创建数据库时未指定字符集，则会使用服务器的字符集。而在这些层次中，最终决定存储结果与查询结果的，还是列的字符集。
而对于SQL查询的处理环节，MySQL则会根据每个环节之间的字符集差别分别进行转换。而正是由于可能出现的多次转换，导致了设置不当时经常会出现问题。下面举例来说明：
首先看看MySQL中字符集相关的环境变量：
mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.31 sec)
另外，与校对规则相关的环境变量（这里仅列出，不做进一步讨论）：
mysql> show variables like 'coll%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | utf8_swedish_ci |
| collation_database | utf8_swedish_ci |
| collation_server | utf8_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.01 sec)
以上环境变量是在我的英文Ubuntu 9.10环境下默认安装的MySQL的字符集设置。可以发现，其中除了操作系统（character_set_system）使用了utf8外，其他的字符集全部采用了utf8（一种单字节的字符集）。
接下来，在这个环境下，我们分别创建两个数据库（test_utf8和test_utf8）：
mysql> create database test_utf8;
Query OK, 1 row affected (0.13 sec)
mysql> create database test_utf8 character set utf8;
Query OK, 1 row affected (0.03 sec)
然后，我们可以用“show create database”命令分别查看一下这两个库：
mysql> show create database test_utf8;
+-------------+----------------------------------------+
| Database | Create Database |
+-------------+----------------------------------------+
| test_utf8 | CREATE DATABASE `test_utf8`
/*!40100 DEFAULT CHARACTER SET utf8 */ |
+-------------+----------------------------------------+
1 row in set (0.02 sec)
mysql> show create database test_utf8;
+-----------+------------------------------------------+
| Database | Create Database |
+-----------+------------------------------------------+
| test_utf8 | CREATE DATABASE `test_utf8`
/*!40100 DEFAULT CHARACTER SET utf8 */ |
+-----------+------------------------------------------+
1 row in set (0.00 sec)
可以看到，创建test_utf8时，没有指定字符集，它使用了服务器的缺省字符集utf8。
然后，我们分别在两个库中，各自创建两个表：
mysql> create table test_utf8.test (test varchar(10));
Query OK, 0 rows affected (0.38 sec)
mysql> create table test_utf8.test_utf8 (test varchar(10)) character set utf8;
Query OK, 0 rows affected (0.17 sec)
mysql> create table test_utf8.test (test varchar(10));
Query OK, 0 rows affected (0.14 sec)
mysql> create table test_utf8.test_utf8 (test varchar(10)) character set utf8;
Query OK, 0 rows affected (0.11 sec)
用“show create table”来查看这四个表：
mysql> show create table test_utf8.test;
+-------+------------------------------+
| Table | Create Table |
+-------+------------------------------+
| test | CREATE TABLE `test` (
`test` varchar(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+------------------------------+
1 row in set (0.05 sec)
mysql> show create table test_utf8.test_utf8;
+-----------+----------------------------+
| Table | Create Table |
+-----------+----------------------------+
| test_utf8 | CREATE TABLE `test_utf8` (
`test` varchar(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-----------+----------------------------+
1 row in set (0.00 sec)
mysql> show create table test_utf8.test;
+-------+--------------------------------+
| Table | Create Table |
+-------+--------------------------------+
| test | CREATE TABLE `test` (
`test` varchar(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+--------------------------------+
1 row in set (0.03 sec)
mysql> show create table test_utf8.test_utf8;
+-------------+-------------------------------+
| Table | Create Table |
+-------------+-------------------------------+
| test_utf8 | CREATE TABLE `test_utf8` (
`test` varchar(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------------+-------------------------------+
1 row in set (0.00 sec)
同样可以看到，对于没有指定字符集时，所创建的表会缺省使用该数据库的字符集。
下面，我们分别往每个表里插入一条记录（该字段的值为单个汉字“阿”）：
mysql> insert into test_utf8.test values ('阿');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_utf8.test_utf8 values ('阿');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_utf8.test values ('阿');
Query OK, 1 row affected (0.08 sec)
mysql> insert into test_utf8.test_utf8 values ('阿');
Query OK, 1 row affected (0.00 sec)
为说明查询过程（包括插入与查询等操作）中的字符集关系，下面我们修改一下字符集：
mysql> set names 'utf8';
Query OK, 0 rows affected (0.00 sec)
此时，我们可以看看环境变量：
mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.29 sec)
其中的客户端（character_set_client）、连接（character_set_connection）、结果（character_set_results）都变成了utf8。
然后，我们再向四个表中分别插入一条记录，还是插入单个汉字“阿”：
mysql> insert into test_utf8.test values ('阿');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into test_utf8.test_utf8 values ('阿');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_utf8.test values ('阿');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_utf8.test_utf8 values ('阿');
Query OK, 1 row affected, 1 warning (0.00 sec)
最后，我们分别查询每个表中的数据（包括其长度与十六进制值）：
mysql> select test, length(test), hex(test) from test_utf8.test;
+--------+--------------+-----------+
| test | length(test) | hex(test) |
+--------+--------------+-----------+
| 阿 | 3 | E998BF |
| ? | 1 | 3F |
+--------+--------------+-----------+
2 rows in set (0.75 sec)
mysql> select test, length(test), hex(test) from test_utf8.test_utf8;
+--------+--------------+--------------+
| test | length(test) | hex(test) |
+--------+--------------+--------------+
| 阿 | 6 | C3A9CB9CC2BF |
| 阿 | 3 | E998BF |
+--------+--------------+--------------+
2 rows in set (0.03 sec)
mysql> select test, length(test), hex(test) from test_utf8.test;
+--------+--------------+--------------+
| test | length(test) | hex(test) |
+--------+--------------+--------------+
| 阿 | 6 | C3A9CB9CC2BF |
| 阿 | 3 | E998BF |
+--------+--------------+--------------+
2 rows in set (0.00 sec)
mysql> select test, length(test), hex(test) from test_utf8.test_utf8;
+--------+--------------+-----------+
| test | length(test) | hex(test) |
+--------+--------------+-----------+
| 阿 | 3 | E998BF |
| ? | 1 | 3F |
+--------+--------------+-----------+
可以看出，造成存储结果差异的不是数据库的字符集，而是表的字符集（注：其实应该是字段的字符集，不过由于示例中没有指定字段的字符集，所以字段缺省使用了表的字符集）。
另外，还可以看到，在第一次插入数据（环境为utf8）时，只有表的字符集也为utf8时，才正确存入了文字“阿”（E998BF三个字节为“阿”字的UTF8编码）。而第二次插入数据（环境为utf8）时，只有表的字符集也为utf8时，才正确存入了文字“阿”。因此，在使用MySQL数据库时，应确保环境（character_set_client、character_set_connection和character_set_results）与数据库表的字符集相一致。
而在字符集选择上，虽然环境与表都使用utf8时也能正确写入和读取汉字，但在使用MySQL函数length获取字符串长度时，得到的却是字节数3。所以，如果要很好地支持中文，最好避免采用MySQL的默认字符集utf8，而尽可能采用能够支持中文的字符集（GB2312、GBK、GB18030或UTF8）。GB系列的标准，每个汉字占用两个字节，相比UTF8会节省些空间。但是由于与其他国家的字符集有重叠，不利于多语言混用。因此要支持多语言，最好的还是使用UTF8字符集。
至于为什么在环境与表的字符集不同时，存储的结果会有那么大变化，这里可以简单分析一下：情况一，当环境为utf8、表为utf8时，由于系统是utf8，在通过MySQL的查询连接（Connection）时，汉字被从utf8当成utf8处理，即被当成3个字节；而当存入utf8表时，这三个字节被分别转换成utf8格式，由于每个字节的ASCII码都大于127，因此都被转成了两个字节，共计六个字节。情况二、当环境为utf8、表为utf8时，从系统到环境时字符集一致没有做任何转换，仍然是单个字符（3个字节），而当这个字符存入utf8表时，由于utf8的范围（0～127）没法存储下汉字，因此实际存入数据库的就成了个问号（ASCII值为3F）。
对于情况二，由于数据库里存入的是问号，因此无论查询时使用何种配置都无法正确读出结果。而对于情况一，可以采用如下的方式将结果转换后读出：
mysql> select convert(cast(convert(test using utf8) as binary)
-> using utf8) from test_utf8.test_utf8 limit 1;
+----------------------------------------------------------------+
| convert(cast(convert(test using utf8) as binary) using utf8) |
+----------------------------------------------------------------+
| 阿 |
+----------------------------------------------------------------+
1 row in set (0.04 sec)
这里经过三次转换：第一次将utf8表中的内容转为utf8，第二次将该字符串转为二进制格式，第三次将该二进制串以utf8格式转出。具体转换过程可参考：
mysql> select
->
-> test as s1,
-> hex(test) as h1,
-> charset(test) as c1,
->
-> convert(test using utf8) as s2,
-> hex(convert(test using utf8)) as h2,
-> charset(convert(test using utf8)) as c2
->
-> from test_utf8.test_utf8 limit 1;
+--------+--------------+------+--------+--------+--------+
| s1 | h1 | c1 | s2 | h2 | c2 |
+--------+--------------+------+--------+--------+--------+
| 阿 | C3A9CB9CC2BF | utf8 | 阿 | E998BF | utf8 |
+--------+--------------+------+--------+--------+--------+
1 row in set (0.00 sec)
mysql>
mysql> select
->
-> cast(convert(test using utf8) as binary) as s3,
-> hex(cast(convert(test using utf8) as binary)) as h3,
-> charset(cast(convert(test using utf8) as binary)) as c3,
->
-> convert(cast(convert(test using utf8) as binary) using utf8) as s4,
-> hex(convert(cast(convert(test using utf8) as binary) using utf8)) as h4,
-> charset(convert(cast(convert(test using utf8) as binary) using utf8)) as c4
->
-> from test_utf8.test_utf8 limit 1;
+------+--------+--------+------+--------+------+
| s3 | h3 | c3 | s4 | h4 | c4 |
+------+--------+--------+------+--------+------+
| 阿 | E998BF | binary | 阿 | E998BF | utf8 |
+------+--------+--------+------+--------+------+
1 row in set (0.00 sec)