聊一聊 MySQL 排序規(guī)則

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

在日常工作中，對(duì)于MySQL排序規(guī)則，很少關(guān)注，大部分時(shí)候都是直接使用字符集默認(rèn)的排序規(guī)則，常常忽視了排序規(guī)則的細(xì)節(jié)問(wèn)題，了解排序規(guī)則有助于更好的理解MySQL字符比較和排序相關(guān)的知識(shí)

[[346355]]

本文轉(zhuǎn)載自微信公眾號(hào)「Linux開(kāi)發(fā)那些事兒」，作者 LinuxThings 。轉(zhuǎn)載本文請(qǐng)聯(lián)系Linux開(kāi)發(fā)那些事兒公眾號(hào)。

簡(jiǎn)述

說(shuō)起排序規(guī)則就離不開(kāi)字符集，嚴(yán)格來(lái)說(shuō)，排序規(guī)則是依賴于字符集的。

字符集是用來(lái)定義MySQL存儲(chǔ)不同字符的方式，而排序規(guī)則一般指對(duì)字符集中字符串之間的比較、排序制定的規(guī)則。一種字符集可以對(duì)應(yīng)多種排序規(guī)則，但是一種排序規(guī)則只能對(duì)應(yīng)指定的一種字符集，兩個(gè)不同的字符集不能有相同的排序規(guī)則。

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

默認(rèn)排序規(guī)則

字符集的默認(rèn)排序規(guī)則

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)

每種字符集都有一個(gè)默認(rèn)的排序規(guī)則, 可以通過(guò)下面的SQL語(yǔ)句查詢指定字符集的默認(rèn)排序規(guī)則

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

utf8字符集的默認(rèn)排序方式是 utf8_general_ci 字符集中字符最大長(zhǎng)度占3個(gè)字節(jié)

utf8mb4 字符集的默認(rèn)排序方式是 utf8mb4_general_ci 字符集中字符最大長(zhǎng)度占4個(gè)字節(jié)

數(shù)據(jù)庫(kù)的默認(rèn)排序規(guī)則

MySQL服務(wù)器的默認(rèn)字符集可以在 /etc/my.cnf 配置中的 [mysqld] 下修改

例如：現(xiàn)需要把MySQL服務(wù)器的默認(rèn)字符集設(shè)置為 utf8, 默認(rèn)排序規(guī)則設(shè)置為 utf8_general_ci, 只需要在 /etc/my.cnf 配置文件的 [mysqld] 下添加以下子項(xiàng)

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

創(chuàng)建數(shù)據(jù)庫(kù)的時(shí)候如果沒(méi)有指定字符集，會(huì)使用MySQL服務(wù)器默認(rèn)字符集和默認(rèn)排序規(guī)則

假如: 在下面例子中，MySQL服務(wù)器的默認(rèn)字符集和默認(rèn)排序規(guī)則分別是 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)

在上面的例子中，創(chuàng)建了 at 數(shù)據(jù)庫(kù)，創(chuàng)建數(shù)據(jù)庫(kù)的時(shí)候沒(méi)有為數(shù)據(jù)庫(kù)指定字符集和排序規(guī)則，此時(shí)會(huì)使用 MySQL服務(wù)器的默認(rèn)字符集和排序規(guī)則

通過(guò)SQL語(yǔ)句查詢 at 數(shù)據(jù)庫(kù)的默認(rèn)字符集和默認(rèn)排序規(guī)則，結(jié)果和MySQL服務(wù)器的默認(rèn)字符集和默認(rèn)排序規(guī)則是一樣的

排序規(guī)則命名以及名字后綴

命名

排序規(guī)則的命名是以和它自身關(guān)聯(lián)的字符集名字開(kāi)頭的，后面再接一個(gè)或多個(gè)后綴來(lái)表示指定字符集的一種排序規(guī)則

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

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

后綴

排序規(guī)則名字的后綴是有特殊意義的，根據(jù)后綴可以知道排序規(guī)則是否區(qū)分大小寫(xiě)，是否區(qū)分重音，是否是二進(jìn)制等等，下面列出了部分后綴的說(shuō)明

_ci : 不區(qū)分大小寫(xiě), Case-insensitive的縮寫(xiě) 
 
_cs : 區(qū)分大小寫(xiě)，Case-sensitive的縮寫(xiě) 
 
_ai : 不區(qū)分重音，Accent-insensitive的縮寫(xiě) 
 
_as : 區(qū)分重音，Accent-sensitive的縮寫(xiě) 
 
_bin : 二進(jìn)制

1: 不區(qū)分大小寫(xiě)

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)

例子中排序規(guī)則為 utf8_unicode_ci 是不區(qū)分大小寫(xiě)的，所以字符 a 和字符 A 會(huì)被當(dāng)做相同字符處理

2: 區(qū)分大小寫(xiě)

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)

例子中排序規(guī)則為 latin1_general_cs 是會(huì)區(qū)分大小寫(xiě)的，所以字符 a 和字符 A 會(huì)被認(rèn)為是兩個(gè)不同的字符

