您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 模糊查詢完全指南：LIKE 模式匹配與 REGEXP 正則表達式深度解析

編輯：MySQL 綜合教學

兩大模糊查詢體系

特性	SQL 標準模式 (LIKE)	擴展正則表達式 (REGEXP/RLIKE)
操作符	`LIKE` / `NOT LIKE`	`REGEXP` / `NOT REGEXP` (`RLIKE` 同義)
通配符	`%` (任意長度字符，含 0) `_` (單個字符)	標準 POSIX 正則元字符 (見下表)
大小寫敏感	默認不敏感 (依字符集校對規則，如 `utf8mb4_general_ci`)	默認敏感 (可用字符類 `[aA]` 或 `LOWER()` 實現不敏感)
匹配語義	整列匹配 (需顯式 `%` 包裹實現包含)	子串匹配 (模式出現在值任意位置即匹配)
性能特徵	可利用索引 (前綴匹配 `abc%`)	通常全表掃描，不可用索引 (MySQL 8.0+ 部分優化)

LIKE 模式匹配實戰速查


-- 以 "b" 開頭
WHERE name LIKE 'b%'

-- 以 "fy" 結尾
WHERE name LIKE '%fy'

-- 包含 "w"
WHERE name LIKE '%w%'

-- 精確 5 個字符
WHERE name LIKE '_____'   -- 5 個下劃線

-- 轉義特殊字符 (查詢字面值 "%" 或 "_")
WHERE name LIKE '%\%%' ESCAPE '\'
WHERE name LIKE '%\_%' ESCAPE '\'

※ 索引利用鐵律：**只有前綴固定模式 (`abc%`) 才可能走索引**；`%abc`、`%abc%`、`_abc` 均導致全表掃描。

REGEXP 正則表達式核心元字符表

元字符	含義	示例匹配
`.`	任意單個字符	`a.c` → `abc`, `axc`, `a-c`
`[...]`	字符類：括號內任一字符	`[abc]` → `a` `b` `c`
`[^...]`	否定字符類：除括號外任一字符	`[^abc]` → 非 `a/b/c` 的字符
`-` (在 [] 內)	範圍	`[a-z]` 小寫字母, `[0-9]` 數字
`*`	前導元素 0 次或多次	`ab*c` → `ac`, `abc`, `abbc`...
`+`	前導元素 1 次或多次	`ab+c` → `abc`, `abbc`...
`?`	前導元素 0 次或 1 次	`ab?c` → `ac`, `abc`
`{n}` / `{n,}` / `{n,m}`	精確 n 次 / 至少 n 次 / n 到 m 次	`a{3}` → `aaa`
`^`	字串開始錨點	`^abc` → 以 `abc` 開頭
`$`	字串結束錨點	`abc$` → 以 `abc` 結尾
`\|`	或 (分支)	`cat\|dog` → `cat` 或 `dog`
`()`	分組/捕獲	`(ab)+` → `ab`, `abab`...

REGEXP 實戰對照：重寫 LIKE 經典場景


-- 1. 以 "b" 或 "B" 開頭 (不敏感)
WHERE name REGEXP '^[bB]'
-- 或
WHERE name REGEXP '^b'  -- 若列校對規則 _ci 則自動不敏感

-- 2. 以 "fy" 結尾
WHERE name REGEXP 'fy$'

-- 3. 包含 "w" 或 "W"
WHERE name REGEXP '[wW]'

-- 4. 精確 5 個字符 (^ 錨點開始，$ 錨點結尾，5 個任意字符 .)
WHERE name REGEXP '^.....$'
-- 簡寫量詞
WHERE name REGEXP '^.{5}$'

-- 5. 包含數字 (任意位置)
WHERE name REGEXP '[0-9]'

-- 6. 純字母 (全字符皆為字母)
WHERE name REGEXP '^[a-zA-Z]+$'

-- 7. 郵箱簡易校驗
WHERE email REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'

性能與優化鐵律

優先用 `LIKE` 前綴匹配 (`col LIKE 'prefix%'`) — 可走索引，極快。
避免 `LIKE '%suffix'` / `LIKE '%mid%'` — 全表掃描，大表禁用。
`REGEXP` 默認全表掃描 — 僅在小表、低頻、或無索引可用時使用。
大表模糊搜尋正解： 全文索引 (`FULLTEXT` + `MATCH...AGAINST`)、Elasticsearch、Solr、Bleve 等專用搜索引擎。
MySQL 8.0 新特性： `REGEXP_LIKE()`、`REGEXP_SUBSTR()`、`REGEXP_REPLACE()`、`REGEXP_INSTR()` 提供更豐富正則處理能力，但索引利用仍受限。

常見坑與避雷指南

❌ `WHERE col = '%abc%'` — **等號不支持通配符**，永遠匹配字面值 `%abc%`。
❌ `WHERE col REGEXP '.abc.'` 以為是「包含 abc」 — `.` 匹配**任意單字符**，應用 `WHERE col REGEXP 'abc'` 即可（子串匹配語義）。
❌ 正則中直接使用保留字符 `. * + ? ^ $ [ ] ( ) { } | \` — **必須轉義** (如 `\.` `\*` `\\`)。
❌ 忽略大小寫敏感差異 — `LIKE` 通常不敏感，`REGEXP` 敏感，明確指定 `[aA]` 或 `LOWER(col) REGEXP ...`。
❌ 在高頻大表 `WHERE` 中濫用 `REGEXP` — 直接拖垮數據庫 CPU/IO。