颜林林的个人网站

MySQL数据库字符集问题小结

2010-01-15 21:38

这两天尝试着帮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_utf8test_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_clientcharacter_set_connectioncharacter_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)