MySQL 中文模糊查詢亂碼問題：BINARY + UCASE + CONCAT 精確匹配方案

MySQL LIKE 模糊查詢中英文混合亂碼：根因與標準解法

現象：WHERE title LIKE '%a%' 返回大量不含「a」的中文記錄。根因：非二進制排序規則下，多字節中文字符的某些字節值與 ASCII 字母重疊，導致誤匹配。本文提供標準化解決方案，兼顧大小寫不敏感與精確匹配。

根因分析

• MySQL 預設排序規則（如 utf8mb4_general_ci）為**不區分大小寫、按字符集權重比較**
• 中文字符在 UTF-8/GBK 編碼中，部分字節落在 ASCII 字母範圍（0x61-0x7A）
• LIKE '%a%' 在字節層面匹配，非字符層面 → 中文內碼字節命中「a」

標準解法三步走

步驟 1：BINARY 強制字節級精確比較

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

效果：消除中文誤匹配。
副作用：區分大小寫，「Achech」≠「achech」。

步驟 2：UCASE 統一大小寫（解決大小寫敏感）

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

效果：標題與關鍵字同轉大寫，實現不區分大小寫精確匹配。

步驟 3：CONCAT 動態構建模式（工程化最佳實踐）

-- 應用層傳入關鍵字變數 @keyword
SELECT id, title, name FROM news
WHERE BINARY UCASE(title) LIKE CONCAT('%', UCASE(@keyword), '%');

優勢：
• 應用層只需傳入原始關鍵字，無需手動加 %
• 關鍵字同樣經 UCASE，大小寫完全中和
• 防注入：參數化查詢中 @keyword 作為值傳遞，非 SQL 拼接

完整參數化查詢範例

<?php
$keyword = $_GET['q'] ?? '';
$stmt = $conn->prepare("
    SELECT id, title, name FROM news
    WHERE BINARY UCASE(title) LIKE CONCAT('%', UCASE(?), '%')
");
$stmt->bind_param("s", $keyword);
$stmt->execute();
$result = $stmt->get_result();
// ...
?>

替代寫法（等價）

WHERE BINARY UCASE(title) LIKE UCASE(CONCAT('%', ?, '%'))

效果相同，UCASE 包裹整個模式串。

效能與索引考量

生產環境建議

1. 小表/低頻：直接用 BINARY UCASE + CONCAT 方案，簡單可靠
2. 大表/高頻：
   a. 增加 title_upper VARCHAR(255) GENERATED ALWAYS AS (UCASE(title)) STORED 虛擬欄位 + 索引
   b. 查詢 WHERE title_upper LIKE CONCAT('%', UCASE(?), '%') (仍全掃，但省函數計算)
   c. 或遷移至 Elasticsearch/全文索引
3. 字符集統一：資料庫、表、欄位、連接全部 utf8mb4，避免編碼轉換導致的二次亂碼
4. 關鍵字預處理：應用層 trim()、過濾特殊字符（%、_、\）防止 LIKE 通配符注入

總結

BINARY UCASE(title) LIKE CONCAT('%', UCASE(?), '%') 是解決 MySQL 中英文混合模糊查詢亂碼的**標準工程化方案**：精確匹配、大小寫不敏感、參數化安全、易於維護。大數據量場景請配合全文索引或搜尋引擎。

1. 上一篇文章： MySQL 查詢效能優化三部曲（一）：預處理語句 Prepared Statement 綁定查詢深度解析
2. 下一篇文章： MySQL 數據遷移至 SQL Server 完整指南：ODBC 數據源配置與導入嚮導實戰

MySQL 資料庫相關知識

MySQL 環境變量完整指南：客戶端、服務端、構建時三大類 30+ 變量速查

MySQL 程序啟動時讀取環境變量決定默認行為，分類速查：

MySQL JOIN 深度解析：LEFT/RIGHT/INNER JOIN 語法差異、執行邏輯與常見誤區實測

引言 JOIN 是 SQL 查詢的核心操作，但 LEFT J

一致性哈希算法深度解析：原理、虛擬節點與分佈式緩存架構實戰

傳統取模算法的災難性缺陷 分佈式緩存集群N台節點，路由算法h

MySQL 中文全文索引插件 mysqlcft 安裝與使用完整指南

為什麼需要 mysqlcft？ MySQL 內建全文索引**