MySQL 英文字母大小寫敏感性完整指南：從字符集到實戰技巧

MySQL 英文字母大小寫敏感性完整指南：從字符集到實戰技巧

MySQL 查詢時大小寫是否敏感，取決於校對規則。預設通常為不敏感（如 utf8_general_ci），但可通過多層級配置實現敏感查詢。本文系統梳理字符集/校對規則概念、四級配置層級、以及不修改表結構即可實現大小寫敏感的實用技巧。

核心概念：字符集與校對規則

• 字符集：符號與編碼的集合（如 utf8、gbk、latin1）。
• 校對規則：在字符集內比較字符的規則，命名約定為 字符集_語言_ci/cs/bin，其中 _ci 為大小寫不敏感，_cs 為敏感，_bin 為二進制比較。
• 每個字符集有默認校對規則（utf8 默認 utf8_general_ci），可通過 SHOW COLLATION 查看。

四級配置層級與繼承關係

MySQL 允許在服務器、數據庫、表、連接四個層級設置字符集與校對規則，優先級由高到低：

1. 服務器級：my.cnf 中設 collation_server=utf8_bin 或啟動參數 --collation-server。僅影響重啟後新建的庫/表/列。
2. 數據庫級：CREATE DATABASE db CHARACTER SET utf8 COLLATE utf8_bin; 影響該庫新建表。
3. 表級：CREATE TABLE t(...) DEFAULT CHARSET utf8 COLLATE utf8_bin; 影響該表新建列。
4. 連接級：通過 character_set_client、character_set_connection、collation_connection 等會話變量控制，SET NAMES utf8 COLLATE utf8_bin 即可臨時生效。

注意：修改上層配置不會級聯更新已存在的下層對象，需顯式 ALTER。

實戰驗證：各層級設置大小寫敏感

1. 服務器級

[mysqld]
collation_server = utf8_bin

重啟後建庫建表，查詢 WHERE name='aaa' 僅匹配小寫。

2. 數據庫級

CREATE DATABASE yutest DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

同樣實現敏感查詢。

3. 表級

CREATE TABLE t1(name VARCHAR(10)) DEFAULT CHARSET utf8 COLLATE utf8_bin;

4. 連接級（最靈活，無需改結構）

SET NAMES utf8 COLLATE utf8_bin;
-- 或
SET collation_connection = utf8_bin;

不改表結構也能區分大小寫的兩大技巧

技巧一：SQL 中使用 COLLATE 子句

SELECT * FROM t1 WHERE name COLLATE utf8_bin = 'ABC';  -- 只匹配大寫
SELECT * FROM t1 WHERE name = 'ABC';                   -- 預設不敏感，匹配 ABC/Abc/abc

適用於 WHERE、ORDER BY、GROUP BY、聚合函數等。

技巧二：BINARY 操作符（COLLATE ..._bin 的簡寫）

SELECT * FROM t1 WHERE BINARY name = 'ABC';
SELECT * FROM t1 ORDER BY BINARY name;

BINARY 'str' 等價於 'str' COLLATE charset_bin，每個字符集均有對應的 _bin 校對規則。

常見坑與最佳實踐

• 預設不敏感：生產環境常用 utf8_general_ci，若業務需區分大小寫（如帳號、驗證碼），必須顯式指定 _bin 或 _cs 校對規則。
• 列級覆蓋：ALTER TABLE t MODIFY name VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_bin; 可單獨修改某列。
• 連接層統一：應用層建立連接時執行 SET NAMES utf8mb4 COLLATE utf8mb4_bin，確保全鏈路一致。

總結

MySQL 大小寫敏感性由校對規則決定，四級配置層層繼承。生產中推薦「庫/表級預設 _ci，關鍵業務列級或查詢級顯式指定 _bin」，兼顧通用性與精確性。