LIKE 基本語法與通配符

SELECT 欄位 FROM 資料表 WHERE 欄位 LIKE 模式;

通配符	說明	範例	匹配範例
%	匹配任意長度字串（含 0 字元）	'王%'	王小明、王、王大明李
_	匹配單一字元	'王_'	王小、王大（不匹配「王」）
[charlist]	字元集合中的任一字元	'[abc]%'	以 a/b/c 開頭
[^charlist]	不在字元集合中的字元	'[^0-9]%'	非數字開頭

> **注意**：`[ ]` 語法為 SQL Server/Access 專用，MySQL **不支援**。MySQL 請用 `REGEXP` 替代。

MySQL 特有：ESCAPE 子句

查詢包含通配符本身的字串：

-- 查詢實際包含 "100%" 的商品
SELECT * FROM products WHERE name LIKE '%100\%%' ESCAPE '\';

-- 查詢包含底線 "_" 的欄位
SELECT * FROM table WHERE col LIKE '%\_%' ESCAPE '\';

大小寫敏感度控制

• 預設：依照排序規則，`utf8mb4_general_ci` 不分大小寫
• 區分大小寫：`WHERE col LIKE BINARY 'ABC%'` 或 `WHERE col COLLATE utf8mb4_bin LIKE 'ABC%'`

實戰範例

常見查詢模式

-- 1. 前綴匹配（可用索引）
WHERE name LIKE '張%'

-- 2. 後綴匹配（無法用索引）
WHERE email LIKE '%@gmail.com'

-- 3. 包含匹配（無法用索引）
WHERE content LIKE '%錯誤%'

-- 4. 固定長度模式（可用索引）
WHERE phone LIKE '09_______'  -- 10 碼手機號

-- 5. 多條件組合
WHERE (title LIKE '%關鍵字%' OR content LIKE '%關鍵字%')
  AND status = 'published'

⚡ 效能殺手：前導通配符 %xxx%

WHERE name LIKE '%關鍵字%' 會導致 **全表掃描**，資料量大時極慢。

優化策略對比

策略	原理	適用場景	優點	缺點
前綴索引	只索引前 N 字元	固定前綴查詢（如郵遞區號、電話）	簡單、原生支援	不支援中間/後綴匹配
FULLTEXT 索引	倒排索引	自然語言、關鍵字搜尋	支援相關性、中文需 ngram	不支援任意子字串、有最小詞長
生成欄位 + 索引	反轉字串建索引	後綴查詢（如域名、副檔名）	巧用 B-tree 索引	需額外儲存空間、維護成本
外部搜尋引擎	Elasticsearch 等	複雜搜尋、高併發、海量資料	功能最完整	架構複雜、延遲同步
應用層快取	Redis 熱門關鍵字快取	重複熱門查詢	極速回應	資料一致性挑戰

生成欄位反轉索引實戰（解決後綴查詢）

ALTER TABLE emails
ADD COLUMN reversed_email VARCHAR(255) 
    GENERATED ALWAYS AS (REVERSE(email)) STORED,
ADD INDEX idx_reversed_email (reversed_email);

-- 查詢 @gmail.com 結尾
SELECT * FROM emails 
WHERE reversed_email LIKE REVERSE('@gmail.com') || '%';  -- 'moc.liamg@%'

中文模糊查詢特殊問題

MySQL 預設字元集下，中文模糊查詢可能匹配到非預期字元（編碼重疊問題）。

解決方案：BINARY + UPPER/LOWER

-- 精確匹配（區分大小寫、避免編碼干擾）
WHERE BINARY UPPER(name) LIKE UPPER('%關鍵字%')

-- PHP 完整範例
$keyword = mysqli_real_escape_string($conn, $_GET['q']);
$sql = "SELECT * FROM members 
        WHERE BINARY UPPER(username) LIKE UPPER(CONCAT('%', ?, '%'))";
$stmt = $conn->prepare($sql);
$stmt->bind_param("s", $keyword);
$stmt->execute();

REGEXP 正則表達式：更強大的模式匹配

-- 開頭為數字
WHERE col REGEXP '^[0-9]'

-- 包含連續 3 個數字
WHERE col REGEXP '[0-9]{3}'

-- Email 簡易驗證
WHERE email REGEXP '^[^@]+@[^@]+\.[^@]+$'

-- 中文姓名（2-4 個中文字）
WHERE name REGEXP '^[\u4e00-\u9fa5]{2,4}$'

最佳實務總結

1. 優先使用前綴匹配（`LIKE 'prefix%'`），可利用索引
2. 避免前導 %，評估 FULLTEXT 或搜尋引擎替代
3. 中文查詢加 BINARY 避免編碼誤匹配
4. 參數化查詢 防 SQL 注入
5. 大量資料 必上 Elasticsearch/OpenSearch
6. EXPLAIN 分析 每個 LIKE 查詢執行計畫

掌握 LIKE 的特性與限制，配合索引策略與外部工具，才能在模糊查詢中遊刃有餘！