字符编码在MySQL中的应用

 

使用 MySQL 数据库涉及到字符编码的问题。早期的 MySQL 表没有字符集的概念，其字符串实际上都仅是字节序列。现在我们使用的 4.1.x 系列的 MySQL 已经非常清晰地定义了字符集的作用。字符集的设置作用于两个方面：一是存储的数据，二是数据库查询的字符转换。

每一个文本字段（char,varchar,text）都分别有一个 character set 和 collation 属性。前者指定该文本字段所包含的值（这是一个字符串）储存在磁盘上时所使用的编码。在编码的选择上可以考虑两方面的因素：一是存储所占空间（UTF-8编码可以表示任意字符，但是汉字通常需用3个字节表示；UCS-2用两个字节表示汉字，但是也用2个字节表示英文字母；因此相对来说GBK编码对于主要存储汉字的应用来说比较经济），二是查询时的转码开销。collation属性指定字符串比较的特性，这影响到字符串相等判断和排序。在此不累述。

每一个表也分别有一个 character set 和 collation 属性。当没有显示指定文本字段的字符集时，采用表的字符集。

以下语句建立一个默认gbk编码的表，其中一个字段显式地指定为utf-8编码。

CREATE TABLE t (
a char(8) character set utf8,
b char(8)
) DEFAULT CHARSET=gbk;

用 SHOW FULL COLUMNS FROM t 命令查看该表的实际结构：

+-------+---------+-----------------+------+-----+---------+-------+
| Field | Type | Collation | Null | Key | Default | Extra |
+-------+---------+-----------------+------+-----+---------+-------+
| a | char(8) | utf8_general_ci | YES | | NULL | |
| b | char(8) | gbk_chinese_ci | YES | | NULL | |
+-------+---------+-----------------+------+-----+---------+-------+

MySQL 中使用到字符编码的另一个方面是查询时的字符转换。每个数据库连接附有一个字符集属性，使用该连接执行任何查询时，MySQL都会将文本字段的值（这是一个字符串）按该连接字符集映射成字节序列。可以通过以下查询指定连接的字符集：

SET NAMES gbk;

如果使用 MySQL Connector/J (com.mysql.jdbc.Driver)，也可以通过连接URL中的 characterEncoding 参数指定连接字符集，形如：

jdbc:mysql://localhost:3306/dbname?characterEncoding=GBK

这样，同一连接接下的查询中，字符串就都会以gbk编码的字节序列返回。这个命令通常是必须的，因为MySQL默认的连接编码是latin1，因此如果不设置连接字符集就直接查询文本字段的值，那么MySQL会企图用latin1编码映射字符，这时汉字都不能读出。

与此类似的MySQL的另一个至关重要的特性是，它在字段赋值时会自动将源字段的文本按目标字段的字符集编码，即使目标字符集无法映射源字符串也不报错（最新的MySQL会产生Warning，但是一来MySQL表通常不支持事务，二来对于ALTER TABLE之类的操作事务是无效的）。这个特性在移植数据表示可能导致严重后果。参考下面的例子：

假设一个现有的MySQL表使用latin1编码来储存文本（早期的MySQL表不支持字符集，那么默认为latin1；即使对于支持字符集的MySQL版本，仍有不少现有的程序能够“成功”地用latin1编码储存汉字）：

mysql> show full columns from t;

+-------+--------------+-------------------+------+-----+---------+-------+
| Field | Type | Collation | Null | Key | Default | Extra |
+-------+--------------+-------------------+------+-----+---------+-------+
| a | varchar(100) | latin1_swedish_ci | YES | | NULL | |
+-------+--------------+-------------------+------+-----+---------+-------+

这个字段 a 中实际上存储的是汉字的gbk编码字节序列。一些应用程序可以以latin1编码读出字段的值，然后显示（通常这些程序并不在乎字符集；字符的解码交给终端或IE来做）。但是这种方法对最新的 MySQL J/Connector (com.mysql.jdbc.Driver) 不适用，因此我想把这个字段转换为gbk编码的。

下面这个是*错误*的做法：

ALTER TABLE t MODIFY a varchar(100) character set gbk;

根据这个查询，MySQL会将 a 字段中原先的字符串（它以为是一个字节一个拉丁字符，但实际上我们存储了gbk编码的字节序列）按gbk编码映射为新的字节序列。这时原先用来表示一个汉字的两个字节就被当作两个汉字来映射，但是由于gbk编码中没有对 >0x80 的单字节的映射，因此原字段中所有的中文都被映射为 ? 。而且无法挽救。

正确的做法是，依次执行以下两个查询：

ALTER TABLE t MODIFY a blob;
ALTER TABLE t MODIFY a varchar(100) character set gbk;

第一句话让MySQL保留原字段的字节序列，但是清除字符集标记。后一句话给字段加上新的字符集标记，并保留字节序列不动。

在两个不同字符集的字段间赋值也存在类似上面的注意事项。