10.3. 确定默认字符集和校对
- 10.3.1. 服务器字符集和校对
- 10.3.2. 数据库字符集和校对
- 10.3.3. 表字符集和校对
- 10.3.4. 列字符集和校对
- 10.3.5. 字符集和校对分配示例
- 10.3.6. 连接字符集和校对
- 10.3.7. 字符串文字字符集和校对
- 10.3.8. 在SQL语句中使用COLLATE
- 10.3.9. COLLATE子句优先
- 10.3.10. BINARY操作符
- 10.3.11. 校对确定较为复杂的一些特殊情况
- 10.3.12. 校对必须适合字符集
- 10.3.13. 校对效果的示例
10.3.1. 服务器字符集和校对
MySQL服务器有一个服务器字符集和一个服务器校对规则，它们均不能设置为空。MySQL按照如下方法确定服务器字符集和服务器校对规则：
·当服务器启动时根据有效的选项设置
·根据运行时的设定值
在服务器级别，确定方法很简单。当启动mysqld时，根据使用的初始选项设置来确定服务器字符集和校对规则。可以使用--default-character-set设置字符集，并且可以在字符集后面为校对规则添加--default-collation。如果没有指定一个字符集，那就与--default-character-set=latin1相同。如果你仅指定了一个字符集（例如，latin1），但是没有指定一个校对规则，那就与--default-charset=latin1--default-collation=latin1_swedish_ci相同，因为latin1_swedish_ci是latin1的默认校对规则。因此，以下三个命令有相同的效果：
shell> mysqld
shell> mysqld --default-character-set=latin1
shell> mysqld --default-character-set=latin1 \
--default-collation=latin1_swedish_ci
更改设定值的一个方法是通过重新编译。如果希望在从源程序构建时更改默认服务器字符集和校对规则，使用：--with-charset和--with-collation作为configure的参量。例如：
shell> ./configure --with-charset=latin1
或者：
shell> ./configure --with-charset=latin1 \
--with-collation=latin1_german1_ci
mysqld和configure都验证字符集/校对规则组合是否有效。如果无效，每个程序都显示一个错误信息，然后终止。
当前的服务器字符集和校对规则可以用作character_set_server和collation_server系统变量的值。在运行时能够改变这些变量的值。
10.3.2. 数据库字符集和校对
每一个数据库有一个数据库字符集和一个数据库校对规则，它不能够为空。CREATE DATABASE和ALTER DATABASE语句有一个可选的子句来指定数据库字符集和校对规则：CREATE DATABASE db_name
[[DEFAULT] CHARACTER SET charset_name]
[[DEFAULT] COLLATE collation_name]
ALTER DATABASE db_name
[[DEFAULT] CHARACTER SET charset_name]
[[DEFAULT] COLLATE collation_name]
例如：
CREATE DATABASE db_name
DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
MySQL这样选择数据库字符集和数据库校对规则：
·如果指定了CHARACTER SET X和COLLATE Y，那么采用字符集X和校对规则Y。
·如果指定了CHARACTER SET X而没有指定COLLATE Y，那么采用CHARACTER SET X和CHARACTER SET X的默认校对规则。
·否则，采用服务器字符集和服务器校对规则。
MySQL的CREATE DATABASE ... DEFAULT CHARACTER SET ...语法与标准SQL的CREATE SCHEMA ... CHARACTER SET ...语法类似。因此，可以在同一个MySQL服务器上创建使用不同字符集和校对规则的数据库。
如果在CREATE TABLE语句中没有指定表字符集和校对规则，则使用数据库字符集和校对规则作为默认值。它们没有其它目的。
默认数据库的字符集和校对规则可以用作character_set_database和 collation_database系统变量。无论何时默认数据库更改了，服务器都设置这两个变量的值。如果没有默认数据库，这两个变量与相应的服务器级别的变量（character_set_server和collation_server）具有相同的值。
10.3.3. 表字符集和校对
每一个表有一个表字符集和一个校对规则，它不能为空。为指定表字符集和校对规则，CREATE TABLE 和ALTER TABLE语句有一个可选的子句：CREATE TABLE tbl_name (column_list)
[DEFAULT CHARACTER SET charset_name [COLLATE collation_name]]
ALTER TABLE tbl_name
[DEFAULT CHARACTER SET charset_name] [COLLATE collation_name]
例如：
CREATE TABLE t1 ( ... )
DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;
MySQL按照下面的方式选择表字符集和校对规则：
·如果指定了CHARACTER SET X和COLLATE Y，那么采用CHARACTER SET X和COLLATE Y。
·如果指定了CHARACTER SET X而没有指定COLLATE Y，那么采用CHARACTER SET X和CHARACTER SET X的默认校对规则。
·否则，采用服务器字符集和服务器校对规则。
如果在列定义中没有指定列字符集和校对规则，则默认使用表字符集和校对规则。表字符集和校对规则是MySQL的扩展;在标准SQL中没有。
10.3.4. 列字符集和校对
每一个“字符”列（即，CHAR、VARCHAR或TEXT类型的列）有一个列字符集和一个列校对规则，它不能为空。列定义语法有一个可选子句来指定列字符集和校对规则：col_name {CHAR | VARCHAR | TEXT} (col_length)[CHARACTER SET charset_name [COLLATE collation_name]]
例如：
CREATE TABLE Table1
(
column1 VARCHAR(5) CHARACTER SET latin1 COLLATE latin1_german1_ci
);
MySQL按照下面的方式选择列字符集和校对规则：
·如果指定了CHARACTER SET X和COLLATE Y，那么采用CHARACTER SET X和COLLATE Y。
·如果指定了CHARACTER SET X而没有指定COLLATE Y，那么采用CHARACTER SET X和CHARACTER SET X的默认校对规则。
·否则，采用表字符集和服务器校对规则。
CHARACTER SET和COLLATE子句是标准的SQL。
10.3.5. 字符集和校对分配示例
以下例子显示了MySQL怎样确定默认字符集和校对规则。
示例1：表和列定义
CREATE TABLE t1
(
c1 CHAR(10) CHARACTER SET latin1 COLLATE latin1_german1_ci
) DEFAULT CHARACTER SET latin2 COLLATE latin2_bin;
在这里我们有一个列使用latin1字符集和latin1_german1_ci校对规则。是显式的定义，因此简单明了。需要注意的是，在一个latin2表中存储一个latin1列不会存在问题。
示例2：表和列定义
CREATE TABLE t1
(
c1 CHAR(10) CHARACTER SET latin1
) DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;
这次我们有一个列使用latin1字符集和一个默认校对规则。尽管它显得自然，默认校对规则却不是表级。相反，因为latin1的默认校对规则总是latin1_swedish_ci，列c1有一个校对规则latin1_swedish_ci（而不是latin1_danish_ci）。
示例3：表和列定义
CREATE TABLE t1
(
c1 CHAR(10)
) DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;
我们有一个列使用一个默认字符集和一个默认校对规则。在这种情况下，MySQL查找表级别来确定列字符集和校对规则。因此，列c1的字符集是latin1，它的校对规则是latin1_danish_ci。
示例4：数据库、表和列定义
CREATE DATABASE d1
DEFAULT CHARACTER SET latin2 COLLATE latin2_czech_ci;
USE d1;
CREATE TABLE t1
(
c1 CHAR(10)
);
我们创建了一个没有指定字符集和校对规则的列。我们也没有指定表级字符集和校对规则。在这种情况下，MySQL查找数据库级的相关设置。（数据库的设置变为表的设置，其后变为列的设置。）因此，列c1的字符集为是latin2，它的校对规则是latin2_czech_ci。
10.3.6. 连接字符集和校对
一些字符集和校对规则系统变量与客户端和服务器的交互有关。在前面的章节中已经提到过部分内容：·服务器字符集和校对规则可以用作character_set_server和collation_server变量的值。
·默认数据库的字符集和校对规则可以用作character_set_database和collation_database变量的值。
在客户端和服务器的连接处理中也涉及了字符集和校对规则变量。每一个客户端有一个连接相关的字符集和校对规则变量。
考虑什么是一个“连接”：它是连接服务器时所作的事情。客户端发送SQL语句，例如查询，通过连接发送到服务器。服务器通过连接发送响应给客户端，例如结果集。对于客户端连接，这样会导致一些关于连接的字符集和校对规则的问题，这些问题均能够通过系统变量来解决：
·当查询离开客户端后，在查询中使用哪种字符集？
服务器使用character_set_client变量作为客户端发送的查询中使用的字符集。
·服务器接收到查询后应该转换为哪种字符集？
转换时，服务器使用character_set_connection和collation_connection系统变量。它将客户端发送的查询从character_set_client系统变量转换到character_set_connection（除非字符串文字具有象_latin1或_utf8的引介词）。collation_connection对比较文字字符串是重要的。对于列值的字符串比较，它不重要，因为列具有更高的校对规则优先级。
·服务器发送结果集或返回错误信息到客户端之前应该转换为哪种字符集？
character_set_results变量指示服务器返回查询结果到客户端使用的字符集。包括结果数据，例如列值和结果元数据（如列名）。
你能够调整这些变量的设置，或可以依赖默认值（这样，你可以跳过本章）。
有两个语句影响连接字符集：
SET NAMES 'charset_name'
SET CHARACTER SET charset_name
SET NAMES显示客户端发送的SQL语句中使用什么字符集。因此，SET NAMES 'cp1251'语句告诉服务器“将来从这个客户端传来的信息采用字符集cp1251”。它还为服务器发送回客户端的结果指定了字符集。（例如，如果你使用一个SELECT语句，它表示列值使用了什么字符集。）
SET NAMES 'x'语句与这三个语句等价：
mysql> SET character_set_client = x;
mysql> SET character_set_results = x;
mysql> SET character_set_connection = x;
将x设置为character_set_connection也就设置了collation_connection是x的默认校对规则。
SET CHARACTER SET语句是类似的，但是为默认数据库设置连接字符集和校对规则。SET CHARACTER SET x语句与这三个语句等价：
mysql> SET character_set_client = x;
mysql> SET character_set_results = x;
mysql> SET collation_connection = @@collation_database;
当一个客户端连接时，它向服务器发送希望使用的字符集名称。服务器为那个字符集设置character_set_client、character_set_results和 character_set_connection变量。（实际上，服务器为使用该字符集执行一个SET NAMES操作。）
对于mysql客户端，如果你希望使用与默认字符集不同的字符集，不需要每次启动时执行SET NAMES语句。可以在mysql语句行中或者选项文件中添加一个--default-character-set选项设置。例如，你每次运行mysql时，以下的选项文件设置把三个字符集变量修改为koi8r：
[mysql]
default-character-set=koi8r
例如：假设column1定义为CHAR(5) CHARACTER SET latin2。如果没有设定SET NAMES或SET CHARACTER SET，那么对于SELECT column1 FROM t，当连接后，服务器使用客户端指定的字符集返回列column1的所有值。另一方面，如果你设定SET NAMES 'latin1'或SET CHARACTER SET latin1，那么发送结果之前，服务器转换latin2值到latin1。转换可能会丢失那些不属于两种字符集的字符。
如果不希望服务器执行任何转换，设置character_set_results为NULL：
mysql> SET character_set_results = NULL;
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排序。