MySQL 5.1参考手册 :: 10. 字符集支持(2)

10.3.7. 字符串文字字符集和校对

每一字符串字符文字有一个字符集和一个校对规则，它不能为空。

一个字符串文字可能有一个可选的字符集引介词和COLLATE子句：

[_charset_name]'string' [COLLATE collation_name]

例如：

SELECT 'string';

SELECT _latin1'string';

SELECT _latin1'string' COLLATE latin1_danish_ci;

对于简单的语句SELECT 'string'，字符串使用由character_set_connection和collation_connection系统变量定义的字符集和 校对规则。

_charset_name表达式正式称做一个引介词。它告诉解析程序，“后面将要出现的字符串使用字符集X。”因为以前人们对此感到困惑，我们强调引介词不导致任何转换; 它仅是一个符号，不改变字符串的值。引介词在标准十六进制字母和数字十六进制符号（x'literal'和 0xnnnn）中是合法的，以及？（当在一个编程语言接口中使用预处理的语句时进行参数替换）。

例如：

SELECT _latin1 x'AABBCC';

SELECT _latin1 0xAABBCC;

SELECT _latin1 ?;

MySQL这样确定一个文字字符集和校对规则：

·         如果指定了CHARACTER SET X和COLLATE Y，那么使用CHARACTER SET X和COLLATE Y。

·         如果指定了CHARACTER SET X而没有指定COLLATE Y，那么使用CHARACTER SET X和CHARACTER SET X的默认校对规则。

·         否则，使用通过character_set_connection 和 collation_connection系统变量给出的字符集和 校对规则。

例如：

·         使用latin1字符集和latin1_german1_ci校对规则的字符串：

·                SELECT _latin1'Müller' COLLATE latin1_german1_ci;

·         使用latin1字符集和其默认校对规则的字符串（即，latin1_swedish_ci）：

·                SELECT _latin1'Müller';

·         使用连接默认字符集和校对规则的字符串：

·                SELECT 'Müller';

字符集引介词和COLLATE子句是根据标准SQL规范实现的。

10.3.8. 在SQL语句中使用COLLATE

• 使用COLLATE子句，能够为一个比较覆盖任何默认校对规则。COLLATE可以用于多种SQL语句中。下面是一些例子：

  ·         使用ORDER BY：

  ·                SELECT k

  ·                FROM t1

  ·                ORDER BY k COLLATE latin1_german2_ci;

  ·         使用AS：

  ·                SELECT k COLLATE latin1_german2_ci AS k1

  ·                FROM t1

  ·                ORDER BY k1;

  ·         使用GROUP BY：

  ·                SELECT k

  ·                FROM t1

  ·                GROUP BY k COLLATE latin1_german2_ci;

  ·         使用聚合函数：

  ·                SELECT MAX(k COLLATE latin1_german2_ci)

  ·                FROM t1;

  ·         使用DISTINCT：

  ·                SELECT DISTINCT k COLLATE latin1_german2_ci

  ·                FROM t1;

  ·         使用WHERE：

  ·                     SELECT *

  ·                     FROM t1

  ·                     WHERE _latin1 'Müller' COLLATE latin1_german2_ci = k;

  ·                     SELECT *

  ·                     FROM t1

  ·                     WHERE k LIKE _latin1 'Müller' COLLATE latin1_german2_ci;

  ·         使用HAVING：

  ·                SELECT k

  ·                FROM t1

  ·                GROUP BY k

  ·                HAVING k = _latin1 'Müller' COLLATE latin1_german2_ci;

10.3.9. COLLATE子句优先

COLLATE子句有较高的优先级（高于||），因此下面两个表达式是等价的：

x || y COLLATE z

x || (y COLLATE z)

10.3.10. BINARY操作符

BINARY操作符是COLLATE子句的一个速记符。BINARY 'x'等价与'x' COLLATE y，这里y是字符集'x'二元 校对规则的名字。每一个字符集有一个二元校对规则。例如，latin1字符集的二元 校对规则是latin1_bin，因此，如果列a是字符集latin1，以下两个语句有相同效果：

SELECT * FROM t1 ORDER BY BINARY a;

