MySQL LIKE 模糊查詢全攻略：通配符、大小寫、中文編碼、索引效能與 PHP 實戰

前言

LIKE 是 SQL 模糊比對的核心運算子，配合通配符 % (任意長度)、_ (單字元) 實現前綴、後綴、包含、單字元匹配。本文系統講解語法、大小寫敏感度、中文編碼坑、索引使用條件、效能優化與 PHP 參數化查詢實戰。

一、基本語法與通配符

二、大小寫敏感度

2.1 預設行為

• 非二進制字串 (CHAR、VARCHAR、TEXT)：依**排序規則**決定。
  utf8mb4_general_ci、utf8mb4_unicode_ci → **不區分大小寫**。
  utf8mb4_bin、latin1_bin → **區分大小寫**。
• 二進制字串 (BINARY、VARBINARY、BLOB)：永遠區分大小寫。

2.2 強制區分大小寫

SELECT * FROM user WHERE username LIKE BINARY '%Admin%';  -- 僅匹配 Admin
SELECT * FROM user WHERE username COLLATE utf8mb4_bin LIKE '%Admin%';  -- 同效

2.3 強制不區分大小寫

SELECT * FROM user WHERE username COLLATE utf8mb4_general_ci LIKE '%admin%';

三、中文編碼與模糊查詢亂碼

3.1 根因

• 資料表/欄位排序規則為 utf8mb4_general_ci，但連線字元集設為 latin1 或 gbk → 位元組序列錯位，導致 LIKE '%中文%' 匹配到錯誤位元組組合。
• 儲存時編碼與查詢時編碼不一致。

3.2 解決方案

1. **統一全鏈路 UTF8MB4**：
   SET NAMES utf8mb4 (連線)、CHARSET=utf8mb4 (表/庫)、COLLATE=utf8mb4_general_ci (欄位)。
2. **查詢時強制二進制比對** (不依賴排序規則)：
   SELECT * FROM user WHERE username LIKE BINARY CONCAT('%', '中文', '%');
3. **忽略英文大小寫且支援中文** 的通用寫法：
   SELECT * FROM user WHERE UPPER(username) LIKE BINARY CONCAT('%', UPPER('a中文b'), '%');

四、LIKE 與索引效能

4.1 索引生效條件

**原理**：B+樹索引按字典序排序，前綴固定才能定位範圍；前導通配符無法定位起始點。

4.2 效能優化策略

• **業務允許時，改為前綴匹配** LIKE 'keyword%'。
• **全文搜尋需求** → 使用 FULLTEXT INDEX + MATCH() AGAINST() (MySQL 5.6+ 支援中文 ngram parser)。
• **前綴固定 + 後綴模糊** LIKE 'prefix_%' 仍可用索引。
• **避免在大表對非索引欄位用 %...%**；必要時加 LIMIT、縮小掃描範圍 (WHERE id > 10000 AND col LIKE '%x%')。

五、PHP 參數化查詢實戰

5.1 PDO (推薦)

$pdo = new PDO('mysql:host=127.0.0.1;dbname=test;charset=utf8mb4', 'user', 'pass', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false,  // 真正預處理
]);

$keyword = 'admin';
$stmt = $pdo->prepare('SELECT id, username FROM user WHERE username LIKE ?');
$stmt->execute(["%{$keyword}%"]);  // 通配符在 PHP 端拼接，而非 SQL 端
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo "ID: {$row['id']}, User: {$row['username']}\n";
}

5.2 mysqli

$mysqli = new mysqli('127.0.0.1', 'user', 'pass', 'test');
$mysqli->set_charset('utf8mb4');

$keyword = 'admin';
$stmt = $mysqli->prepare('SELECT id, username FROM user WHERE username LIKE ?');
$like = "%{$keyword}%";
$stmt->bind_param('s', $like);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    echo "ID: {$row['id']}, User: {$row['username']}\n";
}

5.3 安全提醒

• **永遠使用參數化查詢**，杜絕 SQL 注入。
• 通配符 %、_ 在 PHP 端拼接，傳入完整模式字串給 ? 佔位符。
• 若用戶輸入含 %、_ 需按字面匹配，需轉義：str_replace(['%', '_'], ['\\%', '\\_'], $input)。

六、常見問題速查

結語： LIKE 雖簡單，但編碼、排序規則、索引匹配規則暗藏玄機。掌握「前綴用索引、中文統一 UTF8MB4、大小寫顯式控制、參數化防注入」，即能寫出正確且高效的模糊查詢。