MySQL Collation排序规则

一、MySQL Collation排序规则介绍

MySQL 中的排序规则是一组规则，用于比较特定字符集中的字符。它是任何特定集合的订单序列。MySQL 支持各种字符集，每个字符集总是使用一个或多个排序规则，至少一个默认排序规则。MySQL 不允许我们让任何两个字符集使用相同的排序规则。

我们可以使用以下语句查看 MySQL 数据库服务器中可用的所有默认字符集排序规则：

SHOW CHARACTER SET;

它将返回以下输出：

在此图像中，默认排序规则列值指定每个字符集的默认排序规则名称。

请注意，任何字符集的排序规则始终以字符集名称开头并以_cs（区分大小写）、_ci（不区分大小写）或 _bin（二进制）结尾。

默认情况下，上述语句（SHOW CHARACTER SET）在MySQL中产生所有可用的排序规则。如果我们想获取任何特定字符集的所有排序规则名称，请使用可选的LIKE 或WHERE 子句使用返回匹配字符集名称的 SHOW COLLATION 语句。

二、MySQL Collation排序规则语法

以下是用于查找特定字符集的排序规则名称的语法：

SHOW COLLATION LIKE 'character_set_name%';

以下语句显示 MySQL 中 latin1 字符集的所有排序规则名称：

SHOW COLLATION LIKE 'latin1%';

我们可以看到如下输出，其中每个字符集都具有默认排序规则，例如latin1_danish_ci是latin1字符集的默认排序规则：

排序规则还为我们提供了对字符串进行排序的功能。它基于权重执行字符排序。字符串中可用的每个字符都映射到一个权重。如果字符比较的权重相同，则称为相等。同样，如果字符的权重不同，它会根据它们的权重的相对大小进行比较。

MySQL 提供了WEIGHT_STRING()函数来查找字符串字符的权重。此函数将表示权重的值返回为二进制字符串。因此，我们需要使用HEX(WEIGHT_STRING(str))函数以可打印的形式显示权重。

让我们借助一个例子来理解它。以下语句采用不区分大小写的非二进制字符串“AaBbCc”，并返回给定字母没有差异的权重：

SELECT HEX(WEIGHT_STRING('AaBbCc' COLLATE latin1_swedish_ci)) AS Weight;

请参阅以下输出，其中权重 A=41=a、B=42=b 和 C=43=c 相等：

如果我们采用二进制字符串“AaBbCc”，那么它的权重对于给定的字母可能会有所不同：

SELECT HEX(WEIGHT_STRING(BINARY 'AaBbCc')) AS Weight;

它将给出如下输出，其中权重 A=41、a=61、B=42、b=62、C=43 和 c=63 不同：

三、设置字符集和排序规则

MySQL 允许我们通过四种方式配置字符集和排序规则，如下所示：

在服务器级别设置
在数据库级别设置
在表级别设置
在列级别设置

在服务器级别设置字符集和排序规则

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

如果我们在服务器启动时只设置一个字符集，MySQL 将使用指定字符集的默认排序规则。如果我们显式设置字符集和排序规则，MySQL 将对数据库服务器中创建的所有数据库使用这些设置。请参阅以下语句，该语句通过命令行工具将utf8指定为字符集，并将utf8_unicode_cs指定为服务器的排序规则：

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

四、在数据库级别设置字符集和排序规则

在 MySQL 中创建数据库时，无需设置字符集和排序规则。这是因为 MySQL 将使用服务器的默认字符集和排序规则来创建数据库。

我们可以在使用CREATE DATABASE创建数据库时修改数据库级别的默认设置或 ALTER DATABASE 语句。下面是使用 CREATE DATABASE 语句创建数据库的语法：

CREATE DATABASE database_name  
CHARACTER SET character_set_name;  
COLLATE collation_name

以下是使用 ALTER DATABASE 语句创建数据库的语法：

ALTER DATABASE database_name  
CHARACTER SET character_set_name  
COLLATE collation_name;

当我们在数据库级别指定字符集和排序规则时，MySQL 将其用于数据库中创建的所有表。

示例

此示例说明如何在数据库级别设置字符集和排序规则。

CREATE DATABASE demo_db  
CHARACTER SET utf8  
COLLATE utf8_unicode_ci;

在上面的语句中，我们明确指定了数据库的字符集和排序规则。因此，它不使用服务器级别的默认设置。

五、在表级别设置字符集和排序规则

数据库将包含带有字符集和排序规则的表。这些字符集和排序规则不同于默认设置。当使用CREATE TABLE 创建表时语句或使用ALTER TABLE更改表的结构语句，我们可以为特定表指定默认字符集和排序规则。

下面是使用 ALTER TABLE 语句创建数据库的语法。

CREATE TABLE table_name(  
   ...  
)  
CHARACTER SET character_set_name  
COLLATE collation_name

以下是使用 ALTER TABLE 语句创建数据库的语法：

ALTER TABLE table_name(  
  ...  
)  
CHARACTER SET character_set_name  
COLLATE collation_name

示例

此示例说明如何在表级别设置字符集和排序规则。

USE demodb;   
CREATE TABLE demotable1(  
Column1 char(25)  
);

在上面的例子中，我们没有在表级别指定字符集和排序规则。因此，它使用数据库字符集和排序规则。在这里，表 demotable1 将 utf8 作为字符集，将 utf8_unicode_ci 作为排序规则。

如果我们想将上表的字符集更改为 latin1 并将排序规则更改为 latin1_german1_ci，请使用以下语句：

ALTER TABLE demptable1  
CHARACTER SET latin1  
COLLATE latin1_german1_ci;

六、在列级别设置字符集和排序规则

列可以是不同的类型，例如CHAR、VARCHAR 或 TEXT。它可以有自己的字符集和排序规则，这与表的默认设置不同。

我们可以使用 CREATE TABLE 或 ALTER TABLE 语句更改列定义中列的字符集和排序规则设置，如下所示：

column_name [column_type] (length)  
CHARACTER SET character_set_name  
COLLATE collation_name

示例

此示例说明如何在列级别设置字符集和排序规则。

ALTER TABLE demotable1  
MODIFY column1 VARCHAR(25)  
CHARACTER SET utf8mb4  
COLLATION utf8mb4_0900_ai_ci;

在上面的示例中，我们在创建表时确定的列级别更改了字符集和排序规则。因此，它使用utf8mb4作为字符集，使用utf8mb4_0900_ai_ci作为排序规则。

七、设置字符集和排序规则的要点

以下是设置字符集和排序规则的要点或规则：

如果我们明确设置它们，我们可以在 MySQL 中同时使用字符集和排序规则。
如果我们设置唯一的字符集而不指定排序规则，MySQL 使用字符集的默认排序规则。
如果我们只设置排序规则而不指定字符集，MySQL 使用与排序规则关联的字符集。
如果我们没有同时指定字符集和排序规则，MySQL 将使用默认字符集和排序规则。

MySQL 字符集 MySQL 正则表达式