MySQL 大小寫敏感查詢完整指南：校對規則四層級配置與繞過技巧

核心概念

• 字符集：符號集合與編碼（如 utf8）
• 校對規則：比較規則（如 utf8_general_ci 不敏感、utf8_bin 敏感）
• 命名慣例：`__`

四層級預設繼承關係

Server (my.cnf / 啟動參數)
  ↓
Database (CREATE DATABASE ... COLLATE)
  ↓
Table (CREATE TABLE ... COLLATE)
  ↓
Column (col VARCHAR(10) COLLATE utf8_bin)
  ↓
Connection (SET NAMES / character_set_connection)

修改上層不影響已存在的下層對象。

層級配置實測

1. 伺服器級

# my.cnf
[mysqld]
collation_server = utf8_bin
# 重啟後新建庫繼承 utf8_bin，查詢大小寫敏感

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; -- 影響當前會話比較行為

查詢層面繞過技巧（不改結構）

實測對比

-- 表預設 utf8_general_ci (CI)
SELECT * FROM t1 WHERE name = 'ABC';   -- 返回 ABC, Abc, abc (3 行)
SELECT * FROM t1 WHERE BINARY name = 'ABC'; -- 僅返回 ABC (1 行)

避坑指南

• my.cnf 不支援 collation_database 參數（啟動報錯）
• 唯一索引/主鍵受校對規則影響：CI 下 'a' 與 'A' 衝突
• ORDER BY、GROUP BY、DISTINCT 皆遵循欄位校對規則
• 跨表關聯欄位校對規則必須一致，否則無法用索引