在 MySQL 中，排序規(guī)則（Collation）是控制字符比較方式的核心機(jī)制，直接影響查詢結(jié)果的排序、匹配邏輯（如大小寫區(qū)分）等。本文將從基礎(chǔ)概念出發(fā)，詳解排序規(guī)則的作用、與字符集的關(guān)系、查看與配置方法，并通過實(shí)際案例說明其對(duì)查詢結(jié)果的影響，幫助開發(fā)者精準(zhǔn)控制數(shù)據(jù)匹配行為。

一、排序規(guī)則的核心概念

排序規(guī)則（Collation）是字符集（Character Set）的 "配套規(guī)則"，定義了字符之間的比較、排序邏輯。其核心作用體現(xiàn)在兩個(gè)維度：

• 大小寫區(qū)分：是否將'a'和'A'視為相同字符；
• 編碼適配：針對(duì)特定字符集（如 UTF-8）的語言特性（如 accents 字符）制定比較規(guī)則。

例如，UTF-8 字符集的utf8mb4_0900_ai_ci排序規(guī)則中：

• ai表示 "accent-insensitive"（忽略重音）；
• ci表示 "case-insensitive"（忽略大小寫）；
  而utf8mb4_0900_as_cs中，as（accent-sensitive）和cs（case-sensitive）則表示區(qū)分重音和大小寫。

二、字符集與排序規(guī)則的關(guān)聯(lián)

字符集與排序規(guī)則是 "一對(duì)多" 的關(guān)系：一個(gè)字符集可以對(duì)應(yīng)多個(gè)排序規(guī)則，但每個(gè)排序規(guī)則僅屬于一個(gè)字符集。兩者的默認(rèn)關(guān)聯(lián)規(guī)則如下：

• 若僅指定字符集，排序規(guī)則將使用該字符集的默認(rèn)值（如utf8mb4默認(rèn)排序規(guī)則為utf8mb4_0900_ai_ci）；
• 若僅指定排序規(guī)則，字符集將自動(dòng)匹配該排序規(guī)則對(duì)應(yīng)的字符集（如指定utf8mb4_general_ci，字符集即為utf8mb4）。

三、排序規(guī)則的查看與配置

1. 查看字符集與排序規(guī)則信息

查看所有字符集及其默認(rèn)排序規(guī)則：

-- 方法1：SHOW命令
SHOW CHARACTER SET;
-- 方法2：查詢系統(tǒng)表
SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS;

查看指定字符集的所有排序規(guī)則：

-- 查看utf8mb4字符集的所有排序規(guī)則
SHOW COLLATION WHERE Charset = 'utf8mb4';

查看數(shù)據(jù)庫的默認(rèn)字符集與排序規(guī)則：

-- 方法1：使用系統(tǒng)變量
USE test_db; -- 切換到目標(biāo)數(shù)據(jù)庫
SELECT @@character_set_database, @@collation_database;
-- 方法2：查詢系統(tǒng)表
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM INFORMATION_SCHEMA.SCHEMATA 
WHERE SCHEMA_NAME = 'test_db'; -- 替換為數(shù)據(jù)庫名

2. 不同對(duì)象的排序規(guī)則設(shè)置

MySQL 中，排序規(guī)則可在服務(wù)器、數(shù)據(jù)庫、表、列、字符串五個(gè)層級(jí)設(shè)置，下層默認(rèn)繼承上層配置（可單獨(dú)覆蓋）。

四、排序規(guī)則對(duì)查詢結(jié)果的影響

排序規(guī)則不影響數(shù)據(jù)存儲(chǔ)（存入的'a'和'A'會(huì)原樣保存），但直接決定查詢時(shí)的匹配邏輯。以下通過測(cè)試案例驗(yàn)證：

測(cè)試環(huán)境

• MySQL 版本：8.0.28
• 測(cè)試表：test_db.a（字段i為char(1)類型）

案例 1：忽略大小寫的排序規(guī)則（utf8mb4_0900_ai_ci）

-- 設(shè)置字段排序規(guī)則為忽略大小寫
ALTER TABLE test_db.a CHANGE i i CHAR(1) COLLATE utf8mb4_0900_ai_ci;
-- 插入數(shù)據(jù)
INSERT INTO test_db.a VALUES ('a'), ('A');
-- 查詢：條件為'i = 'a''
SELECT * FROM test_db.a WHERE i = 'a'; 
-- 結(jié)果：返回'a'和'A'（兩者被視為相同）

案例 2：區(qū)分大小寫的排序規(guī)則（utf8mb4_0900_as_cs）

-- 設(shè)置字段排序規(guī)則為區(qū)分大小寫
ALTER TABLE test_db.a CHANGE i i CHAR(1) COLLATE utf8mb4_0900_as_cs;
-- 插入數(shù)據(jù)（同上）
INSERT INTO test_db.a VALUES ('a'), ('A');
-- 查詢：條件為'i = 'a''
SELECT * FROM test_db.a WHERE i = 'a'; 
-- 結(jié)果：僅返回'a'（嚴(yán)格區(qū)分大小寫）

結(jié)論

• 字段的排序規(guī)則直接決定查詢時(shí)的字符匹配邏輯；
• 若需查詢時(shí)區(qū)分大小寫，需將字段排序規(guī)則設(shè)置為*_cs（case-sensitive）類型；
• 若需忽略大小寫，使用*_ci（case-insensitive）類型。

五、實(shí)踐建議

• 明確大小寫需求：根據(jù)業(yè)務(wù)場(chǎng)景選擇排序規(guī)則。例如，用戶名通常需要區(qū)分大小寫（用*_cs），而商品名稱可能忽略大小寫（用*_ci）。

• 統(tǒng)一配置層級(jí)：建議在數(shù)據(jù)庫或表級(jí)別統(tǒng)一設(shè)置排序規(guī)則，避免列級(jí)別配置混亂（特殊字段單獨(dú)覆蓋）。

查詢時(shí)臨時(shí)覆蓋：如需臨時(shí)改變匹配邏輯，可在查詢中指定排序規(guī)則：

-- 臨時(shí)按區(qū)分大小寫查詢（即使字段默認(rèn)不區(qū)分）
SELECT * FROM test_db.a WHERE i COLLATE utf8mb4_0900_as_cs = 'a';

• 注意連接級(jí)影響：客戶端連接的collation_connection變量會(huì)影響字符串常量的匹配邏輯，若需一致行為，建議在字段級(jí)別顯式指定排序規(guī)則。

總結(jié)

MySQL 排序規(guī)則是控制字符比較邏輯的關(guān)鍵機(jī)制，其核心作用體現(xiàn)在查詢階段的匹配與排序行為。通過理解字符集與排序規(guī)則的關(guān)聯(lián)、掌握多層級(jí)配置方法，并結(jié)合業(yè)務(wù)場(chǎng)景選擇合適的規(guī)則（如區(qū)分 / 忽略大小寫），可確保查詢結(jié)果符合預(yù)期，避免因字符匹配問題導(dǎo)致的業(yè)務(wù)異常。

到此這篇關(guān)于MySQL 排序規(guī)則Collation實(shí)例詳解的文章就介紹到這了,更多相關(guān)mysql排序規(guī)則內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論