一文了解Mysql排序规则

MySQL
MySQL
发布日期:   2024-12-21
文章字数:   2.2k
阅读时长:   9 分
阅读次数:  

简述

说起排序规则就离不开字符集，严格来说，排序规则是依赖于字符集的。

字符集是用来定义MySQL存储不同字符的方式，而排序规则一般指对字符集中字符串之间的比较、排序制定的规则。一种字符集可以对应多种排序规则，但是一种排序规则只能对应指定的一种字符集，两个不同的字符集不能有相同的排序规则。

mysql> show collation like 'utf8mb4%';
+------------------------+---------+-----+---------+----------+---------+
| Collation              | Charset | Id  | Default | Compiled | Sortlen |
+------------------------+---------+-----+---------+----------+---------+
| utf8mb4_general_ci     | utf8mb4 |  45 | Yes     | Yes      |       1 |
| utf8mb4_bin            | utf8mb4 |  46 |         | Yes      |       1 |
| utf8mb4_unicode_ci     | utf8mb4 | 224 |         | Yes      |       8 |
| utf8mb4_icelandic_ci   | utf8mb4 | 225 |         | Yes      |       8 |
| utf8mb4_latvian_ci     | utf8mb4 | 226 |         | Yes      |       8 |
| utf8mb4_romanian_ci    | utf8mb4 | 227 |         | Yes      |       8 |
| utf8mb4_slovenian_ci   | utf8mb4 | 228 |         | Yes      |       8 |
| utf8mb4_polish_ci      | utf8mb4 | 229 |         | Yes      |       8 |
| utf8mb4_estonian_ci    | utf8mb4 | 230 |         | Yes      |       8 |
| utf8mb4_spanish_ci     | utf8mb4 | 231 |         | Yes      |       8 |
| utf8mb4_swedish_ci     | utf8mb4 | 232 |         | Yes      |       8 |
| utf8mb4_turkish_ci     | utf8mb4 | 233 |         | Yes      |       8 |
| utf8mb4_czech_ci       | utf8mb4 | 234 |         | Yes      |       8 |
| utf8mb4_danish_ci      | utf8mb4 | 235 |         | Yes      |       8 |
| utf8mb4_lithuanian_ci  | utf8mb4 | 236 |         | Yes      |       8 |
| utf8mb4_slovak_ci      | utf8mb4 | 237 |         | Yes      |       8 |
| utf8mb4_spanish2_ci    | utf8mb4 | 238 |         | Yes      |       8 |
| utf8mb4_roman_ci       | utf8mb4 | 239 |         | Yes      |       8 |
| utf8mb4_persian_ci     | utf8mb4 | 240 |         | Yes      |       8 |
| utf8mb4_esperanto_ci   | utf8mb4 | 241 |         | Yes      |       8 |
| utf8mb4_hungarian_ci   | utf8mb4 | 242 |         | Yes      |       8 |
| utf8mb4_sinhala_ci     | utf8mb4 | 243 |         | Yes      |       8 |
| utf8mb4_german2_ci     | utf8mb4 | 244 |         | Yes      |       8 |
| utf8mb4_croatian_ci    | utf8mb4 | 245 |         | Yes      |       8 |
| utf8mb4_unicode_520_ci | utf8mb4 | 246 |         | Yes      |       8 |
| utf8mb4_vietnamese_ci  | utf8mb4 | 247 |         | Yes      |       8 |
+------------------------+---------+-----+---------+----------+---------+
26 rows in set (0.00 sec)

上图中，Collation 列表示排序方式，Charset 列表示字符集，可以看出 utf8mb4 字符集对应着许多的排序方式，排序方式那一列每一项的值都不一样，并且每一项都对应唯一一种字符集，在这里是 utf8mb4 字符集。

默认排序规则

字符集的默认排序规则

mysql> show character set like 'utf8mb4%';
+---------+---------------+--------------------+--------+
| Charset | Description   | Default collation  | Maxlen |
+---------+---------------+--------------------+--------+
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci |      4 |
+---------+---------------+--------------------+--------+
1 row in set (0.00 sec)

每种字符集都有一个默认的排序规则, 可以通过下面的SQL语句查询指定字符集的默认排序规则

