柚子快報(bào)邀請(qǐng)碼778899分享：MySQL 排序規(guī)則

http://yzkb.51969.com/

文章目錄

1.簡(jiǎn)介2.支持的排序規(guī)則3.設(shè)置排序規(guī)則4.查看排序規(guī)則5.中文排序規(guī)則參考文獻(xiàn)

1.簡(jiǎn)介

字符集是一組符號(hào)和編碼。排序規(guī)則是一組用于比較字符集中的字符的規(guī)則。

每個(gè) MySQL 字符集可以支持一個(gè)或者多個(gè)排序規(guī)則，用于定義每個(gè)字符的比較規(guī)則，包括是否區(qū)分大小寫，是否區(qū)分重音等。

2.支持的排序規(guī)則

MySQL 使用 SHOW COLLATION 語(yǔ)句查看各種字符集支持的排序規(guī)則：

SHOW COLLATION

[LIKE 'pattern' | WHERE expr]

比如：

SHOW COLLATION;

+--------------------------+----------+-----+---------+----------+---------+

| Collation | Charset | Id | Default | Compiled | Sortlen |

+--------------------------+----------+-----+---------+----------+---------+

| big5_chinese_ci | big5 | 1 | Yes | Yes | 1 |

| big5_bin | big5 | 84 | | Yes | 1 |

| dec8_swedish_ci | dec8 | 3 | Yes | Yes | 1 |

...

| gb18030_chinese_ci | gb18030 | 248 | Yes | Yes | 2 |

| gb18030_bin | gb18030 | 249 | | Yes | 1 |

| gb18030_unicode_520_ci | gb18030 | 250 | | Yes | 8 |

+--------------------------+----------+-----+---------+----------+---------+

222 rows in set (0.06 sec)

Collation：排序規(guī)則的名稱。這是排序規(guī)則的唯一標(biāo)識(shí)符，您可以在創(chuàng)建或更改表時(shí)使用它來指定表的排序規(guī)則。Charset：字符集的名稱。排序規(guī)則是與特定字符集關(guān)聯(lián)的，該列顯示了該排序規(guī)則適用的字符集。Id：排序規(guī)則的內(nèi)部編號(hào)。這是MySQL內(nèi)部使用的標(biāo)識(shí)符。Default：是否為默認(rèn)排序規(guī)則。如果是默認(rèn)排序規(guī)則，將顯示“Yes”；否則，顯示“”No”。Compiled：是否已編譯排序規(guī)則。編譯的排序規(guī)則可以更快地執(zhí)行字符排序操作。如果已編譯，則顯示“Yes”；否則，顯示“”No”。Sortlen：顯示了排序規(guī)則的最大前綴長(zhǎng)度。在某些情況下，只需比較字符串的前幾個(gè)字符即可確定排序順序，這可以提高性能。Sortlen 列顯示了應(yīng)用此規(guī)則時(shí)要比較的字符數(shù)。

字符集至少有一個(gè)排序規(guī)則，大多數(shù)有多個(gè)。每個(gè)字符集都有一個(gè)默認(rèn)排序規(guī)則，例如 utf8mb4 和 latin1 的默認(rèn)排序規(guī)則為 utf8mb4_0900_ai_ci 和 latin1_swedish_ci。

或者從 INFORMATION_SCHEMA CHARACTER_SETS 視圖中查看所有字符集與之對(duì)應(yīng)的默認(rèn)排序規(guī)則。

SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS;

+--------------------+----------------------+---------------------------------+--------+

| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN |

+--------------------+----------------------+---------------------------------+--------+

| big5 | big5_chinese_ci | Big5 Traditional Chinese | 2 |

| dec8 | dec8_swedish_ci | DEC West European | 1 |

| cp850 | cp850_general_ci | DOS West European | 1 |

...

| cp932 | cp932_japanese_ci | SJIS for Windows Japanese | 2 |

| eucjpms | eucjpms_japanese_ci | UJIS for Windows Japanese | 3 |

| gb18030 | gb18030_chinese_ci | China National Standard GB18030 | 4 |

+--------------------+----------------------+---------------------------------+--------+

41 rows in set (0.05 sec)

MySQL 8.0 默認(rèn)使用 utf8mb4 字符集，默認(rèn)的排序規(guī)則為 utf8mb4_0900_ai_ci，表示不區(qū)分重音和大小寫。例如：

SELECT 'a' = 'A';

+-----------+

| 'a' = 'A' |

+-----------+

| 1 |

+-----------+

1 row in set (0.05 sec)

如果換成 utf8mb4_zh_0900_as_cs 排序規(guī)則，a 和 A 比較的結(jié)果如下：

SELECT 'a' COLLATE utf8mb4_zh_0900_as_cs = 'A';

+-----------------------------------------+

| 'a' COLLATE utf8mb4_zh_0900_as_cs = 'A' |

+-----------------------------------------+

| 0 |

+-----------------------------------------+

1 row in set (0.05 sec)

3.設(shè)置排序規(guī)則

排序規(guī)則和字符集一樣支持不同級(jí)別的設(shè)置。如果沒有指定排序規(guī)則，MySQL 會(huì)基于字符集設(shè)置一個(gè)默認(rèn)的排序規(guī)則。使用 SHOW 語(yǔ)句查看當(dāng)前設(shè)置：

