MySQL 大小寫敏感查詢：COLLATE utf8_bin 方案與效能對比

對比示例（30萬行測試表）

方案 1：BINARY 關鍵字（不走索引）

SELECT * FROM `tableName` WHERE (BINARY `weixin` = 'value') LIMIT 1;

EXPLAIN 顯示 type=ALL，掃描 30 萬行，耗時明顯。

方案 2：COLLATE utf8_bin（走索引）

SELECT * FROM `tableName` WHERE `weixin` = 'value' COLLATE utf8_bin LIMIT 1;

EXPLAIN 顯示 type=ref，key=idx_weixin，rows=1，毫秒級返回。

原理差異

• BINARY col：將列轉為二進制字符串比較，破壞索引有序性，優化器無法使用索引。
• col COLLATE utf8_bin：指定比較時的校對規則為二進制，索引結構仍按原校對構建，但比較時按字節碼匹配，**可利用現有索引**。

建表層面指定

CREATE TABLE t (
    weixin VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_bin,
    INDEX idx_weixin (weixin)
) ENGINE=InnoDB;

列級指定 utf8_bin 後，所有比較默認區分大小寫，無需 SQL 顯式 COLLATE。

其他校對規則

• utf8_general_ci：不區分大小寫，速度略快，排序精度略低（德語 ß 等）。
• utf8_unicode_ci：UCA 標準排序，精度高，稍慢。
• utf8_bin：按字節碼比較，區分大小寫、重音、特殊字符，最嚴格。

最佳實踐

1. 業務需大小寫敏感 → 列定義 COLLATE utf8_bin 或 utf8mb4_bin。
2. 臨時查詢需區分 → WHERE col = 'val' COLLATE utf8_bin。
3. 禁用 BINARY col 於大表 WHERE 條件，必走全表掃描。