您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

SQL LIKE 謓詞全解析：通配符、* % ? # [] [!] 組合模式、字符串模糊匹配實戰與性能優化

編輯：MySQL 資料庫相關知識

標準 SQL LIKE 語法


SELECT * FROM table_name WHERE column_name LIKE pattern;

pattern 中兩個標準通配符：

%：匹配**任意長度 (含 0)** 字符串 (ANSI SQL 標準)。
_ (下劃線)：匹配**單個**字符 (ANSI SQL 標準)。

擴展通配符 (Access / T-SQL / 某些 ODBC 驅動支援，MySQL 不支援原生)

符號	含義	示例匹配	MySQL 等效
`*`	任意多字符	`abc`	`%abc%`
`?`	單個字符	`a?c`	`a_c`
`#`	單個數字 (0-9)	`a#c`	`a[0-9]c` (REGEXP)
`[abc]`	字符集合中任一	`[aeiou]`	`REGEXP '[aeiou]'`
`[a-z]`	字符範圍	`[a-f]`	`REGEXP '[a-f]'`
`[!abc]` / `[^abc]`	排除字符集合	`[^0-9]`	`NOT REGEXP '[0-9]'`

關鍵提示：MySQL **原生 LIKE 僅支援 % 和 _**。上述擴展符號在 MySQL LIKE 中視為**字面字符**，不具通配含義。需用 REGEXP / RLIKE 實現正則匹配。

MySQL 實戰模式大全

需求	LIKE 模式	說明
以「張」開頭	`張%`	索引可用 (前綴匹配)
以「三」結尾	`%三`	全表掃描 (後綴匹配)
包含「中文」	`%中文%`	全表掃描 (中間匹配)
第二個字符是「李」	`_李%`	全表掃描
長度為 3 的字符串	`___`	三個下劃線
包含數字	`REGEXP '[0-9]'`	需正則
純數字	`REGEXP '^[0-9]+$'`	需正則
匹配字面 `%` 或 `_`	`\%`, `\_`	轉義符 `\` (或 `ESCAPE '\'`)

性能優化鐵律

前綴匹配 (col LIKE 'abc%') 可用索引，範圍掃描極快。
後綴/中間匹配 (%abc / %abc%) 無法用索引，全表掃描。
- 方案 A：全文索引 FULLTEXT (自然語言/布爾模式)。
- 方案 B：反轉列存儲 + 前綴匹配 (如存 REVERSE(name)，查 REVERSE(name) LIKE 'cba%')。
- 方案 C：Elasticsearch / OpenSearch 專業搜索引擎。
避免在 WHERE 中對列使用函數：WHERE UPPER(name) LIKE 'ABC%' → 索引失效。改存標準化大寫列或功能性索引 (8.0+)。
字符集一致**：比較雙方字符集/校對規則不一致導致隱式轉換、索引失效、結果錯誤。
EXPLAIN 驗證**：執行計劃 type=range / ref 表示用上索引；ALL 為全表掃描。

實戰案例：用戶名模糊搜索優化


-- ❌ 慢：中間匹配，全表掃描
SELECT * FROM users WHERE username LIKE '%john%';

-- ✅ 快：前綴匹配，走索引 (需業務接受「以...開頭」語義)
SELECT * FROM users WHERE username LIKE 'john%';

-- ✅ 專業：全文索引 (MyISAM/InnoDB 5.6+)
ALTER TABLE users ADD FULLTEXT(username);
SELECT * FROM users WHERE MATCH(username) AGAINST('+john' IN BOOLEAN MODE);

-- ✅ 架構級：同步至 Elasticsearch，提供高亮、分詞、模糊、拼音、同義詞等豐富能力

轉義符自定義


-- 默認轉義符 \ 
SELECT * FROM t WHERE col LIKE '%\%%' ESCAPE '\';  -- 匹配字面 %

-- 自定義轉義符 |
SELECT * FROM t WHERE col LIKE '%|%' ESCAPE '|';  -- 匹配字面 % (此處 | 為轉義符，模式中無轉義字符)
-- 正確自定義示例：
SELECT * FROM t WHERE col LIKE '%|_%' ESCAPE '|';  -- 匹配字面 _ (|_ 表示轉義 _)