MySQL - 排序规则

• 实现 MySQL 排序规则
• 设置字符集和排序规则
• 服务器级别
• 数据库级别
• 表级别
• 显示默认排序规则

MySQL 排序规则是一组用于决定如何比较和排序字符集中各种字符的规则。 MySQL 支持多种字符集，包括 ASCII、Unicode 系统、二进制等。

这些字符集中的每个字符都具有权重。MySQL 排序规则根据字符各自的权重对字符进行排序。例如，比较同一字符集中的两个字符时，如果一个字符的权重大于另一个字符，则该字符的权重较大;反之亦然。如果两个字符的权重相等，则它们相等。

每个字符集必须至少有一个(或更多)排序规则，并且任何两个字符集不能具有相同的排序规则。

实现 MySQL 排序规则

MySQL 实现了各种类型的排序规则来比较字符串 -

• 8 位字符集的简单排序规则

• 8 位字符集的复杂排序规则

• 非 Unicode 多字节字符集的排序规则

• Unicode 多字节字符集的排序规则

• 其他排序规则

每个字符集都有内置的二进制排序规则，因此无需重新定义。此类内置排序规则在任何情况下都不得修改，以免出现服务器意外行为。

8 位字符集的简单排序规则

• 由于 8 位字符集最多只能容纳 256 个字符，因此此类排序规则使用长度为 256 的权重数组实现。
• 字符集中的每个字符都与权重一一对应。
• 它不区分大小写，因此相同字符的大写和小写具有相同的权重。

8 位字符集的复杂排序规则

• 对于复杂的 8 位字符集，排序规则是通过使用函数定义字符顺序来实现的。
• 在这里，我们创建一个 C 源文件，用于指定字符集属性并定义执行以下函数所需的支持例程对该字符集进行正确的操作。

非 Unicode 多字节字符集的排序规则

• 与单字节(8 位)字符不同，多字节字符的代码和权重之间存在两种关系。
• 字符的权重等于其代码。
• 字符代码与权重一一映射，其中权重不一定等于代码。

Unicode 多字节字符集的排序规则

某些排序规则基于 Unicode 排序算法 (UCA)。它们具有以下属性 -

• 如果字符具有权重，则每个权重使用 2 个字节。
• 如果字符没有权重，则该字符可忽略。
• 单个字符可以具有多个权重。这称为扩展。例如，德语字母(SHARP S)的权重为 0x0FEA0FEA。
• 多个字符加在一起只能有一个权重。这称为收缩。例如，捷克语中的"ch"是一个字母，其权重为 0x0EE2。

其他排序规则

• 不属于上述任何类别的排序规则称为其他排序规则。

设置字符集和排序规则

MySQL 允许我们在三个不同级别设置字符集和排序规则。如下所示:

• 在服务器级别

• 在数据库级别

• 在表级别

在服务器级别

在 MySQL 中，字符集 latin1 将用作默认字符集。因此，默认排序规则为 latin1_swedish_ci。MySQL 允许我们在服务器启动时更改这些默认设置。

启动 MySQL 服务器时，如果我们指定了字符集，它将使用该字符集的默认排序规则。但是，如果我们明确指定字符集和排序规则，MySQL 将对所有后续创建的数据库使用该组合。

示例

在下面的查询中，我们将服务器的字符集设置为 utf8，排序规则设置为 utf8_unicode_cs。

mysqld --character-set-server=utf8 --collation-server=utf8_unicode_cs

如果将 --collat​​ion-server 设置为用户定义的排序规则名称，则会发出警告。

在数据库级别

创建数据库时，如果我们未提供任何字符集和排序规则，则数据库将使用服务器的默认字符集和排序规则。

我们可以使用 CREATE DATABASE 语句在数据库级别覆盖默认字符集和排序规则。

如果要覆盖现有数据库的默认设置，可以使用 ALTER DATABASE 语句。

语法

以下是在数据库级别覆盖默认设置的基本语法 -

[CREATE | ALTER] DATABASE database_name  
CHARACTER SET character_set_name 
COLLATE collation_name;

示例

此处，我们创建一个数据库，并使用以下查询指定字符集为 utf8，排序规则为 utf8_unicode_ci -

CREATE DATABASE testdb
CHARACTER SET utf8
COLLATE utf8_unicode_ci;

表级别

在 MySQL 中，数据库可能包含与数据库字符集和排序规则不同的表。

我们可以在使用 CREATE TABLE 语句创建表时指定默认字符集和排序规则。

如果要覆盖现有表的默认设置，可以使用 ALTER TABLE 语句。

语法

以下是使用 CREATE TABLE 语句为表指定默认字符集和排序规则的语法 -

[CREATE | ALTER] TABLE table_name
column_name datatype (length)
CHARACTER SET character_set_name
COLLATE collation_name

示例

在以下查询中，我们创建一个没有任何字符集和排序规则的表。因此，它使用数据库的字符集和排序规则。

CREATE TABLE CUSTOMERS(
   ID VARCHAR(45),
   NAME VARCHAR(45),
   AGE INT
);

现在，我们使用 ALTER TABLE 语句将字符集修改为"latin1"，并将排序规则修改为"latin_german_ci"。

ALTER TABLE CUSTOMERS
CHARACTER SET latin1
COLLATE latin1_german1_ci;

显示默认排序规则

我们可以使用 SHOW CHARACTER SET 查询显示 MySQL 数据库服务器中所有字符集的默认排序规则。

SHOW CHARACTER SET;

在最新版本的 MySQL 中，用户定义的排序规则已被弃用。因此，如果在任何 SQL 语句中使用它们，服务器都会发出警告。

每个字符集的排序规则字符串都以字符集名称开头，以 _ci(不区分大小写)、_cs(区分大小写)或 _bin(二进制)结尾。

MySQL LIKE 子句

在 MySQL 中，使用 LIKE 子句和 SHOW COLLATION 语句，我们可以指定一个模式来获取与给定模式匹配的排序规则的名称和其他信息。

SHOW COLLATION LIKE 'greek%';

输出

上述查询返回所有包含名称 greek 的排序规则。

MySQL WHERE 子句

我们可以将 WHERE 子句与 SHOW COLLATION 语句结合使用，以检索符合指定条件的排序规则名称。

SHOW COLLATION WHERE Charset = 'cp1251';

输出

上述查询返回所有字符集 ID 等于"cp1251"的排序规则。