MySQL 查詢強制區分大小寫：BINARY 關鍵字與表級字符集兩種方案

背景痛點

MySQL 默認不區分大小寫 (默認字符集 utf8_general_ci，_ci = case insensitive)。用戶名、編碼等業務場景若需嚴格區分大小寫，直接查詢會返回錯誤結果：

-- 以下兩條結果完全一致 (預期不同)
SELECT * FROM `user` WHERE name LIKE 'a%';
SELECT * FROM `user` WHERE name LIKE 'A%';

方案一：查詢級強制區分 (推薦，零風險)

在 WHERE 條件中添加 BINARY 關鍵字，將字符串比較轉為二進制逐字節比較：

SELECT * FROM `user` WHERE BINARY name LIKE 'a%';  -- 僅匹配小寫 a 開頭
SELECT * FROM `user` WHERE BINARY name LIKE 'A%';  -- 僅匹配大寫 A 開頭

• 優點：不改表結構、不鎖表、不影響現有數據、靈活控制單條查詢
• 適用：已上線大表 (百萬行+)，ALTER TABLE 可能導致鎖表宕機

方案二：表/列級永久區分 (建表時規劃)

在列定義中添加 BINARY 屬性，或指定 _bin 校對規則：

-- 列級
CREATE TABLE `user` (
  name VARCHAR(20) BINARY
);

-- 或指定校對規則 (效果相同)
CREATE TABLE `user` (
  name VARCHAR(20) COLLATE utf8_bin
);

• 優點：一次配置，永久生效，應用層無需感知
• 缺點：建表後修改需 ALTER TABLE ... MODIFY COLUMN ... BINARY，大表操作高風險
• 全庫級：CREATE DATABASE db_name CHARACTER SET utf8 COLLATE utf8_bin; (慎用，影響所有字符串比較)

深度補充：校對規則選型指南

MySQL 8.0 默認 utf8mb4_0900_ai_ci (不區分大小寫、不區分重音)，如需區分大小寫請顯式指定 _as_cs 或 _bin。

性能提示

• BINARY 會導致索引失效 (全表掃描)，大表慎用
• 高頻區分大小寫查詢建議：建表時指定 COLLATE utf8_bin，配合索引使用
• 臨時方案：WHERE name COLLATE utf8_bin LIKE 'A%' 效果等同 BINARY