萬盛學電腦網

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

MySQL 正則表達式 REGEXP 完整指南:語法、範例與效能考量

基本語法

SELECT column FROM table WHERE column REGEXP 'pattern';

MySQL 使用 Henry Spencer 的正則引擎(POSIX 1003.2 子集),支援常見元字元。

核心元字元速查表

元字元說明範例
^行首錨定'^abc' 匹配以 abc 開頭
$行尾錨定'abc$' 匹配以 abc 結尾
.任意單字元(含換行)'a.c' 匹配 aac, abc, a-c...
*前項 0 次或多次'ab*c' 匹配 ac, abc, abbc...
+前項 1 次或多次'ab+c' 匹配 abc, abbc...(不匹配 ac)
?前項 0 次或 1 次'ab?c' 匹配 ac, abc
|或邏輯'cat|dog' 匹配 cat 或 dog
()分組'(ab)+' 匹配 ab, abab...
{n}精確 n 次'a{3}' = 'aaa'
{n,}至少 n 次'a{2,}' = 'aa+'
{n,m}n 到 m 次'a{2,4}' = aa, aaa, aaaa
[abc]字元類別(任一)'[aeiou]' 匹配任一母音
[^abc]排除字元類別'[^0-9]' 非數字
[a-z]範圍'[0-9a-f]' 十六進位字元
[:class:]POSIX 字元類別'[[:alnum:]]+' 英數字
[::]單字邊界'[[:<:]]word[[:>:]]' 整詞匹配

實戰範例

多值匹配

SELECT prod_name FROM products
WHERE prod_name REGEXP '1000|2000'
ORDER BY prod_name;

模式分組與可選

SELECT prod_name FROM products
WHERE prod_name REGEXP '\\\\([0-9]+ sticks?\\\\)'
ORDER BY prod_name;
-- 匹配 (1 stick), (3 sticks), (10 stick)...

字元類別應用

-- 純英數字
SELECT 'justalnums' REGEXP '[[:alnum:]]+';  -- 1
SELECT '!!' REGEXP '[[:alnum:]]+';          -- 0

-- 整詞匹配
SELECT 'a word a' REGEXP '[[:<:]]word[[:>:]]';  -- 1
SELECT 'a sword a' REGEXP '[[:<:]]word[[:>:]]'; -- 0

效能與最佳實踐

⚠️ 效能陷阱

  • REGEXP 無法使用索引(MySQL 8.0 前),全表掃描
  • 複雜模式回溯導致 CPU 飆升
  • 大表直接 REGEXP 會嚴重拖慢查詢

優化策略

  1. 前置過濾: 先用 LIKE 或 = 縮小範圍,再套用 REGEXP
  2. WHERE category = 'electronics' AND name REGEXP '^PRO-[0-9]{4}$'
  3. 生成欄位/虛擬欄位(MySQL 5.7+): 預先計算正則結果
  4. ALTER TABLE products ADD COLUMN is_pro_code BOOLEAN
        GENERATED ALWAYS AS (name REGEXP '^PRO-[0-9]{4}$') STORED;
    CREATE INDEX idx_is_pro_code ON products(is_pro_code);
  5. MySQL 8.0 REGEXP_INSTR/REGEXP_SUBSTR: 可搭配函數索引
  6. 應用層預處理: 匯入時用程式語言正則解析,存結構化欄位

版本差異提醒

  • MySQL 8.0 升級為 ICU 正則引擎,語法更豐富(支援 \d, \w, ?= 等),但部分舊語法行為變更
  • REGEXP_LIKE() 為 8.0 新增標準函數,語意更清晰
copyright © 萬盛學電腦網 all rights reserved