聊一聊 MySQL 排序規則

作者：LinuxThings 2020-10-15 06:56:51

數據庫 MySQL

在日常工作中，對于MySQL排序規則，很少關注，大部分時候都是直接使用字符集默認的排序規則，常常忽視了排序規則的細節問題，了解排序規則有助于更好的理解MySQL字符比較和排序相關的知識

[[346355]]

本文轉載自微信公眾號「Linux開發那些事兒」，作者 LinuxThings 。轉載本文請聯系Linux開發那些事兒公眾號。

在日常工作中，對于MySQL排序規則，很少關注，大部分時候都是直接使用字符集默認的排序規則，常常忽視了排序規則的細節問題，了解排序規則有助于更好的理解MySQL字符比較和排序相關的知識。

簡述

說起排序規則就離不開字符集，嚴格來說，排序規則是依賴于字符集的。

字符集是用來定義MySQL存儲不同字符的方式，而排序規則一般指對字符集中字符串之間的比較、排序制定的規則。一種字符集可以對應多種排序規則，但是一種排序規則只能對應指定的一種字符集，兩個不同的字符集不能有相同的排序規則。

上圖中，Collation 列表示排序方式，Charset 列表示字符集，可以看出 utf8 字符集對應著許多的排序方式，排序方式那一列每一項的值都不一樣，并且每一項都對應唯一一種字符集，在這里是 utf8 字符集。

默認排序規則

字符集的默認排序規則

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

每種字符集都有一個默認的排序規則, 可以通過下面的SQL語句查詢指定字符集的默認排序規則

上面的例子是查詢字符集前綴包含utf8的默認排序方式，從中可以得知：

utf8字符集的默認排序方式是 utf8_general_ci 字符集中字符最大長度占3個字節

utf8mb4 字符集的默認排序方式是 utf8mb4_general_ci 字符集中字符最大長度占4個字節

數據庫的默認排序規則

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

例如：現需要把MySQL服務器的默認字符集設置為 utf8, 默認排序規則設置為 utf8_general_ci, 只需要在 /etc/my.cnf 配置文件的 [mysqld] 下添加以下子項

character-set-server=utf8 
collation-server=utf8_general_ci

創建數據庫的時候如果沒有指定字符集，會使用MySQL服務器默認字符集和默認排序規則

假如: 在下面例子中，MySQL服務器的默認字符集和默認排序規則分別是 utf8 和 utf8_general_ci

mysql> create database at; 
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='at'; 
+-------------+----------------------------+------------------------+ 
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | 
+-------------+----------------------------+------------------------+ 
| at          | utf8                       | utf8_general_ci        | 
+-------------+----------------------------+------------------------+ 
1 row in set (0.00 sec)

在上面的例子中，創建了 at 數據庫，創建數據庫的時候沒有為數據庫指定字符集和排序規則，此時會使用 MySQL服務器的默認字符集和排序規則

通過SQL語句查詢 at 數據庫的默認字符集和默認排序規則，結果和MySQL服務器的默認字符集和默認排序規則是一樣的

排序規則命名以及名字后綴

命名

排序規則的命名是以和它自身關聯的字符集名字開頭的，后面再接一個或多個后綴來表示指定字符集的一種排序規則

例如：utf8_general_ci 和 utf8_bin 就是 utf8字符集的兩種排序規則, latin1_swedish_ci 是 latin1字符集的排序規則

注意： binary 字符集只有一種排序規則,并且它的排序規則名字和字符集名字一樣, 也是 binary

后綴

排序規則名字的后綴是有特殊意義的，根據后綴可以知道排序規則是否區分大小寫，是否區分重音，是否是二進制等等，下面列出了部分后綴的說明

_ci : 不區分大小寫, Case-insensitive的縮寫 
 
_cs : 區分大小寫，Case-sensitive的縮寫 
 
_ai : 不區分重音，Accent-insensitive的縮寫 
 
_as : 區分重音，Accent-sensitive的縮寫 
 
_bin : 二進制

1: 不區分大小寫

mysql> SET NAMES 'utf8' COLLATE 'utf8_unicode_ci'; 
Query OK, 0 rows affected (0.02 sec) 
 
mysql> select 'a' = 'A'; 
+-----------+ 
| 'a' = 'A' | 
+-----------+ 
|         1 | 
+-----------+ 
1 row in set (0.00 sec)

