柚子快報邀請碼778899分享：MySQL 排序規(guī)則

http://yzkb.51969.com/

文章目錄

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

1.簡介

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

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

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

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

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

或者從 INFORMATION_SCHEMA CHARACTER_SETS 視圖中查看所有字符集與之對應的默認排序規(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 默認使用 utf8mb4 字符集，默認的排序規(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 比較的結果如下：

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.設置排序規(guī)則

排序規(guī)則和字符集一樣支持不同級別的設置。如果沒有指定排序規(guī)則，MySQL 會基于字符集設置一個默認的排序規(guī)則。使用 SHOW 語句查看當前設置：

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 對應，表示 MySQL 服務器的全局默認排序規(guī)則，可以在服務器配置文件中 [mysqld] 部分的 collation-server 選項或者啟動服務時通過命令行參數(shù) --collation-server 進行設置，也可以在運行時動態(tài)修改。

collation_database 和 character_set_database 對應，表示當前默認數(shù)據(jù)庫的排序規(guī)則。當我們使用 CREATE DATABASE 或者 ALTER DATABASE 時，可以指定一個排序規(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]

同樣，對于 CREATE TABLE 和 ALTER TABLE 語句，也可以為表或者字符類型的字段指定一個排序規(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 對應，表示客戶端連接使用的排序規(guī)則。

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

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

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

您可以查詢 information_schema 數(shù)據(jù)庫的 SCHEMATA 視圖來查看數(shù)據(jù)庫的排序規(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 語句。

SHOW TABLE STATUS LIKE 'tbl_name';

也可以查詢 information_schema 數(shù)據(jù)庫的 TABLES 表，以獲取有關數(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ī)則

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

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

SELECT name

FROM employee

ORDER BY name;

name |

---------|

關興 |

關平 |

關羽 |

劉備 |

周倉 |

孫丫鬟 |

孫乾 |

孫尚香 |

龐統(tǒng) |

廖化 |

...

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

SELECT name

FROM employee

ORDER BY name collate 'utf8mb4_zh_0900_as_cs';

name |

----------|

鄧芝 |

法正 |

關平 |

關興 |

關羽 |

黃權 |

黃忠 |

簡雍 |

蔣琬 |

廖化 |

...

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

SELECT emp_name

FROM employee

ORDER BY convert(emp_name using gbk);

該語句和上一個示例返回的結果相同。

參考文獻

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

柚子快報邀請碼778899分享：MySQL 排序規(guī)則

http://yzkb.51969.com/

精彩文章