MySQL REGEXP 正則表達式實戰：從基礎匹配到複雜模式提取

MySQL REGEXP 正則表達式實戰：從基礎匹配到複雜模式提取

MySQL REGEXP (8.0+ 支持 REGEXP_REPLACE/REGEXP_SUBSTR) 提供比 LIKE 強大得多的模式匹配能力。本文通過分級示例掌握核心語法。

一、基礎字符匹配

-- 包含子串 '1000'
SELECT prod_name FROM products WHERE prod_name REGEXP '1000';

-- 單字符通配符 '.' (等同 LIKE '_')
SELECT prod_name FROM products WHERE prod_name REGEXP '.000';
-- 匹配 1000, 2000, a000 等

二、分支/或匹配 |

-- 包含 1000 或 2000
SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000';
-- 多重分支: '1000|2000|3000'

三、字符集合 [] 與範圍 -

-- 匹配 '1 Ton' '2 Ton' '3 Ton'
SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton';

-- 匹配任意數字開頭的 ' Ton'
SELECT prod_name FROM products WHERE prod_name REGEXP '[1-9] Ton';
-- 字母範圍: '[a-z]' '[A-Z]' '[a-zA-Z0-9]'

四、特殊字符轉義 \\

-- 匹配字面點號 '.'
SELECT prod_name FROM products WHERE prod_name REGEXP '\\.';
-- MySQL 字符串字面量需雙重轉義: '\\\\.' 或使用十六進制 0x2E

五、量詞：重複次數控制

-- 匹配 '(4 sticks)' 或 '(4 stick)'  (s 可選)
SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9] sticks?\\)';

六、定位符：邊界約束

-- 僅匹配以數字或點號開頭的行
SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]';

七、字符類別簡寫 (POSIX 標準)

[[:alnum:]]  -- 字母數字
[[:alpha:]]  -- 字母
[[:digit:]]  -- 數字 [0-9]
[[:space:]]  -- 空白字符
[[:lower:]]  -- 小寫字母
[[:upper:]]  -- 大寫字母
[[:punct:]]  -- 標點符號

八、實戰案例

1. 手機號驗證：REGEXP '^1[3-9][0-9]{9}$'
2. Email 簡易校驗：REGEXP '^[^@]+@[^@]+\\.[^@]+$'
3. 提取純數字 (8.0+)：REGEXP_SUBSTR(col, '[0-9]+')
4. 清洗特殊字符 (8.0+)：REGEXP_REPLACE(col, '[^a-zA-Z0-9\\u4e00-\\u9fa5]', '')
5. 分割字符串 (8.0+)：REGEXP_SUBSTR(col, '[^,]+', 1, n) 取第 n 段。

九、性能與注意事項

• REGEXP 無法使用普通 B+Tree 索引，全表掃描。大表需謹慎，考慮全文索引或應用層預過濾。
• MySQL 使用 Henry Spencer 正則庫，語法接近 POSIX ERE，不支持 PCRE 進階特性 (如前瞻 (?=)、非捕獲組 (?:)、回溯引用 \\1)。
• 字符集影響：utf8mb4 下 . 匹配單字符 (可能多字節)；二進制字符集 _bin 逐字節匹配。
• 8.0+ 新函數 REGEXP_LIKE() (等同 REGEXP)、REGEXP_INSTR()、REGEXP_SUBSTR()、REGEXP_REPLACE() 遷移自 Oracle，功能更完整。

正則表達式是數據清洗、異常檢測、模式提取的利器，但「能用正則別用正則」——優先考慮 LIKE、SUBSTRING_INDEX、JSON_EXTRACT 等確定性函數，保留 REGEXP 解決真正複雜的非結構化模式。