例子中排序規則為 utf8_unicode_ci 是不區分大小寫的，所以字符 a 和字符 A 會被當做相同字符處理

2: 區分大小寫

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 會被認為是兩個不同的字符

3: 二進制

mysql> SET NAMES 'utf8' COLLATE 'utf8_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)

上面例子使用的排序規則是 utf8_bin 從結果可以得知：utf8_bin 排序規則區分大小寫，也區分重音字符

4: 是否區分重音

重音字符是類似 à、ě、ň 的字符，不區分重音是指字符 a和 à、e和ě 以及 n和ň 被認為是同一個字符

對于非二進制(后綴為 _bin)的排序規則, 如果排序規則名字后綴不包含 _ai 和 _as, 則排序規則名稱中的 _ci 默認隱含了_ai, _cs默認隱含了_as

例如: utf8_unicode_ci排序規則是不區分大小寫并且隱含不區分重音的

latin1_general_cs 排序規則是區分大小寫并且隱含區分重音的

具體的請查看下面的例子

mysql> SET NAMES 'utf8' COLLATE 'utf8_unicode_ci'; 
Query OK, 0 rows affected (0.02 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.01 sec)

查看排序規則

查看數據庫數據庫的排序規則

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

上面的例子是查看 at 數據庫的字符集和排序規則，從結果可以得知：at數據庫的排序規則是latin1_swedish_ci

方法2：直接查詢 collation_database 變量值

mysql> use at; 
Database changed 
mysql> show variables like 'collation_database'; 
+--------------------+-------------------+ 
| Variable_name      | Value             | 
+--------------------+-------------------+ 
| collation_database | latin1_swedish_ci | 
+--------------------+-------------------+ 
1 row in set (0.00 sec)

實例中 use at SQL語句切換到 at 數據庫，然后使用 show variables like 'collation_database'; 語句查詢 at 數據庫的排序規則

查看表的排序規則

方法1：根據數據庫名和表名查看 INFORMATION_SCHEMA.TABLES 表中的 TABLE_COLLATION 字段，可以得到指定數據庫中指定表的排序規則，具體的實例如下所示：

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

方法2：執行 show create table 表名語句查看

mysql> show create table tc\G 
*************************** 1. row *************************** 
       Table: tc 
