MySQL数据库字符集问题小结

2010-01-15 21:38

本文共约 3452 字预计阅读时间约 12 分钟

技术 MySQL 字符集

这两天尝试着帮COS中文论坛做一个SQL同步脚本，以便将phpwind论坛的数据导入bbpress论坛。由于两个论坛的数据库中使用了不同的字符集，折腾了好久都只得到一堆乱码。因此仔细研究了一番MySQL处理不同字符集的机制，所幸终于解决问题，于是将研究结果在此做一小结。

本文只简单总结有关字符集（Character Set）的配置，对于校对规则（Collation）的相关问题，暂不考虑。

为了使用的灵活，MySQL数据库在处理字符集问题时，允许对多个不同层次和环节分别进行字符集的定义。从数据库结构的层次上看，包括：服务器（server）、数据库（database）、表（table）、列（column）。从一次SQL查询的处理环节上看，又包括：客户端（Client）、连接（Connection）、结果（Result）。

数据库结构的四个层次的字符集定义，是在创建（数据库或表）时指定的。若未指定，则将缺省使用上一层次的字符集。例如，创建数据库时未指定字符集，则会使用服务器的字符集。而在这些层次中，最终决定存储结果与查询结果的，还是列的字符集。

而对于SQL查询的处理环节，MySQL则会根据每个环节之间的字符集差别分别进行转换。而正是由于可能出现的多次转换，导致了设置不当时经常会出现问题。下面举例来说明：

首先看看MySQL中字符集相关的环境变量：

    sql
    
      
      
    
  
 1mysql> show variables like 'char%';
 2+--------------------------+----------------------------+
 3| Variable_name            | Value                      |
 4+--------------------------+----------------------------+
 5| character_set_client     | utf8                       |
 6| character_set_connection | utf8                       |
 7| character_set_database   | utf8                       |
 8| character_set_filesystem | binary                     |
 9| character_set_results    | utf8                       |
10| character_set_server     | utf8                       |
11| character_set_system     | utf8                       |
12| character_sets_dir       | /usr/share/mysql/charsets/ |
13+--------------------------+----------------------------+
148 rows in set (0.31 sec)

    共 14 行
  

另外，与校对规则相关的环境变量（这里仅列出，不做进一步讨论）：

    sql
    
      
      
    
  
1mysql> show variables like 'coll%';
2+----------------------+-------------------+
3| Variable_name        | Value             |
4+----------------------+-------------------+
5| collation_connection | utf8_swedish_ci   |
6| collation_database   | utf8_swedish_ci   |
7| collation_server     | utf8_swedish_ci   |
8+----------------------+-------------------+
93 rows in set (0.01 sec)

    共 9 行
  

以上环境变量是在我的英文Ubuntu 9.10环境下默认安装的MySQL的字符集设置。可以发现，其中除了操作系统（character_set_system）使用了utf8外，其他的字符集全部采用了utf8（一种单字节的字符集）。

接下来，在这个环境下，我们分别创建两个数据库（test_utf8和test_utf8）：

    sql
    
1mysql> create database test_utf8;
2Query OK, 1 row affected (0.13 sec)
3
4mysql> create database test_utf8 character set utf8;
5Query OK, 1 row affected (0.03 sec)

    共 5 行

然后，我们可以用“show create database”命令分别查看一下这两个库：

    sql
    
      
      
    
  
 1mysql> show create database test_utf8;
 2+-------------+----------------------------------------+
 3| Database    | Create Database                        |
 4+-------------+----------------------------------------+
 5| test_utf8   | CREATE DATABASE `test_utf8`
 6                /*!40100 DEFAULT CHARACTER SET utf8 */ |
 7+-------------+----------------------------------------+
 81 row in set (0.02 sec)
 9
10mysql> show create database test_utf8;
11+-----------+------------------------------------------+
12| Database  | Create Database                          |
13+-----------+------------------------------------------+
14| test_utf8 | CREATE DATABASE `test_utf8`
15              /*!40100 DEFAULT CHARACTER SET utf8 */   |
16+-----------+------------------------------------------+
171 row in set (0.00 sec)

    共 17 行
  

可以看到，创建test_utf8时，没有指定字符集，它使用了服务器的缺省字符集utf8。