SELECT * FROM t1 ORDER BY a COLLATE latin1_bin;

10.3.11. 校对确定较为复杂的一些特殊情况

在绝大多数查询中，MySQL使用哪种校对规则进行比较是很显然的。例如，在下列情况中，校对规则明显的是“列x的列校对规则”：

SELECT x FROM T ORDER BY x;

SELECT x FROM T WHERE x = x;

SELECT DISTINCT x FROM T;

但是，当涉及多个操作数时，可能不明确。例如：

SELECT x FROM T WHERE x = 'Y';

这个查询应该使用列x的 校对规则，还是字符串文字'Y'的 校对规则？

标准化SQL使用“可压缩性”规则解决这种问题。基本上，这个意思是：既然x和'Y'都有 校对规则，哪个校对规则优先？这可能比较难解决，但是以下规则适合大多数情况：

·         一个外在的COLLATE子句可压缩性是0（根本不能压缩。）

·         使用不同校对规则的两个字符串连接的可压缩性是1。

·         列校对规则的可压缩性是2。

·         “系统常数”（如USER()或VERSION()函数返回的字符串）可压缩性是3。

·         文字规则的可压缩性是4。

·         NULL或从NULL派生的表达式的可压缩性是 5。

上述可压缩性值是MySQL5.1当前所用的。

这样上述规则可以模糊解决：

·         使用最低的可压缩性值的校对规则。

·         如果两侧有相同的可压缩性，那么如果校对规则不同则发生错误。

例如：

column1 = 'A'	使用column1的校对规则
column1 = 'A' COLLATE x	使用'A'的校对规则
column1 COLLATE x = 'A' COLLATE y	错误

使用COERCIBILITY（）函数确定一个字符串表达式的可压缩性：

mysql> SELECT COERCIBILITY('A' COLLATE latin1_swedish_ci);

        -> 0

mysql> SELECT COERCIBILITY(VERSION());

        -> 3

mysql> SELECT COERCIBILITY('A');

        -> 4

见12.9.3节，“信息函数”。

没有系统常数或可忽略的压缩性。函数如USER()的可压缩性是2而不是3，文字的可压缩性是3而不是4。

10.3.12. 校对必须适合字符集

请注意每个字符集有一个或多个校对规则，并且每个校对规则只能属于一个字符集。因此，以下语句会产生一个错误信息，因为校对规则latin2_bin对于字符集latin1非法：

mysql> SELECT _latin1 'x' COLLATE latin2_bin;

ERROR 1251: COLLATION 'latin2_bin' is not valid

for CHARACTER SET 'latin1'

10.3.13. 校对效果的示例

假设表T中的列X有这些latin1列值：

Muffler

Müller

MX Systems

MySQL

假设使用下面的语句获取列值：

SELECT X FROM T ORDER BY X COLLATE collation_name;

使用不同校对规则的列值结果排序见下表：

latin1_swedish_ci	latin1_german1_ci	latin1_german2_ci
Muffler	Muffler	Müller
MX系统	Müller	Muffler
Müller	MX系统	MX系统
MySQL	MySQL	MySQL

本表显示了我们在ORDER BY字句中使用不同所校对规则的效果的示例。在本例中导致不同排序的字符是上面带有两个圆点的U（ü），它在德语中发音为"U-umlaut"。

·         第一列显示的是使用瑞典/芬兰校对规则的SELECT语句的结果，它被称作U-umlaut使用Y排序。

·         第二列显示的是使用德语DIN-1校对规则的SELECT语句的结果，它被称作U-umlaut使用U排序。

·         第三列显示的是使用德语DIN-2校对规则的SELECT语句的结果，它被称作U-umlaut使用UE排序。

10.4. 字符集支持影响到的操作

10.4.1. 结果字符串

10.4.2. CONVERT()

10.4.3. CAST()

10.4.4. SHOW语句

本节讨论在MySQL5.1中考虑到字符集信息的操作。

10.4.1. 结果字符串

MySQL中有许多操作符和函数可以返回字符串。本节回答这个问题：返回的字符串使用什么字符集和 校对规则？