Variable_name |Value |

-----------------------------|------------------|

collation_connection |utf8mb4_0900_ai_ci|

collation_database |utf8mb4_0900_ai_ci|

collation_server |utf8mb4_0900_ai_ci|

default_collation_for_utf8mb4|utf8mb4_0900_ai_ci|

其中，collation_server 與 character_set_server 對(duì)應(yīng)，表示 MySQL 服務(wù)器的全局默認(rèn)排序規(guī)則，可以在服務(wù)器配置文件中 [mysqld] 部分的 collation-server 選項(xiàng)或者啟動(dòng)服務(wù)時(shí)通過命令行參數(shù) --collation-server 進(jìn)行設(shè)置，也可以在運(yùn)行時(shí)動(dòng)態(tài)修改。

collation_database 和 character_set_database 對(duì)應(yīng)，表示當(dāng)前默認(rèn)數(shù)據(jù)庫(kù)的排序規(guī)則。當(dāng)我們使用 CREATE DATABASE 或者 ALTER DATABASE 時(shí)，可以指定一個(gè)排序規(guī)則。

CREATE DATABASE db_name

[[DEFAULT] CHARACTER SET charset_name]

[[DEFAULT] COLLATE collation_name]

ALTER DATABASE db_name

[[DEFAULT] CHARACTER SET charset_name]

[[DEFAULT] COLLATE collation_name]

同樣，對(duì)于 CREATE TABLE 和 ALTER TABLE 語(yǔ)句，也可以為表或者字符類型的字段指定一個(gè)排序規(guī)則。

CREATE TABLE table_name (column_list)

[[DEFAULT] CHARACTER SET charset_name]

[COLLATE collation_name]]

ALTER TABLE table_name

[[DEFAULT] CHARACTER SET charset_name]

[COLLATE collation_name]

collation_connection 和 character_set_connection 對(duì)應(yīng)，表示客戶端連接使用的排序規(guī)則。

排序規(guī)則由字符集的名稱、可選的本地語(yǔ)言代碼和 Unicode 版本以及其他屬性組成，例如 utf8mb4_zh_0900_as_cs 表示 9.0.0 版本 utf8mb4 字符集的中文排序規(guī)則，區(qū)分重音（accent sensitive）和大小寫（case sensitive）。

4.查看排序規(guī)則

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

您可以查詢 information_schema 數(shù)據(jù)庫(kù)的 SCHEMATA 視圖來查看數(shù)據(jù)庫(kù)的排序規(guī)則。

SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME

FROM information_schema.SCHEMATA

WHERE SCHEMA_NAME = 'your_database_name';

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

要查看特定數(shù)據(jù)表的排序規(guī)則，使用 SHOW TABLE STATUS 語(yǔ)句。

SHOW TABLE STATUS LIKE 'tbl_name';

也可以查詢 information_schema 數(shù)據(jù)庫(kù)的 TABLES 表，以獲取有關(guān)數(shù)據(jù)表的信息。

SELECT TABLE_NAME, TABLE_COLLATION

FROM information_schema.TABLES

WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

5.中文排序規(guī)則

對(duì)于中文而言，排序方式與英文有所不同。英文通常按照字母排序，而中文通常按照拼音、偏旁部首或者筆畫進(jìn)行排序。

MySQL 8.0 默認(rèn)使用的排序規(guī)則 utf8mb4_0900_ai_ci 對(duì)于中文按照偏旁部首進(jìn)行排序。以下語(yǔ)句按照員工的姓名進(jìn)行排序：

SELECT name

FROM employee

ORDER BY name;

name |

---------|

關(guān)興 |

關(guān)平 |

關(guān)羽 |

劉備 |

周倉(cāng) |

孫丫鬟 |

孫乾 |

孫尚香 |

龐統(tǒng) |

廖化 |

...

對(duì)于 utf8mb4 字符集，utf8mb4_zh_0900_as_cs 排序規(guī)則按照中文拼音進(jìn)行排序。例如：

SELECT name

FROM employee

ORDER BY name collate 'utf8mb4_zh_0900_as_cs';

name |

----------|

鄧芝 |

法正 |

關(guān)平 |

關(guān)興 |

關(guān)羽 |

黃權(quán) |

黃忠 |

簡(jiǎn)雍 |

蔣琬 |

廖化 |

...

也可以將數(shù)據(jù)轉(zhuǎn)換為其他支持特定排序規(guī)則的字符集，例如 gbk 字符集默認(rèn)的 gbk_chinese_ci 排序規(guī)則就是按照拼音進(jìn)行排序：

SELECT emp_name

FROM employee

ORDER BY convert(emp_name using gbk);

該語(yǔ)句和上一個(gè)示例返回的結(jié)果相同。

參考文獻(xiàn)

Chapter 10 Character Sets, Collations, Unicode 13.7.7.38 SHOW TABLE STATUS Statement

柚子快報(bào)邀請(qǐng)碼778899分享：MySQL 排序規(guī)則

http://yzkb.51969.com/

精彩文章