上面的例子是查询字符集前缀包含utf8mb4的默认排序方式，从中可以得知：

utf8mb4 字符集的默认排序方式是 utf8mb4_general_ci 字符集中字符最大长度占4个字节

数据库的默认排序规则

MySQL服务器的默认字符集可以在 /etc/my.cnf 配置中的 [mysqld] 下修改

例如：现需要把MySQL服务器的默认字符集设置为 utf8mb4, 默认排序规则设置为 utf8mb4_general_ci, 只需要在 /etc/my.cnf 配置文件的 [mysqld] 下添加以下子项

character-set-server=utf8mb4 
collation-server=utf8mb4_general_ci

创建数据库的时候如果没有指定字符集，会使用MySQL服务器默认字符集和默认排序规则

假如: 在下面例子中，MySQL服务器的默认字符集和默认排序规则分别是 utf8mb4 和 utf8mb4_general_ci

mysql> create database test_a;
Query OK, 1 row affected (0.00 sec)

mysql> select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME from INFORMATION_SCHEMA.SCHEMATA where SCHEMA_NAME='test_a'; 
+-------------+----------------------------+------------------------+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+-------------+----------------------------+------------------------+
| test_a      | utf8mb4                    | utf8mb4_general_ci     |
+-------------+----------------------------+------------------------+
1 row in set (0.00 sec)

在上面的例子中，创建了 test_a 数据库，创建数据库的时候没有为数据库指定字符集和排序规则，此时会使用 MySQL服务器的默认字符集和排序规则

通过SQL语句查询 test_a 数据库的默认字符集和默认排序规则，结果和MySQL服务器的默认字符集和默认排序规则是一样的

排序规则命名以及名字后缀

命名

排序规则的命名是以和它自身关联的字符集名字开头的，后面再接一个或多个后缀来表示指定字符集的一种排序规则

例如：utf8mb4_general_ci 和 utf8mb4_bin 就是 utf8mb4字符集的两种排序规则, latin1_swedish_ci 是 latin1字符集的排序规则

注意： binary 字符集只有一种排序规则,并且它的排序规则名字和字符集名字一样, 也是 binary

后缀

排序规则名字的后缀是有特殊意义的，根据后缀可以知道排序规则是否区分大小写，是否区分重音，是否是二进制等等，下面列出了部分后缀的说明

_ci : 不区分大小写, Case-insensitive的缩写 
 
_cs : 区分大小写，Case-sensitive的缩写 
 
_ai : 不区分重音，Accent-insensitive的缩写 
 
_as : 区分重音，Accent-sensitive的缩写 
 
_bin : 二进制
不区分大小写
mysql> SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
Query OK, 0 rows affected (0.07 sec)

mysql> select 'a' = 'A';
+-----------+
| 'a' = 'A' |
+-----------+
|         1 |
+-----------+
1 row in set (0.01 sec)

例子中排序规则为 utf8mb4_unicode_ci 是不区分大小写的，所以字符 a 和字符 A 会被当做相同字符处理

区分大小写
mysql> SET NAMES 'latin1' COLLATE 'latin1_general_cs';
Query OK, 0 rows affected (0.00 sec)

mysql> select 'a' = 'A';
+-----------+
| 'a' = 'A' |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

例子中排序规则为 latin1_general_cs 是会区分大小写的，所以字符 a 和字符 A 会被认为是两个不同的字符

二进制
mysql> SET NAMES 'utf8mb4' COLLATE 'utf8mb4_bin';
Query OK, 0 rows affected (0.00 sec)

mysql> select 'a' = 'A';
+-----------+
| 'a' = 'A' |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

mysql> select 'à' = 'a';
+------------+
| 'à' = 'a'  |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)

上面例子使用的排序规则是 utf8mb4_bin 从结果可以得知：utf8mb4_bin 排序规则区分大小写，也区分重音字符

是否区分重音

重音字符是类似 à、ě、ň 的字符，不区分重音是指字符 a和 à、e和ě 以及 n和ň 被认为是同一个字符

对于非二进制(后缀为 _bin)的排序规则, 如果排序规则名字后缀不包含 _ai 和 _as, 则排序规则名称中的 _ci 默认隐含了_ai, _cs默认隐含了_as