对于简单的函数，即接收字符串输入然后返回一个字符串结果作为输出的函数，输出的字符集和校对规则与原始输入的相同。例如，UPPER（X）返回一个字符串，其字符和 校对规则与X相同。类似的函数还有INSTR()、LCASE()、LOWER()、LTRIM()、MID()、REPEAT()、REPLACE()、REVERSE()、RIGHT()、RPAD()、RTRIM()、SOUNDEX()、SUBSTRING()、TRIM()、UCASE()和UPPER()。（还需要注意：REPLACE()函数不同于其它函数，它总是忽略输入字符串的 校对规则，并且进行大小写不敏感的比较。）

对于合并多个字符串输入并且返回单个字符串输出的运算，应用标准SQL“聚合规则”：

·         如果存在显式的校对规则X，那么使用X。

·         如果存在显式的校对规则X和Y，那么产生一个错误。

·         否则，如果全部校对规则是X，那么使用X。

·         其它情况，结果没有校对规则。

例如，使用CASE ... WHEN a THEN b WHEN b THEN c COLLATE X END。结果校对规则是X。对于CASE、UNION、||、CONCAT()、ELT()、GREATEST()、IF()和LEAST()情况相同。

对于转换为字符数据的运算，从运算得到的结果字符串的字符集和校对规则由character_set_connection和collation_connection系统变量定义。这适用于CAST()、CHAR()、CONV()、FORMAT()、HEX()和SPACE()函数。

10.4.2. CONVERT()

CONVERT（）提供一个在不同字符集之间转换数据的方法。语法是：

CONVERT(expr USING transcoding_name)

在 MySQL中，转换代码名与相应的字符集名相同。

例子：

SELECT CONVERT(_latin1'Müller' USING utf8);

INSERT INTO utf8table (utf8column)

    SELECT CONVERT(latin1field USING utf8) FROM latin1table;

CONVERT(... USING ...)根据标准SQL规范实施。

在传统SQL模式中，如果你转换一个“0”日期字符串到日期类型，CONVERT（）函数返回NULL。在MySQL5.1中还产生一条警告。

10.4.3. CAST()

你也可以使用CAST()函数将一个字符串转换到一个不同的字符集。语法是：

CAST(character_string AS character_data_type CHARACTER SET charset_name)

例如：

SELECT CAST(_latin1'test' AS CHAR CHARACTER SET utf8);

如果使用CAST()时没有指定CHARACTER SET，结果字符集和校对规则通过character_set_connection 和 collation_connection系统变量定义。如果用CAST()并带有CHARACTER SET X选项，那么结果字符集和校对规则是X和其 默认的校对规则。

你可能不能在CAST()中使用COLLATE子句，但是你可以在外部使用它。也就是说，不是CAST(... COLLATE ...)，而是CAST(...) COLLATE ...。

例如：

