MySQL 預設大小寫不敏感（依字符集 Collation），但業務常需精確匹配（如驗證碼、大小寫敏感代碼）。本文對比 BINARY、欄位屬性 BINARY、COLLATE utf8_bin 三種方案的執行計畫與效能，揭示為什麼 COLLATE 是唯一支援索引的正解。

預設行為：大小寫不敏感

SELECT * FROM table_name WHERE a LIKE 'a%';
SELECT * FROM table_name WHERE a LIKE 'A%';
-- 兩者結果完全相同（預設 utf8_general_ci / utf8mb4_general_ci）

方案對比實測（30萬行測試表，weixin 列有索引）

❌ 方案一：BINARY 關鍵字（查詢層面）

SELECT * FROM `tableName` WHERE (BINARY weixin = 'value') LIMIT 1;

執行計畫：type=ALL（全表掃描），無法使用索引。
原因：BINARY 作為函數/運算符作用於列上，破壞索引列的原始值，導致索引失效。

❌ 方案二：欄位定義 BINARY 屬性（Schema 層面）

CREATE TABLE t (a VARCHAR(20) BINARY);

本質：相當於 COLLATE utf8_bin（或對應字符集的 _bin）。雖可用索引，但影響全表所有查詢，缺乏靈活性，不推薦。

✅ 方案三：COLLATE utf8_bin（查詢層面指定排序規則）

SELECT * FROM `tableName` WHERE weixin = 'value' COLLATE utf8_bin LIMIT 1;

執行計畫：type=ref，正常使用索引，rows=1。
原理：COLLATE 只改變比較規則，不改變列存儲值，索引結構（B+ 樹按字符集排序）依然有效。

效能量化對比

方案	索引使用	30萬行耗時	適用場景
BINARY 關鍵字	❌ 全表掃描	~800ms	極小表、臨時調試
欄位 BINARY 屬性	✅ 可用	~2ms	全表業務均需大小寫敏感
COLLATE utf8_bin	✅ 可用	~2ms	推薦：單查詢精確控制

字符集與 Collation 選擇指南

進階：大小寫敏感排序與分組

-- 排序區分大小寫
SELECT * FROM t ORDER BY name COLLATE utf8mb4_bin;

-- 分組區分大小寫
SELECT name COLLATE utf8mb4_bin, COUNT(*) FROM t GROUP BY name COLLATE utf8mb4_bin;

最佳實踐總結

1. 查詢層面用 COLLATE xxx_bin：精確控制、不影響其他查詢、索引全用
2. 禁止在 WHERE 條件列上使用 BINARY 關鍵字：索引失效殺手
3. Schema 設計階段：若整個業務天然大小寫敏感（如代碼、Token），欄位定義用 COLLATE utf8mb4_bin
4. 字符集統一：資料庫/表/欄位/連接字符集保持一致，避免隱式轉換導致索引失效
5. EXPLAIN 驗證：上線前必看執行計畫，確認 type=ref/range/eq_ref 而非 ALL

總結

大小寫敏感查詢看似簡單，實則關係索引生死。BINARY 關鍵字是「索引殺手」，COLLATE utf8mb4_bin 才是「索引友好」的正規軍。牢記：比較規則與存儲分離，索引才能生效。

字符集	對應 _bin Collation	備註
utf8 / utf8mb3	utf8_bin	僅支援 BMP 字符（3 字節）
utf8mb4	utf8mb4_bin / utf8mb4_0900_bin	完整 Unicode，8.0 預設
latin1	latin1_bin	單字節，效能最快但功能受限
gbk	gbk_bin	中文環境常用，支援漢字大小寫無意義