例如: utf8mb4_unicode_ci排序规则是不区分大小写并且隐含不区分重音的

latin1_general_cs 排序规则是区分大小写并且隐含区分重音的

具体的请查看下面的例子

mysql> SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
Query OK, 0 rows affected (0.00 sec)

mysql> select 'à' = 'a';
+------------+
| 'à' = 'a'  |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

mysql> SET NAMES 'latin1' COLLATE 'latin1_general_cs';
Query OK, 0 rows affected (0.00 sec)

mysql> select 'à' = 'a';
+------------+
| 'à' = 'a'  |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)

查看排序规则

查看数据库数据库的排序规则

mysql> select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME from INFORMATION_SCHEMA.SCHEMATA where SCHEMA_NAME='test_a'; 
+-------------+----------------------------+------------------------+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+-------------+----------------------------+------------------------+
| test_a      | utf8mb4                    | utf8mb4_general_ci     |
+-------------+----------------------------+------------------------+
1 row in set (0.00 sec)

上面的例子是查看 test_a 数据库的字符集和排序规则，从结果可以得知：test_a数据库的排序规则是utf8mb4_general_ci

方法2：直接查询 collation_database 变量值

mysql> use test_a;
Database changed
mysql> show variables like 'collation_database';
+--------------------+--------------------+
| Variable_name      | Value              |
+--------------------+--------------------+
| collation_database | utf8mb4_general_ci |
+--------------------+--------------------+
1 row in set (0.02 sec)

查看表的排序规则

方法1：根据数据库名和表名查看 INFORMATION_SCHEMA.TABLES 表中的 TABLE_COLLATION 字段，可以得到指定数据库中指定表的排序规则，具体的实例如下所示：

mysql> CREATE TABLE `test` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `title` varchar(255) NOT NULL DEFAULT '',
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT TABLE_SCHEMA, TABLE_NAME,TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='test_a' and TABLE_NAME = 'test';
+--------------+------------+--------------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_COLLATION    |
+--------------+------------+--------------------+
| test_a       | test       | utf8mb4_general_ci |
+--------------+------------+--------------------+
1 row in set (0.00 sec)

方法2：执行 show create table 表名 语句查看

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

一般创建表的时候会指定排序规则，例子中没有显示指定表的排序规则，这是因为使用的是字符集的默认排序规则，test 表的字符集是 utf8mb4 , 默认的排序规则是 utf8mb4_general_ci

几种排序字符集的区别

utf8mb4_unicode_ci 和 utf8mb4_general_ci

准确性

utf8mb4_unicode_ci 是基于标准的 Unicode 来排序和比较，能够在各种语言之间精确排序

utf8mb4_general_ci 没有实现 Unicode 排序规则，在遇到某些特殊语言或者字符集，排序结果可能不一致。

但是绝大多数情况下，这些特殊字符的顺序并不需要那么精确。

性能

utf8mb4_general_ci 在比较和排序的时候更快

utf8mb4_unicode_ci 在特殊情况下，Unicode 排序规则为了能够处理特殊字符的情况，实现了略微复杂的排序算法。

但是在绝大多数情况下，不会发生此类复杂比较。相比选择哪一种 collation，使用者更应该关心字符集与排序规则在 db 里需要统一。

utf8mb4_0900_ai_ci

MySQL 8.0 默认的是 utf8mb4_0900_ai_ci，属于 utf8mb4_unicode_ci 中的一种，具体含义如下：

  • uft8mb4 表示用 UTF-8 编码方案，每个字符最多占 4 个字节。
  • 0900 指的是 Unicode 校对算法版本。（Unicode 归类算法是用于比较符合 Unicode 标准要求的两个 Unicode 字符串的方法）。
  • ai 指的是口音不敏感。也就是说，排序时 e，è，é，ê 和 ë 之间没有区别。
  • ci 表示不区分大小写。也就是说，排序时 p 和 P 之间没有区别。
文章作者: 江湖义气
文章链接: https://blog.wenjy.top/2024/12/21/mysql/collation.html
版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 江湖义气 !
MySQL
  目录