Create Table: CREATE TABLE `tc` ( 
  `id` int(11) NOT NULL, 
  `a` char(32) NOT NULL, 
  `b` char(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, 
  `c` char(32) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
1 row in set (0.00 sec)

上面的例子中, show create table tc\G 是查看 tc 表的創建語句，一般創建表的時候會指定排序規則，例子中沒有顯示指定表的排序規則，這是因為使用的是字符集的默認排序規則，tc 表的字符集是 utf8 , 默認的排序規則是 utf8_general_ci

查看字段的排序規則

上面查看表的排序規則小節的方法2 是查看表的創建語句，字段的排序規則也可以從表創建語句中查看到，如果沒有顯示指定字段的排序規則，創建表的語句中是看不到排序規則的，這表示該字段使用字符集的默認排序方式

mysql> show create table tc\G 
*************************** 1. row *************************** 
       Table: tc 
Create Table: CREATE TABLE `tc` ( 
  `id` int(11) NOT NULL, 
  `a` char(32) NOT NULL, 
  `b` char(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, 
  `c` char(32) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
1 row in set (0.00 sec)

上面例子中,表 tc的字符集是utf8

字段 a 沒有顯示指定排序規則，則它使用的是utf8字符集的默認排序規則utf8_general_ci

字段 b 的排序規則是 utf8_bin

字段 c 的排序規則是 latin1_general_cs

修改排序規則

修改數據庫的排序規則

數據庫的默認排序規則可以通過 alter database 數據庫名 collate 新的排序規則名字 SQL語句進行修改

mysql> select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME from INFORMATION_SCHEMA.SCHEMATA where SCHEMA_NAME='at'; 
+-------------+----------------------------+------------------------+ 
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | 
+-------------+----------------------------+------------------------+ 
| at          | utf8                       | utf8_general_ci        | 
+-------------+----------------------------+------------------------+ 
1 row in set (0.00 sec) 
 
mysql> alter database at collate utf8_bin; 
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='at'; 
+-------------+----------------------------+------------------------+ 
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | 
+-------------+----------------------------+------------------------+ 
| at          | utf8                       | utf8_bin               | 
+-------------+----------------------------+------------------------+ 
 
 
mysql> alter database at collate latin1_swedish_ci ; 
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='at'; 
+-------------+----------------------------+------------------------+ 
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | 
+-------------+----------------------------+------------------------+ 
| at          | latin1                     | latin1_swedish_ci      | 
+-------------+----------------------------+------------------------+ 
1 row in set (0.00 sec)

上面的例子中，at 數據庫初始的字符集和排序規則分別是 utf8 和 utf8_general_ci

SQL語句: alter database at collate utf8_bin; 把字符集和排序規則修改為 utf8 和 utf8_bin

SQL語句: alter database at collate latin1_swedish_ci; 把字符集和排序規則修改為 latin1 和 latin1_swedish_ci

由于latin1_swedish_ci排序規則是屬于latin1字符集，所以 at 數據庫排序規則修改成 latin1_swedish_ci 之后，字符集相應的也會由 utf8 變成 latin1

修改表的排序規則

通過 alter table 表名 collate 新的排序規則名字; 語句可以修改表的排序規則

注意：上面的語句修改表排序規則，對現有字段的排序規則沒影響，只影響后續新添加字段的默認排序規則

mysql> show create table tc\G 
*************************** 1. row *************************** 
       Table: tc 
Create Table: CREATE TABLE `tc` ( 
  `id` int(11) NOT NULL, 
  `a` char(32) NOT NULL, 
  `b` char(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, 
  `c` char(32) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
1 row in set (0.00 sec) 
 
mysql> alter table tc collate latin1_swedish_ci; 
Query OK, 0 rows affected (0.00 sec) 
Records: 0  Duplicates: 0  Warnings: 0 
 
mysql> show create table tc\G 
*************************** 1. row *************************** 
       Table: tc 
Create Table: CREATE TABLE `tc` ( 
  `id` int(11) NOT NULL, 
  `a` char(32) CHARACTER SET utf8 NOT NULL, 
  `b` char(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, 
  `c` char(32) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 
1 row in set (0.00 sec)

上面例子中，tc表初始的字符集是utf8,默認排序規則是utf8_general_ci 執行 alter table tc collate latin1_swedish_ci;SQL語句把表的排序規則修改成latin1_swedish_ci

由于latin1_swedish_ci排序規則是屬于latin1字符集的，所以此時表的字符集也會修改成latin1

修改表排序規則之前，字段a的排序規則是默認的utf8_general_ci，在創建表的語句中沒有顯示出來

修改排序規則之后，由于表默認的排序規則發生的變化，所以字段a會顯示出它原本的字符集

修改字段的排序規則

mysql> show create table tc\G 
*************************** 1. row *************************** 
       Table: tc 
Create Table: CREATE TABLE `tc` ( 
  `id` int(11) NOT NULL, 
  `a` char(32) CHARACTER SET utf8 NOT NULL, 
  `b` char(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, 
  `c` char(32) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL, 
  `d` char(32) DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 
1 row in set (0.00 sec) 
 
mysql> alter table tc modify b char(32) not null  collate latin1_general_cs; 
Query OK, 0 rows affected (0.05 sec) 
Records: 0  Duplicates: 0  Warnings: 0 
 
mysql> show create table tc\G 
*************************** 1. row *************************** 
       Table: tc 
Create Table: CREATE TABLE `tc` ( 
  `id` int(11) NOT NULL, 
  `a` char(32) CHARACTER SET utf8 NOT NULL, 
  `b` char(32) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL, 
  `c` char(32) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL, 
  `d` char(32) DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 
1 row in set (0.00 sec)

上面例子中，字段 b 原始的字符集和排序規則分別是 utf8、utf8_bin

語句 alter table tc modify b char(32) not null collate latin1_general_cs;把字段 b 的排序規則修改成 latin1_general_cs, 由于latin1_general_cs 排序規則是屬于latin1字符集, 所以修改之后字段 b 的字符集和排序規則都發生了變化

小結

本文介紹了MySQL字符集的排序規則，由于篇幅原因，有些點沒有涉及到，更多排序規則有關的細節可以自行查閱MySQL官方文檔

責任編輯：武曉燕來源： Linux開發那些事兒

MySQL 排序