業務發現帳號/密碼、角色名稱「不區分大小寫」,根因是 MySQL 預設字符集校對規則 _ci (Case Insensitive)。不想動全庫字符集,採用 列級 BINARY 操作符 精準修復登入與建帳邏輯。
BINARY 不是函數,是類型轉換操作符,將後續字串視為二進位字串逐位元組比較。COLLATE latin1_bin 或 utf8mb4_bin:區分大小寫、區分重音、按位元組序比較。mysql> SELECT BINARY 'ABCD' = 'abcd' AS ret1, 'ABCD' = 'abcd' AS ret2;
+------+------+
| ret1 | ret2 |
+------+------+
| 0 | 1 |
+------+------+
-- ret1=0 (不相等),ret2=1 (預設 ci 相等)
-- 原:WHERE username = p_user AND password = p_pass
-- 改:
WHERE BINARY username = p_user AND BINARY password = p_pass
-- 原:SELECT COUNT(*) FROM users WHERE name = p_name
-- 改:
SELECT COUNT(*) FROM users WHERE BINARY name = p_name;
CREATE UNIQUE INDEX uk_username_cs ON users (username(50))
USING BTREE
COMMENT '案敏唯一索引'; -- 8.0 支援函數索引
-- 或建立虛擬欄
ALTER TABLE users ADD username_cs VARCHAR(50)
GENERATED ALWAYS AS (BINARY username) VIRTUAL,
ADD UNIQUE INDEX uk_username_cs (username_cs);
BINARY col = 'val' 導致索引失效(函數/操作符作用於欄位)。| 方案 | 改動範圍 | 效能 | 維護成本 | 推薦度 |
|---|---|---|---|---|
修改庫/表預設校對規則 _bin | 全庫/全表 | 索引可用 | 高 (影響所有比較) | ⭐ (風險大) |
欄位級 COLLATE utf8mb4_bin | 單欄 | 索引可用 | 中 (需 ALTER TABLE) | ⭐⭐⭐ |
查詢加 BINARY | 單 SQL | 索引失效 | 低 | ⭐⭐ (小表/低頻) |
| 虛擬欄 + 索引 (8.0+) | 單欄 | 索引可用 | 低 | ⭐⭐⭐⭐⭐ (大表最佳) |