萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 大小寫敏感解決方案:BINARY 操作符實現列級區分

業務發現帳號/密碼、角色名稱「不區分大小寫」,根因是 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 相等)

應用場景與改造

1. 登入驗證存儲過程

-- 原:WHERE username = p_user AND password = p_pass
-- 改:
WHERE BINARY username = p_user AND BINARY password = p_pass

2. 建帳/建角唯一性檢查

-- 原:SELECT COUNT(*) FROM users WHERE name = p_name
-- 改:
SELECT COUNT(*) FROM users WHERE BINARY name = p_name;

3. 唯一索引輔助(MySQL 8.0+)

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' 導致索引失效(函數/操作符作用於欄位)。
  • 小表 (≤ 10 萬) 全掃描可接受;大表必須配合虛擬欄索引或函數索引 (8.0+)。
  • 登入/註冊屬低頻寫入、高頻讀取,虛擬欄索引維護成本極低。

替代方案對比

最佳實踐建議

  1. 新表設計:關鍵業務欄位 (帳號、郵箱、手機) 直接定義 COLLATE utf8mb4_bin 或 utf8mb4_0900_as_cs。
  2. 存量表:加虛擬欄 + 唯一索引,應用層 SQL 不變(自動匹配虛擬欄索引)。
  3. 臨時修復/小表:SQL 直接加 BINARY,快速上線。
  4. 密碼欄位:永不儲存明文,存 bcrypt/argon2 雜湊值,驗證時在應用層比對,資料庫層面無需大小寫敏感。

用 BINARY 精準打擊大小寫不敏感痛點,配合虛擬欄索引實現零業務改造、高效能的列級案敏方案。

copyright © 萬盛學電腦網 all rights reserved
方案改動範圍效能維護成本推薦度
修改庫/表預設校對規則 _bin全庫/全表索引可用高 (影響所有比較)⭐ (風險大)
欄位級 COLLATE utf8mb4_bin單欄索引可用中 (需 ALTER TABLE)⭐⭐⭐
查詢加 BINARY單 SQL索引失效低⭐⭐ (小表/低頻)
虛擬欄 + 索引 (8.0+)單欄索引可用低⭐⭐⭐⭐⭐ (大表最佳)