MySQL 中文模糊查詢精準化：BINARY + UCASE + CONCAT 組合技巧

問題現象

使用 LIKE '%a%' 查詢中文標題時，會誤命中不含 'a' 的中文記錄（因字符集排序規則導致某些多位元組字元被誤判）。

解決方案演進

第一步：加上 BINARY 強制二進位比較

SELECT id, title FROM news WHERE BINARY title LIKE '%a%';

結果精準，但區分大小寫，'Achech' 與 'achech' 結果不同。

第二步：統一轉大寫 + BINARY

SELECT id, title FROM news
WHERE BINARY UCASE(title) LIKE UCASE('%a%');

或使用 CONCAT 組裝萬用字元，程式碼更簡潔：

SELECT id, title FROM news
WHERE BINARY UCASE(title) LIKE CONCAT('%', UCASE('a'), '%');

完整 SQL 範本

-- 參數化版本（防注入）
SET @keyword = '搜尋關鍵字';
SELECT id, title, name
FROM news
WHERE BINARY UCASE(title) LIKE CONCAT('%', UCASE(@keyword), '%');

原理解析

效能影響與優化

• 全表掃描：BINARY 導致無法使用索引，大表會慢。
• 優化建議：
  • 加上 FULLTEXT 索引（MySQL 5.6+ InnoDB 支援中文分詞需配置 ngram parser）。
  • 引入 Elasticsearch / Meilisearch 等專用搜尋引擎。
  • 若資料量中等（<10萬），可接受毫秒級延遲。

替代方案：COLLATE 指定二進位排序規則

SELECT * FROM news
WHERE title COLLATE utf8mb4_bin LIKE CONCAT('%', 'a', '%');

utf8mb4_bin 同樣逐位元組比較，但不自動處理大小寫，仍需搭配 UCASE()。