通配符轉義核心問題

當查詢值本身包含 %、_、[、] 等通配符字符時，必須轉義，否則會被當作模式匹配符號導致結果錯誤。

MySQL 轉義機制

-- 1. ESCAPE 子句指定轉義字符 (推薦，標準 SQL)
SELECT * FROM customer WHERE name LIKE '%\%%' ESCAPE '\';  -- 查詢包含 % 的名字
SELECT * FROM customer WHERE name LIKE '%\_%' ESCAPE '\';  -- 查詢包含 _ 的名字

-- 2. 方括號轉義 (MySQL 特有，類似 T-SQL)
SELECT * FROM customer WHERE name LIKE '%[%]%';  -- 包含 %
SELECT * FROM customer WHERE name LIKE '%[_]%';  -- 包含 _

-- 3. 字符類轉義方括號本身
SELECT * FROM customer WHERE name LIKE '%[[]%';  -- 包含 [
SELECT * FROM customer WHERE name LIKE '%[]]%';  -- 包含 ] (] 在類首不需轉義)

完整轉義對照表

目標字符	ESCAPE '\' 寫法	方括號寫法	說明
%	\%	[%]	匹配任意長度
_	\_	[_]	匹配單字符
[	\[	[[]	字符類開頭
]	\]	[]] 或 [] (類首)	字符類結尾
\	\\	[\]	轉義字符本身
^	\^	[^] (類首為取反)	字符類取反/開頭錨點
-	\-	[-] (類首/尾)	字符範圍連接符

實戰場景

場景 1：查詢包含百分號的商品名

-- 數據: '100%纯棉', '50%off', '纯棉'
-- 需求: 找出包含 % 的商品

-- 方法 1: ESCAPE (標準、可移植)
SELECT * FROM products WHERE name LIKE '%\%%' ESCAPE '\';

-- 方法 2: 方括號 (MySQL 專用，簡潔)
SELECT * FROM products WHERE name LIKE '%[%]%';

-- 方法 3: 反轉思路 (不推薦，全表掃描)
SELECT * FROM products WHERE name NOT LIKE REPLACE(name, '%', '');

場景 2：查詢包含下劃線的用戶名

-- 數據: 'user_name', 'username', 'user-name'
SELECT * FROM users WHERE username LIKE '%\_%' ESCAPE '\';
SELECT * FROM users WHERE username LIKE '%[_]%';

場景 3：查詢包含方括號的日志

-- 數據: '[ERROR] Connection failed', '[WARN] Timeout', 'INFO Started'
SELECT * FROM logs WHERE message LIKE '%[[]%';  -- 包含 [
SELECT * FROM logs WHERE message LIKE '%[]]%';  -- 包含 ]

場景 4：PHP 應用層動態構建 LIKE 條件

 "$column LIKE ? ESCAPE '\\\\'",
        "param" => $pattern
    ];
}

// 使用
[$sql, $param] = buildLikeCondition('name', $userInput, true, true);  // 模糊查詢
$stmt = $pdo->prepare("SELECT * FROM users WHERE $sql");
$stmt->execute([$param]);

// 精確匹配 (無通配符)
[$sql, $param] = buildLikeCondition('code', $code, false, false);
$stmt = $pdo->prepare("SELECT * FROM products WHERE $sql");
$stmt->execute([$param]);

REGEXP 替代方案

-- 查詢包含 % 的記錄 (REGEXP 無需轉義 % _，但需轉義正則元字符)
SELECT * FROM customer WHERE name REGEXP '[%]';  -- 字符類匹配字面 %
SELECT * FROM customer WHERE name REGEXP '[_]';  -- 字符類匹配字面 _

-- 複雜模式：以 [ 開頭，以 ] 結尾
SELECT * FROM logs WHERE message REGEXP '^\[.*\]$';

性能對比

-- 測試表: 100萬行, name VARCHAR(100), 索引 idx_name(name)
-- 查詢包含 'test%' 的記錄 (字面 test%)

-- 方案 1: LIKE + ESCAPE (走索引範圍掃描)
EXPLAIN SELECT * FROM customer WHERE name LIKE 'test\%%' ESCAPE '\';
-- type=range, key=idx_name, rows=預估匹配行數

-- 方案 2: LIKE + 方括號 (同方案 1)
EXPLAIN SELECT * FROM customer WHERE name LIKE 'test[%]%';
-- type=range, key=idx_name

-- 方案 3: REGEXP (全表掃描)
EXPLAIN SELECT * FROM customer WHERE name REGEXP '^test%';
-- type=ALL, key=NULL, rows=1000000

結論: LIKE + 索引 完勝 REGEXP，前綴固定時務必用 LIKE。

避坑清單

• ☑️ 用戶輸入進 LIKE 條件**必須**轉義 %、_、\
• ☑️ 推薦統一用 ESCAPE '\\' 標準語法，跨數據庫兼容
• ☑️ 方括號語法 [%] [_] 簡潔但 MySQL 專用
• ☑️ 前綴固定查詢走索引，%prefix% / %suffix 全表掃描 → 上全文索引/ES
• ☑️ 參數化查詢綁定模式字符串，拼接 SQL 字符串極易注入/轉義漏洞
• ☑️ 字符集校驗：utf8mb4 下中文字符不涉及通配符，無需轉義

擴展：字符類進階用法

-- 匹配任意數字
WHERE code REGEXP '[0-9]'  -- 或 [[:digit:]]

-- 匹配任意字母
WHERE code REGEXP '[a-zA-Z]'  -- 或 [[:alpha:]]

-- 匹配非字母數字 (特殊字符)
WHERE name REGEXP '[^a-zA-Z0-9]'

-- 匹配連續 3 個數字
WHERE code REGEXP '[0-9]{3}'

-- 匹配郵箱格式 (簡易)
WHERE email REGEXP '^[^@]+@[^@]+\.[^@]+$'