SELECT CAST(_latin1'test' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin;

在传统SQL模式中，如果你转换一个“0”日期字符串到日期类型，CAST()函数返回NULL。在MySQL5.1中还产生一条警告。

10.4.4. SHOW语句

一些SHOW语句提供额外的字符集信息。这些语句包括SHOW CHARACTER SET、SHOW COLLATION、SHOW CREATE DATABASE、SHOW CREATE TABLE和SHOW COLUMNS。

SHOW CHARACTER SET命令显示全部可用的字符集。它带有一个可选的LIKE子句来指示匹配哪些字符集名。例如：

mysql> SHOW CHARACTER SET LIKE 'latin%';

+---------+-----------------------------+-------------------+--------+

| Charset | Description                 | Default collation | Maxlen |

+---------+-----------------------------+-------------------+--------+

| latin1  | cp1252 West European        | latin1_swedish_ci |      1 |

| latin2  | ISO 8859-2 Central European | latin2_general_ci |      1 |

| latin5  | ISO 8859-9 Turkish          | latin5_turkish_ci |      1 |

| latin7  | ISO 8859-13 Baltic          | latin7_general_ci |      1 |

+---------+-----------------------------+-------------------+--------+

见13.5.4.1节，“SHOW CHARACTER SET语法”。

SHOW COLLATION语句的输出包括全部可用的字符集。它带有一个可选的LIKE子句来指示匹配哪些 校对规则名。例如：

mysql> SHOW COLLATION LIKE 'latin1%';

+-------------------+---------+----+---------+----------+---------+

| Collation         | Charset | Id | Default | Compiled | Sortlen |

+-------------------+---------+----+---------+----------+---------+

| latin1_german1_ci | latin1  |  5 |         |          |       0 |

| latin1_swedish_ci | latin1  |  8 | Yes     | Yes      |       0 |

| latin1_danish_ci  | latin1  | 15 |         |          |       0 |

| latin1_german2_ci | latin1  | 31 |         | Yes      |       2 |

| latin1_bin        | latin1  | 47 |         | Yes      |       0 |

| latin1_general_ci | latin1  | 48 |         |          |       0 |

| latin1_general_cs | latin1  | 49 |         |          |       0 |

| latin1_spanish_ci | latin1  | 94 |         |          |       0 |

+-------------------+---------+----+---------+----------+---------+

见13.5.4.2节，“SHOW COLLATION语法”。

SHOW CREATE DATABASE语句显示创建给定数据库的CREATE DATABASE语句。结果包括全部数据库选项。支持DEFAULT CHARACTER SET和COLLATE。全部数据库选项存储在命名为db.Opt的文本文件中，该文件能够在数据库目录中找到。

mysql> SHOW CREATE DATABASE test;

+----------+-----------------------------------------------------------------+

| Database | Create Database                                                 |

+----------+-----------------------------------------------------------------+

| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ |

+----------+-----------------------------------------------------------------+

见13.5.4.4节，“SHOW CREATE DATABASE语法”

SHOW CREATE TABLE与SHOW CREATE DATABASE相似，但是显示创建给定数据库的CREATE TABLE语句。列定义显示任何字符集规格，并且表选项包括字符集信息。

见13.5.4.5节，“SHOW CREATE TABLE语法”

当以SHOW FULL COLUMNS调用时，SHOW COLUMNS语句显示表中列的校对规则。具有CHAR、VARCHAR或TEXT数据类型的列有非NULL的 校对规则。数值列和其它非字符类型的列有NULL校对规则。例如：

mysql> SHOW FULL COLUMNS FROM person\G

*************************** 1. row ***************************

     Field: id

      Type: smallint(5) unsigned

 Collation: NULL

      Null: NO

       Key: PRI

   Default: NULL

     Extra: auto_increment

Privileges: select,insert,update,references

   Comment:

*************************** 2. row ***************************

     Field: name

      Type: char(60)

 Collation: latin1_swedish_ci

      Null: NO

       Key:

   Default:

     Extra:

Privileges: select,insert,update,references

   Comment:

字符集不是显示的部分。（字符集名隐含在校对规则名中。）

见13.5.4.3节，“SHOW COLUMNS语法”。

10.5. Unicode支持

MySQL 5.1支持两种字符集以保存Unicode数据：

·         ucs2，UCS-2 Unicode字符集。

·         utf8，Unicode字符集的UTF8编码。

在UCS-2（二进制Unicode表示法）中，每一个字符用一个双字节的Unicode编码来表示的，第一个字节表示重要的意义。例如："LATIN CAPITAL LETTER A"的Unicode编码是0x0041，它按顺序存储为两个字节：0x00 0x41。"CYRILLIC SMALL LETTER YERU"（Unicode 0x044B）顺序存储为两个字节：0x04 0x4B。对于Unicode字符和它们的编码，请参见。

当前，UCS-2还不能够用作为客户端字符集，这意味着SET NAMES 'ucs2'不起作用。

UTF8字符集（转换Unicode表示）是存储Unicode数据的一种可选方法。它根据 RFC 3629执行。UTF8字符集的思想是不同Unicode字符采用变长字节序列编码：

·         基本拉丁字母、数字和标点符号使用一个字节。

·         大多数的欧洲和中东手写字母适合两个字节序列：扩展的拉丁字母（包括发音符号、长音符号、重音符号、低音符号和其它音符）、西里尔字母、希腊语、亚美尼亚语、希伯来语、阿拉伯语、叙利亚语和其它语言。

·         韩语、中文和日本象形文字使用三个字节序列。

RFC 3629说明了采用一到四个字节的编码序列。当前，MySQLUTF8不支持四个字节。（UTF8编码的旧标准是由RFC 2279给出，它描述了从一到六个字节的UTF8编码序列。RFC 3629补充了作废的RFC 2279；因此，不再使用5个字节和6个字节的编码序列。）

提示：使用UTF8时为了节省空间，使用VARCHAR而不要用CHAR。否则，MySQL必须为一个CHAR(10) CHARACTER SET utf8列预备30个字节，因为这是可能的最大长度。

10.6. 用于元数据的UTF8

元数据是“关于数据的数据”。描述数据库的任何数据—作为数据库内容的对立面—是元数据。因此，列名、数据库名、用户名、版本名以及从SHOW语句得到的结果中的大部分字符串是元数据。还包括INFORMATION_SCHEMA数据库中的表中的内容，因为定义的那些表存储关于数据库对象的信息。

元数据表述必须满足这些需求：

·         全部元数据必须在同一字符集内。否则，对INFORM一个TION_SCHEMA数据库中的表执行的SHOW命令和SELECT查询不能正常工作，因为这些运算结果中的同一列的不同行将会使用不同的字符集。

·         元数据必须包括所有语言的所有字符。否则，用户将不能够使用它们自己的语言来命名列和表。

为了满足这两个需求，MySQL使用Unicode字符集存储元数据，即UTF8。如果你从不使用重音字符，这不会导致任何破坏。但如果你使用重音字符，应该注意的是元数据是用UTF8存储。

这意味着，USER()、CURRENT_USER()、DATABASE()和VERSION()函数的返回值被 默认设置为UTF8字符集，这与同义函数如SESSION_USER() 和SYSTEM_USER()的结果相同。

服务器将character_set_system系统变量设置为元数据字符集的名：

mysql> SHOW VARIABLES LIKE 'character_set_system';

+----------------------+-------+

| Variable_name        | Value |

+----------------------+-------+

| character_set_system | utf8  |

+----------------------+-------+

存储元数据使用Unicode并不意味着列头和DESCRIBE函数的结果默认在character_set_system字符集中。当你使用SELECT column1 FROM t语句时，名字为column1的列从服务器返回客户端并使用由SET NAMES语句确定的字符集。更明确地说，使用的字符集是由character_set_results系统变量的值确定的。如果这个系统变量设置为NULL，不执行字符转换，服务器使用最初的字符集（字符集由character_set_system系统变量设置）返回元数据。

如果你希望服务器不使用UTF8字符集返回元数据结果，那么使用SET NAMES语句强制服务器执行字符集转换（见10.3.6节，“连接字符集和校对”），或者在客户端执行转换。在客户端执行转换效率较高，但这种选项并不能使用于全部客户端。

如果你正在一个语句中使用（例如）USER()函数进行比较或赋值，不要担心。MySQL为你执行一些原子转换。

SELECT * FROM Table1 WHERE USER() = latin1_column;

这是可以的，因为在比较之前latin1_column列的内容会自动转换到UTF8。

INSERT INTO Table1 (latin1_column) SELECT USER();

这是可以的，因为赋值之前USER()函数返回的内容自动转换为latin1。至今，自动转换没有全部实施，但是以后的版本中应该工作正常。

尽管自动转换不属于SQL标准，SQL标准化文档中说每一个字符集是（根据支持的字符）Unicode的“子集”。因此，一个知名的原则是，“适用超集的字符集能够应用于其子集”，我们相信Unicode的 校对规则能够应用于非Unicode字符串的比较。

注释：在MySQL5.1中，errmsg.txt文件全部使用UTF8。客户端字符集的转换是自动进行的，如同元数据。

10.7. 与其它DBMS的兼容性

对于MaxDB兼容性，下面两个语句是相同的：

CREATE TABLE t1 (f1 CHAR(n) UNICODE);
CREATE TABLE t1 (f1 CHAR(n) CHARACTER SET ucs2);

10.8. 新字符集配置文件格式

字符集配置存储在XML文件中，一个字符集对应一个文件。