MySQL 中文模糊查詢錯誤問題與 BINARY/UCASE 解決方案

在 MySQL 執行中文模糊查詢時，常出現「查 `%a%` 卻返回不含 'a' 的中文記錄」的誤判現象。經查閱官方手冊，結合 BINARY、UCASE()、CONCAT() 可徹底解決。

問題重現

SELECT id, title, name FROM achech_com.news WHERE title LIKE '%a%';

結果混雜：部份 title 確實含 'a'，部份純中文卻也被錯誤返回。

階段 1：加入 BINARY 精確比對

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

結果正確率大幅提升，但 BINARY 區分大小寫，導致 'Achech' 與 'achech' 結果不一致。

階段 2：統一大小寫再比對

利用 UCASE() 將欄位與關鍵字同步轉大寫，再配合 CONCAT() 動態組裝萬用字元：

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

等效寫法：WHERE BINARY UCASE(title) LIKE UCASE('%a%')。

原理說明

1. BINARY：強制二進位比對，避免字元集排序規則導致的誤判。
2. UCASE()：消除大小寫差異，確保 'A'/'a' 視為同一字元。
3. CONCAT('%', UCASE(關鍵字), '%')：程式端只需傳入純關鍵字，SQL 自動補齊萬用字元，防止注入且跨語言通用。

效能提醒

此寫法會導致全表掃描，無法使用索引，查詢速度會較原生 LIKE 慢數毫秒至數十毫秒。若資料量極大且查詢頻繁，建議改用全文索引或專用搜尋引擎。