萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL REGEXP 正則表達式實戰手冊:語法規則、進階模式、效能陷阱與替代方案

MySQL REGEXP (別名 RLIKE) 提供正則表達式匹配能力,功能強大但效能敏感。本文完整收錄元字符規則、實戰模式、NOT REGEXP 否定匹配、二進位模式、與 LIKE 效能對比、全文索引替代方案,助你在「必須用正則」時用得精準、用得放心。

三大正則謂詞

  • REGEXP / RLIKE:匹配返回 1,不匹配 0
  • NOT REGEXP / NOT RLIKE:不匹配返回 1

核心元字符速查表

實戰模式庫

元字符說明範例匹配
^字串開頭'^abc' 匹配 'abc...'
$字串結尾'abc$' 匹配 '...abc'
.任意單字元 (含換行)'a.c' 匹配 'abc', 'axc'
[...]字符類'[abc]' 匹配 a/b/c;'[a-z]' 匹配小寫字母
[^...]否定字符類'[^0-9]' 非數字
*前一元素 0~n 次'ab*c' 匹配 'ac', 'abc', 'abbc'
+前一元素 1~n 次'ab+c' 匹配 'abc', 'abbc'
?前一元素 0~1 次'ab?c' 匹配 'ac', 'abc'
{n}精確 n 次'a{3}' = 'aaa'
{m,n}m~n 次'a{2,4}' = 'aa'~'aaaa'
|或'cat|dog' 匹配 'cat' 或 'dog'
(...)分組/捕獲'(ab)*' 匹配 '', 'ab', 'abab'
[:class:]POSIX 字符類'[[:digit:]]' = '[0-9]'
[[:<:]] / [[:>:]]單詞邊界 (開頭/結尾)'[[:<:]]word[[:>:]]' 整詞匹配

進階範例:逗號分隔欄位的位置條件匹配

-- t1 欄位格式:'1,1,1,2,3,3,4,4,5,5,2,4,3,2,1,2'
-- 第 1、3、10 個逗號前的數字在 3-5 之間,其餘 1-5
SELECT * FROM tb 
WHERE t1 REGEXP '^[3-5],[1-5],[3-5],[1-5],[1-5],[1-5],[1-5],[1-5],[1-5],[3-5],[1-5],[1-5],[1-5],[1-5],[1-5],[1-5]$';

特殊字符轉義

SELECT '1+2' REGEXP '1+2';    -- 0 (+ 是元字符)
SELECT '1+2' REGEXP '1\\+2';   -- 1 (雙反斜線轉義)
SELECT '1+2' REGEXP '1\\+2';   -- 1 (同上)

效能陷阱與優化

需求REGEXP 模式說明
以字母開頭'^[a-zA-Z]'
純數字'^[0-9]+$'
手機號 (中國大陸)'^1[3-9]\\d{9}$'雙反斜線轉義
Email 簡易版'^[^@]+@[^@]+\\.[^@]+$'
含中文'[\\x{4e00}-\\x{9fa5}]'Unicode 範圍 (需 MySQL 8.0+)
固定格式 CSV 欄位'^[^,]*,[^,]*,[^,]*$'三欄位
複雜業務碼'^[A-Z]{2}-\\d{4}-[A-Z0-9]{6}$'如 AB-2024-XYZ123

替代方案選型

  1. 前綴/後綴/精確匹配 → LIKE 'prefix%' / = (可用索引)
  2. 包含關鍵字 → FULLTEXT INDEX + MATCH() AGAINST() (自然語言/布林模式)
  3. 結構化資料 → JSON 欄位 + JSON_EXTRACT、或拆分關聯表
  4. 極簡模式 → INSTR()、LOCATE()、FIND_IN_SET()
  5. 必須正則 → REGEXP + 條件下推 + 限制掃描行數

MySQL 8.0 正則增強

  • 採用 ICU 庫,支援 Unicode 屬性 \\p{L}、查前瞻/後顧 (Lookahead/Lookbehind)
  • REGEXP_REPLACE()、REGEXP_SUBSTR()、REGEXP_INSTR() 內建函式
  • 效能顯著優於 5.7 Henry Spencer 庫實現
正則是「核武器」:威力大、副作用強、易誤傷友軍。能用 LIKE/FULLTEXT/JSON 解決的堅決不用正則;必須用時,加嚴格 WHERE 條件限制掃描範圍,並在測試環境壓測 CPU/延遲再上線。
copyright © 萬盛學電腦網 all rights reserved
場景問題對策
大表全表掃描REGEXP 無法使用索引加 WHERE 條件縮小範圍、建立生成列索引 (8.0+)、改用全文索引
複雜正則回溯災難性回溯導致 CPU 100%避免巢狀量詞、使用具體字符類、限制長度
頻繁調用函式呼叫開銷大應用層預過濾、緩存結果