MySQL 5.1参考手册 :: 10. 字符集支持(2)
时间：2008-05-11 来源：sdccf
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字符和它们的编码，请参见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文件中，一个字符集对应一个文件。 相关阅读 更多 +
