萬盛學電腦網

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

MySQL 大小寫敏感查詢:BINARY 陷阱與 COLLATE 高效能方案

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。

方案一:BINARY 關鍵字 —— 簡單但毀效能

SELECT * FROM t WHERE BINARY col LIKE 'a%';  -- 僅匹配小寫開頭
SELECT * FROM t WHERE BINARY col = 'Value';  -- 精確匹配

建表時指定 BINARY

CREATE TABLE t (col VARCHAR(20) BINARY);  -- 欄位級強制二進制比較

實測 30 萬筆資料效能對比

-- 使用 BINARY
SELECT * FROM `tableName` WHERE (BINARY weixin = 'value') LIMIT 1;
-- 執行計劃:type=ALL (全表掃描), rows=300,000, Extra=Using where
-- 耗時:~1.2 秒

BINARY 全表掃描執行計劃

結論: BINARY 強制逐行二進制比較,**完全無法使用索引**,資料量一大效能崩塌,嚴禁生產環境核心查詢使用。

方案二:COLLATE utf8mb4_bin —— 走索引、高效能、標準 SQL

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+)

COLLATE 索引查找執行計劃

深度解析:為什麼 COLLATE 能走索引而 BINARY 不能?

維度BINARY / 列定義 BINARYCOLLATE utf8mb4_bin
比較單位字節級 (Byte-by-byte)字符級,按二進制碼點排序
索引相容性破壞索引順序,優化器放棄索引保持索引順序 (二進制碼點序),優化器可用索引
標準相容MySQL 擴展SQL 標準 COLLATE 子句
字元集綁定隱式綁定表/欄位字元集顯式指定,支援跨字元集比較
適用場景極小表、臨時腳本、非索引欄位**所有生產查詢、索引欄位、跨庫比較**

最佳實踐:從建表到查詢的完整鏈路

1. 建表階段:預設字元集 + 顯式索引

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;

2. 查詢階段:按需覆蓋校對規則

-- 大小寫敏感精確匹配 (走索引)
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';

3. 連線層級設定 (可選)

-- 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**

擴展:其他資料庫對比

資料庫大小寫敏感語法索引支援
PostgreSQLWHERE col = 'Val' COLLATE "C" 或 citext 擴展✅ 支援
SQL ServerWHERE col COLLATE Latin1_General_CS_AS = 'Val'✅ 支援
OracleWHERE col = 'Val' (預設敏感)✅ 支援
MySQLWHERE col = 'Val' COLLATE utf8mb4_bin✅ 支援 (唯一推薦方式)

總結

MySQL 大小寫敏感查詢,**棄用 BINARY,擁抱 COLLATE utf8mb4_bin**。後者符合 SQL 標準、完美利用 B+ 樹索引、效能提升三個數量級,是生產環境的唯一正解。建表預設 ci,查詢按需 cs,靈活且高效。

copyright © 萬盛學電腦網 all rights reserved