MySQL 預設大小寫不敏感 (Case-Insensitive),'a' = 'A' 為真。但業務常需精確匹配:用戶名區分大小寫、驗證碼校驗、Case-Sensitive 索引查詢。本文實測 30 萬筆資料,揭示 BINARY 導致全表掃描的效能災難,推薦 COLLATE utf8mb4_bin 走索引的高效能最佳實踐。
SELECT * FROM t WHERE col LIKE 'a%'; -- 同時匹配 'apple', 'Apple', 'APPLE'
SELECT * FROM t WHERE col LIKE 'A%'; -- 結果完全相同
原因:預設校對規則 utf8mb4_general_ci (或 utf8_general_ci) 中 ci = Case Insensitive。
SELECT * FROM t WHERE BINARY col LIKE 'a%'; -- 僅匹配小寫開頭
SELECT * FROM t WHERE BINARY col = 'Value'; -- 精確匹配
CREATE TABLE t (col VARCHAR(20) BINARY); -- 欄位級強制二進制比較
-- 使用 BINARY
SELECT * FROM `tableName` WHERE (BINARY weixin = 'value') LIMIT 1;
-- 執行計劃:type=ALL (全表掃描), rows=300,000, Extra=Using where
-- 耗時:~1.2 秒

結論: BINARY 強制逐行二進制比較,**完全無法使用索引**,資料量一大效能崩塌,嚴禁生產環境核心查詢使用。
SELECT * FROM `tableName` WHERE weixin = 'value' COLLATE utf8mb4_bin LIMIT 1;
-- 使用 COLLATE
SELECT * FROM `tableName` WHERE weixin = 'value' COLLATE utf8mb4_bin LIMIT 1;
-- 執行計劃:type=ref (索引查找), rows=1, Extra=Using where
-- 耗時:~0.001 秒 (提升 1000x+)

| 維度 | BINARY / 列定義 BINARY | COLLATE utf8mb4_bin |
|---|---|---|
| 比較單位 | 字節級 (Byte-by-byte) | 字符級,按二進制碼點排序 |
| 索引相容性 | 破壞索引順序,優化器放棄索引 | 保持索引順序 (二進制碼點序),優化器可用索引 |
| 標準相容 | MySQL 擴展 | SQL 標準 COLLATE 子句 |
| 字元集綁定 | 隱式綁定表/欄位字元集 | 顯式指定,支援跨字元集比較 |
| 適用場景 | 極小表、臨時腳本、非索引欄位 | **所有生產查詢、索引欄位、跨庫比較** |
CREATE TABLE users (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
UNIQUE KEY uk_username (username), -- 索引使用表預設校對規則
UNIQUE KEY uk_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- 大小寫敏感精確匹配 (走索引)
SELECT * FROM users WHERE username = 'Admin' COLLATE utf8mb4_bin;
-- 大小寫敏感前綴匹配 (走索引)
SELECT * FROM users WHERE username LIKE 'Admin%' COLLATE utf8mb4_bin;
-- 大小寫不敏感 (預設行為,走索引)
SELECT * FROM users WHERE username = 'admin';
-- Session 級別改變預設比較行為
SET SESSION collation_connection = utf8mb4_bin;
-- 後續無 COLLATE 子句的查詢均為敏感模式
COLLATE utf8_bin vs utf8mb4_bin — 統一用 utf8mb4,支援 Emoji 且為未來標準WHERE BINARY col = 'val' 以為能走索引 — **永遠走不了**BINARY 以此解決大小寫 — 導致該欄位所有查詢都無法用索引utf8mb4_general_ci,**查詢時顯式 COLLATE utf8mb4_bin**| 資料庫 | 大小寫敏感語法 | 索引支援 |
|---|---|---|
| PostgreSQL | WHERE col = 'Val' COLLATE "C" 或 citext 擴展 | ✅ 支援 |
| SQL Server | WHERE col COLLATE Latin1_General_CS_AS = 'Val' | ✅ 支援 |
| Oracle | WHERE col = 'Val' (預設敏感) | ✅ 支援 |
| MySQL | WHERE col = 'Val' COLLATE utf8mb4_bin | ✅ 支援 (唯一推薦方式) |
MySQL 大小寫敏感查詢,**棄用 BINARY,擁抱 COLLATE utf8mb4_bin**。後者符合 SQL 標準、完美利用 B+ 樹索引、效能提升三個數量級,是生產環境的唯一正解。建表預設 ci,查詢按需 cs,靈活且高效。