MySQL REGEXP 基礎

MySQL 採用 Henry Spencer 實現的 POSIX 1003.2 正則引擎，通過 REGEXP（或同義詞 RLIKE）操作符進行模式匹配，返回 1（匹配）或 0（不匹配）。

核心元字符速查表

元字符	含義	示例
^	字符串開始	'abc' REGEXP '^a' → 1
$	字符串結束	'abc' REGEXP 'c$' → 1
.	任意單字符（含換行）	'abc' REGEXP 'a.c' → 1
[...]	字符集合	'a1' REGEXP '[a-z0-9]' → 1
[^...]	排除字符集合	'a1' REGEXP '[^0-9]' → 1
p1|p2|p3	交替分支（或）	'cat' REGEXP 'cat|dog' → 1
*	前一元素 0 次或多次	'ac' REGEXP 'ab*c' → 1
+	前一元素 1 次或多次	'abc' REGEXP 'ab+c' → 1
?	前一元素 0 次或 1 次	'ac' REGEXP 'ab?c' → 1
{n}	前一元素恰好 n 次	'aaa' REGEXP 'a{3}' → 1
{m,n}	前一元素 m~n 次	'aa' REGEXP 'a{1,3}' → 1
()	分組/捕獲	'abab' REGEXP '(ab){2}' → 1

常用字符類別簡寫

• [[:digit:]] = [0-9]
• [[:alpha:]] = [a-zA-Z]
• [[:alnum:]] = [a-zA-Z0-9]
• [[:space:]] = 空白字符

實戰案例

1. 郵箱格式校驗

SELECT * FROM users 
WHERE email NOT REGEXP '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,4}$';

注意：MySQL 正則中 . 需轉義為 \\.；{2,4} 限制頂級域長度。

2. 手機號驗證（中國大陸）

SELECT * FROM users 
WHERE mobile REGEXP '^1[3-9]\\d{9}$';

3. 提取純數字 / 純字母

-- 僅包含數字
SELECT * FROM t WHERE col REGEXP '^[0-9]+$';

-- 僅包含字母
SELECT * FROM t WHERE col REGEXP '^[A-Za-z]+$';

4. 匹配特定格式：身份證號（18 位）

SELECT * FROM users 
WHERE id_card REGEXP '^[1-9]\\d{5}(19|20)\\d{2}(0[1-9]|1[0-2])(0[1-9]|[12]\\d|3[01])\\d{3}[0-9Xx]$';

5. 查找包含連續重複字符的記錄

SELECT * FROM logs WHERE message REGEXP '(.)\\1{2,}'; -- 連續 3 次以上相同字符

REGEXP vs LIKE 性能對比

維度	LIKE	REGEXP
索引利用	前綴匹配可用索引（col LIKE 'abc%'）	**完全不走索引**，全表掃描
表達能力	簡單通配符 % _	完整正則能力
執行速度	極快（C 級優化）	較慢（NFA 引擎回溯）
適用場景	前綴/後綴/包含查詢	複雜格式校驗、數據清洗

優化建議

1. 能用 LIKE 解決絕不用 REGEXP，如前綴查詢 name LIKE '張%'。
2. 將正則判斷下推到應用層：應用層正則過濾後，再用 IN 列表查詢主鍵。
3. MySQL 8.0+ 可用 REGEXP_LIKE()、REGEXP_SUBSTR()、REGEXP_REPLACE()，功能更強、語法更標準（ICU 引擎）。
4. 避免災難性回溯：慎用嵌套量詞 (a+)+、重疊分支 (a|aa)*。
5. 大數據量必須走索引：建立冗餘欄位（如 email_domain VARCHAR(50)）並建索引，代替 email REGEXP '@gmail\\.com$'。

版本差異提示

• MySQL 5.7：POSIX 正則，不支持前瞻/後顧、非貪婪模式。
• MySQL 8.0：遷移至 ICU 庫，支持 \\d、\\w、? 非貪婪、(?=...) 前瞻等 PCRE 特性，且提供 REGEXP_REPLACE。

總結

REGEXP 是 MySQL 處理複雜文本模式的利器，但**性能代價高、無法利用索引**。實戰中：

1. 優先用 LIKE、=、IN 等可走索引的操作符。
2. 校驗類查詢（郵箱、手機號）建議**寫入時在應用層校驗**，入庫後僅做簡單檢查。
3. MySQL 8.0 環境優先使用新增的 REGEXP_* 函數族，功能更強、語法更現代。