然后，我们分别在两个库中，各自创建两个表：

    sql
    
      
      
    
  
 1mysql> create table test_utf8.test (test varchar(10));
 2Query OK, 0 rows affected (0.38 sec)
 3
 4mysql> create table test_utf8.test_utf8 (test varchar(10)) character set utf8;
 5Query OK, 0 rows affected (0.17 sec)
 6
 7mysql> create table test_utf8.test (test varchar(10));
 8Query OK, 0 rows affected (0.14 sec)
 9
10mysql> create table test_utf8.test_utf8 (test varchar(10)) character set utf8;
11Query OK, 0 rows affected (0.11 sec)

    共 11 行
  

用“show create table”来查看这四个表：

    sql
    
      
      
    
  
 1mysql> show create table test_utf8.test;
 2+-------+------------------------------+
 3| Table | Create Table                 |
 4+-------+------------------------------+
 5| test  | CREATE TABLE `test` (
 6  `test` varchar(10) DEFAULT NULL
 7  ) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
 8+-------+------------------------------+
 91 row in set (0.05 sec)
10
11mysql> show create table test_utf8.test_utf8;
12+-----------+----------------------------+
13| Table | Create Table |
14+-----------+----------------------------+
15| test_utf8 | CREATE TABLE `test_utf8` (
16    `test` varchar(10) DEFAULT NULL
17    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
18+-----------+----------------------------+
191 row in set (0.00 sec)
20
21mysql> show create table test_utf8.test;
22+-------+--------------------------------+
23| Table | Create Table                   |
24+-------+--------------------------------+
25| test  | CREATE TABLE `test` (
26    `test` varchar(10) DEFAULT NULL
27    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
28+-------+--------------------------------+
291 row in set (0.03 sec)
30
31mysql> show create table test_utf8.test_utf8;
32+-------------+-------------------------------+
33| Table       | Create Table                  |
34+-------------+-------------------------------+
35| test_utf8   | CREATE TABLE `test_utf8` (
36         `test` varchar(10) DEFAULT NULL
37         ) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
38+-------------+-------------------------------+
391 row in set (0.00 sec)

    共 39 行
  

同样可以看到，对于没有指定字符集时，所创建的表会缺省使用该数据库的字符集。

下面，我们分别往每个表里插入一条记录（该字段的值为单个汉字“阿”）：

    sql
    
      
      
    
  
 1mysql> insert into test_utf8.test values ('阿');
 2Query OK, 1 row affected (0.00 sec)
 3
 4mysql> insert into test_utf8.test_utf8 values ('阿');
 5Query OK, 1 row affected (0.00 sec)
 6
 7mysql> insert into test_utf8.test values ('阿');
 8Query OK, 1 row affected (0.08 sec)
 9
10mysql> insert into test_utf8.test_utf8 values ('阿');
11Query OK, 1 row affected (0.00 sec)

    共 11 行
  

为说明查询过程（包括插入与查询等操作）中的字符集关系，下面我们修改一下字符集：

    sql
    
1mysql> set names 'utf8';
2Query OK, 0 rows affected (0.00 sec)

    共 2 行

此时，我们可以看看环境变量：

    sql
    
      
      
    
  
 1mysql> show variables like 'char%';
 2+--------------------------+----------------------------+
 3| Variable_name            | Value                      |
 4+--------------------------+----------------------------+
 5| character_set_client     | utf8                       |
 6| character_set_connection | utf8                       |
 7| character_set_database   | utf8                       |
 8| character_set_filesystem | binary                     |
 9| character_set_results    | utf8                       |
10| character_set_server     | utf8                       |
11| character_set_system     | utf8                       |
12| character_sets_dir       | /usr/share/mysql/charsets/ |
13+--------------------------+----------------------------+
148 rows in set (0.29 sec)

    共 14 行
  

其中的客户端（character_set_client）、连接（character_set_connection）、结果（character_set_results）都变成了utf8。

然后，我们再向四个表中分别插入一条记录，还是插入单个汉字“阿”：

    sql
    
      
      
    
  
 1mysql> insert into test_utf8.test values ('阿');
 2Query OK, 1 row affected, 1 warning (0.00 sec)
 3
 4mysql> insert into test_utf8.test_utf8 values ('阿');
 5Query OK, 1 row affected (0.00 sec)
 6
 7mysql> insert into test_utf8.test values ('阿');
 8Query OK, 1 row affected (0.00 sec)
 9
10mysql> insert into test_utf8.test_utf8 values ('阿');
11Query OK, 1 row affected, 1 warning (0.00 sec)

    共 11 行
  

最后，我们分别查询每个表中的数据（包括其长度与十六进制值）：

    sql
    
      
      
    
  
 1mysql> select test, length(test), hex(test) from test_utf8.test;
 2+--------+--------------+-----------+
 3| test   | length(test) | hex(test) |
 4+--------+--------------+-----------+
 5| é˜¿    | 3            | E998BF    |
 6| ?      | 1            | 3F        |
 7+--------+--------------+-----------+
 82 rows in set (0.75 sec)
 9
10mysql> select test, length(test), hex(test) from test_utf8.test_utf8;
11+--------+--------------+--------------+
12| test   | length(test) | hex(test)    |
13+--------+--------------+--------------+
14| é˜¿    | 6            | C3A9CB9CC2BF |
15| 阿     | 3            | E998BF       |
16+--------+--------------+--------------+
172 rows in set (0.03 sec)
18
19mysql> select test, length(test), hex(test) from test_utf8.test;
20+--------+--------------+--------------+
21| test   | length(test) | hex(test)    |
22+--------+--------------+--------------+
23| é˜¿    | 6            | C3A9CB9CC2BF |
24| 阿     | 3            | E998BF       |
25+--------+--------------+--------------+
262 rows in set (0.00 sec)
27
28mysql> select test, length(test), hex(test) from test_utf8.test_utf8;
29+--------+--------------+-----------+
30| test   | length(test) | hex(test) |
31+--------+--------------+-----------+
32| é˜¿    | 3            | E998BF    |
33| ?      | 1            | 3F        |
34+--------+--------------+-----------+

    共 34 行
  

可以看出，造成存储结果差异的不是数据库的字符集，而是表的字符集（注：其实应该是字段的字符集，不过由于示例中没有指定字段的字符集，所以字段缺省使用了表的字符集）。

另外，还可以看到，在第一次插入数据（环境为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）。

对于情况二，由于数据库里存入的是问号，因此无论查询时使用何种配置都无法正确读出结果。而对于情况一，可以采用如下的方式将结果转换后读出：

    sql
    
1mysql> select convert(cast(convert(test using utf8) as binary)
2-> using utf8) from test_utf8.test_utf8 limit 1;
3+----------------------------------------------------------------+
4| convert(cast(convert(test using utf8) as binary) using utf8)   |
5+----------------------------------------------------------------+
6| 阿                                                             |
7+----------------------------------------------------------------+
81 row in set (0.04 sec)

    共 8 行

这里经过三次转换：第一次将utf8表中的内容转为utf8，第二次将该字符串转为二进制格式，第三次将该二进制串以utf8格式转出。具体转换过程可参考：

    sql
    
      
      
    
  
 1mysql> select
 2->
 3-> test as s1,
 4-> hex(test) as h1,
 5-> charset(test) as c1,
 6->
 7-> convert(test using utf8) as s2,
 8-> hex(convert(test using utf8)) as h2,
 9-> charset(convert(test using utf8)) as c2
10->
11-> from test_utf8.test_utf8 limit 1;
12+--------+--------------+------+--------+--------+--------+
13| s1     | h1           | c1   | s2     | h2     | c2     |
14+--------+--------------+------+--------+--------+--------+
15| é˜¿    | C3A9CB9CC2BF | utf8 | é˜¿    | E998BF | utf8   |
16+--------+--------------+------+--------+--------+--------+
171 row in set (0.00 sec)
18
19mysql>
20mysql> select
21->
22-> cast(convert(test using utf8) as binary) as s3,
23-> hex(cast(convert(test using utf8) as binary)) as h3,
24-> charset(cast(convert(test using utf8) as binary)) as c3,
25->
26-> convert(cast(convert(test using utf8) as binary) using utf8) as s4,
27-> hex(convert(cast(convert(test using utf8) as binary) using utf8)) as h4,
28-> charset(convert(cast(convert(test using utf8) as binary) using utf8)) as c4
29->
30-> from test_utf8.test_utf8 limit 1;
31+------+--------+--------+------+--------+------+
32| s3   | h3     | c3     | s4   | h4     | c4   |
33+------+--------+--------+------+--------+------+
34| 阿   | E998BF | binary | 阿   | E998BF | utf8 |
35+------+--------+--------+------+--------+------+
361 row in set (0.00 sec)

    共 36 行
  

搜索文章