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)