MySQL字符集

1.字符集和校对

字符集是一套符号和编码。校对规则是在字符集内用于比较字符的一套规则。让我们使用一个假想字符集的例子来区别清楚。

假设我们有一个字母表使用了四个字母：‘A’、‘B’、‘a’、‘b’。我们为每个字母赋予一个数值：‘A’=0，‘B’= 1，‘a’= 2，‘b’= 3。字母‘A’是一个符号，数字0是‘A’的编码，这四个字母和它们的编码组合在一起是一个字符集。

假设我们希望比较两个字符串的值：‘A’和‘B’。比较的最简单的方法是查找编码：‘A’为0，‘B’为1。因为0 小于1，我们可以说‘A’小于‘B’。我们做的仅仅是在我们的字符集上应用了一个 校对规则。校对规则是一套规则（在这种情况下仅仅是一套规则）：“对编码进行比较。”我们称这种全部可能的规则中的最简单的 校对规则为一个binary（二元）校对规则。

但是，如果我们希望小写字母和大写字母是等价的，应该怎样？那么，我们将至少有两个规则：（1）把小写字母‘a’和‘b’视为与‘A’和‘B’等价；（2）然后比较编码。我们称这是一个大小写不敏感的 校对规则。比二元校对规则复杂一些。

在实际生活中，大多数字符集有许多字符：不仅仅是‘A’和‘B’，而是整个字母表，有时候有许多种字母表，或者一个东方的使用上千个字符的书写系统，还有许多特殊符号和标点符号。并且在实际生活中，大多数 校对规则有许多个规则：不仅仅是大小写不敏感，还包括重音符不敏感（“重音符” 是附属于一个字母的符号，象德语的‘Ö’符号）和多字节映射（例如，作为规则‘Ö’=‘OE’就是两个德语 校对规则的一种）。

MySQL5.1能够做这些事情：

·         使用多种字符集来存储字符串

·         使用多种校对规则来比较字符串

·         在同一台服务器、同一个数据库或甚至在同一个表中使用不同字符集或校对规则来混合字符串

·         允许定义任何级别的字符集和校对规则


2.MySQL中的字符集和校对

MySQL服务器能够支持多种字符集。可以使用SHOW CHARACTER SET语句列出可用的字符集：
mysql> SHOW CHARACTER SET;

任何一个给定的字符集至少有一个校对规则。它可能有几个校对规则。

要想列出一个字符集的校对规则，使用SHOW COLLATION语句。例如，要想查看latin1（“西欧ISO-8859-1”）字符集的 校对规则，使用下面的语句查找那些名字以latin1开头的 校对规则：
mysql> SHOW COLLATION LIKE 'latin1%';

校对规则一般有这些特征：

·         两个不同的字符集不能有相同的校对规则。

·         每个字符集有一个默认校对规则。例如，latin1默认校对规则是latin1_swedish_ci。

·         存在校对规则命名约定：它们以其相关的字符集名开始，通常包括一个语言名，并且以_ci（大小写不敏感）、_cs（大小写敏感）或_bin（二元）结束。

字符集和校对规则有4个级别的默认设置：服务器级、数据库级、表级和连接级。


2.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系统变量的值。在运行时能够改变这些变量的值。


2.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）具有相同的值。


2.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中没有。


2.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。


2.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。


2.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;