MySQL 大小寫敏感解決方案：BINARY 操作符零改表實現精確比對

問題場景

帳號/密碼、角色名稱創建與登入驗證未區分大小寫，導致「Admin」與「admin」視為同一賬號，嚴重安全隱患。

三種方案對比

BINARY 實戰

-- 登入驗證：強制大小寫敏感比對
SELECT * FROM users
WHERE username = 'Admin' AND BINARY password = 'Secret123';

-- 建立賬號檢查：避免大小寫衝突
SELECT COUNT(*) FROM users WHERE BINARY username = 'admin';

驗證測試

mysql> SELECT BINARY 'ABCD' = 'abcd' AS ret1, 'ABCD' = 'abcd' AS ret2;
+------+------+
| ret1 | ret2 |
+------+------+
|    0 |    1 |  -- BINARY 區分大小寫，普通比對不區分（預設校對 _ci）
+------+------+

原理

• BINARY 非函數，屬**類型轉換操作符**，將後續字串視為二進位字串逐字節比較。
• 等同於 CAST(str AS BINARY) 或 COLLATE utf8mb4_bin。
• 索引仍可使用（若欄位校對規則為 _bin 或建立 _bin 索引），否則可能全表掃描。

最佳實踐建議

1. 新專案：欄位定義階段直接指定 COLLATE utf8mb4_bin 或 utf8mb4_0900_as_cs（MySQL 8.0），一勞永逸。
2. 存量專案：核心驗證邏輯（登入、註冊、權限檢查）優先加 BINARY，其餘查詢視業務容忍度決定。
3. 配合 EXPLAIN 確認索引生效，避免 BINARY 導致隱式轉換失效。