3: 二進(jìn)制

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)

上面例子使用的排序規(guī)則是 utf8_bin 從結(jié)果可以得知：utf8_bin 排序規(guī)則區(qū)分大小寫(xiě)，也區(qū)分重音字符

4: 是否區(qū)分重音

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

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

例如: utf8_unicode_ci排序規(guī)則是不區(qū)分大小寫(xiě)并且隱含不區(qū)分重音的

latin1_general_cs 排序規(guī)則是區(qū)分大小寫(xiě)并且隱含區(qū)分重音的

具體的請(qǐng)查看下面的例子

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)

查看排序規(guī)則

查看數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù)的排序規(guī)則

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 數(shù)據(jù)庫(kù)的字符集和排序規(guī)則，從結(jié)果可以得知：at數(shù)據(jù)庫(kù)的排序規(guī)則是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)

實(shí)例中 use at SQL語(yǔ)句切換到 at 數(shù)據(jù)庫(kù)，然后使用 show variables like 'collation_database'; 語(yǔ)句查詢 at 數(shù)據(jù)庫(kù)的排序規(guī)則

查看表的排序規(guī)則

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

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：執(zhí)行 show create table 表名語(yǔ)句查看

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 表的創(chuàng)建語(yǔ)句，一般創(chuàng)建表的時(shí)候會(huì)指定排序規(guī)則，例子中沒(méi)有顯示指定表的排序規(guī)則，這是因?yàn)槭褂玫氖亲址哪J(rèn)排序規(guī)則，tc 表的字符集是 utf8 , 默認(rèn)的排序規(guī)則是 utf8_general_ci

查看字段的排序規(guī)則

上面查看表的排序規(guī)則小節(jié)的方法2 是查看表的創(chuàng)建語(yǔ)句，字段的排序規(guī)則也可以從表創(chuàng)建語(yǔ)句中查看到，如果沒(méi)有顯示指定字段的排序規(guī)則，創(chuàng)建表的語(yǔ)句中是看不到排序規(guī)則的，這表示該字段使用字符集的默認(rèn)排序方式

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 沒(méi)有顯示指定排序規(guī)則，則它使用的是utf8字符集的默認(rèn)排序規(guī)則utf8_general_ci

字段 b 的排序規(guī)則是 utf8_bin

字段 c 的排序規(guī)則是 latin1_general_cs

修改排序規(guī)則

修改數(shù)據(jù)庫(kù)的排序規(guī)則

數(shù)據(jù)庫(kù)的默認(rèn)排序規(guī)則可以通過(guò) alter database 數(shù)據(jù)庫(kù)名 collate 新的排序規(guī)則名字 SQL語(yǔ)句進(jìn)行修改

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 數(shù)據(jù)庫(kù)初始的字符集和排序規(guī)則分別是 utf8 和 utf8_general_ci

SQL語(yǔ)句: alter database at collate utf8_bin; 把字符集和排序規(guī)則修改為 utf8 和 utf8_bin

SQL語(yǔ)句: alter database at collate latin1_swedish_ci; 把字符集和排序規(guī)則修改為 latin1 和 latin1_swedish_ci

由于latin1_swedish_ci排序規(guī)則是屬于latin1字符集，所以 at 數(shù)據(jù)庫(kù)排序規(guī)則修改成 latin1_swedish_ci 之后，字符集相應(yīng)的也會(huì)由 utf8 變成 latin1

修改表的排序規(guī)則

通過(guò) alter table 表名 collate 新的排序規(guī)則名字; 語(yǔ)句可以修改表的排序規(guī)則

注意：上面的語(yǔ)句修改表排序規(guī)則，對(duì)現(xiàn)有字段的排序規(guī)則沒(méi)影響，只影響后續(xù)新添加字段的默認(rèn)排序規(guī)則

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,默認(rèn)排序規(guī)則是utf8_general_ci 執(zhí)行 alter table tc collate latin1_swedish_ci;SQL語(yǔ)句把表的排序規(guī)則修改成latin1_swedish_ci

由于latin1_swedish_ci排序規(guī)則是屬于latin1字符集的，所以此時(shí)表的字符集也會(huì)修改成latin1

修改表排序規(guī)則之前，字段a的排序規(guī)則是默認(rèn)的utf8_general_ci，在創(chuàng)建表的語(yǔ)句中沒(méi)有顯示出來(lái)

修改排序規(guī)則之后，由于表默認(rèn)的排序規(guī)則發(fā)生的變化，所以字段a會(huì)顯示出它原本的字符集

修改字段的排序規(guī)則

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 原始的字符集和排序規(guī)則分別是 utf8、utf8_bin

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

小結(jié)

本文介紹了MySQL字符集的排序規(guī)則，由于篇幅原因，有些點(diǎn)沒(méi)有涉及到，更多排序規(guī)則有關(guān)的細(xì)節(jié)可以自行查閱MySQL官方文檔

責(zé)任編輯：武曉燕來(lái)源： Linux開(kāi)發(fā)那些事兒