解決 MySQL 中文模糊查詢誤判：BINARY、UCASE 與 CONCAT 精準檢索實戰

問題現象

在 WHERE title LIKE '%a%' 查詢中，純中文標題也會被誤判匹配，原因是預設校對規則 (Collation) 將英文字母與部分中文編碼視為相等。

解決方案演進

方案一：BINARY 精確匹配

SELECT * FROM news WHERE BINARY title LIKE '%a%';

• 優點：立即生效，不需改表結構。
• 缺點：區分大小寫，'Achech' 與 'achech' 結果不同。

方案二：UCASE 統一大寫 + BINARY

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

• 優點：不區分大小寫，精準匹配。
• 缺點：無法使用索引 (Full Table Scan)，大表效能差。

方案三：CONCAT 動態組裝模式 (推薦)

SELECT * FROM news 
WHERE BINARY UCASE(title) LIKE CONCAT('%', UCASE('搜尋關鍵字'), '%');

• 優點：程式碼端只需傳入關鍵字變數，自動處理萬用字元與大小寫。
• 缺點：同樣導致全表掃描。

進階優化：全文檢索與生成欄位

1. MySQL 5.7+ 原生全文索引：ALTER TABLE news ADD FULLTEXT(title) WITH PARSER ngram; (支援中文分詞)。
2. 生成欄位 + 索引 (MySQL 5.7+)：

   ALTER TABLE news 
     ADD COLUMN title_upper VARCHAR(200) 
     GENERATED ALWAYS AS (UPPER(title)) STORED,
     ADD INDEX idx_title_upper (title_upper);
   
   -- 查詢可用索引
   SELECT * FROM news WHERE title_upper LIKE CONCAT('%', UPPER('關鍵字'), '%');

3. 外部搜尋引擎：Elasticsearch、Meilisearch 處理複雜中文分詞、同義詞、權重排序。

效能對比