目錄
MySQL 正則表達式詳細說明
1. 基本操作符：REGEXP 和 RLIKE
2. 常用正則表達式模式
3. MySQL 正則表達式函數（MySQL 8.0+）
4. 示例查詢
5. 注意事項
6. 總結
MySQL 正則表達式詳細說明
MySQL 支持正則表達式（Regular Expression），用于在查詢中進行復雜的模式匹配。它主要通過 REGEXP 或 RLIKE 操作符實現（兩者功能相同），以及 MySQL 8.0 及以上版本提供的擴展函數。以下內容將逐步解釋其用法、模式和注意事項。
1. 基本操作符：REGEXP 和 RLIKE
REGEXP和RLIKE是等價的，用于在WHERE子句中匹配字符串模式。- 語法示例：
SELECT * FROM 表名 WHERE 列名 REGEXP '模式'; - 如果匹配成功，返回
1（真）；否則返回0（假）。 - 示例：查詢所有以 "a" 開頭的用戶名。
SELECT * FROM users WHERE username REGEXP '^a';
2. 常用正則表達式模式
MySQL 正則表達式基于 POSIX 擴展正則表達式（ERE）標準。以下是常見元字符和模式：
- 基本匹配：
.：匹配任意單個字符（除換行符外），例如'a.c'匹配 "abc" 或 "aac"。^：匹配字符串開頭，例如'^start'匹配以 "start" 開頭的字符串。$：匹配字符串結尾，例如'end$'匹配以 "end" 結尾的字符串。
- 字符類：
[abc]：匹配 "a"、"b" 或 "c" 中的任意一個字符。[^abc]：匹配除 "a"、"b"、"c" 外的任意字符。[a-z]：匹配任意小寫字母。
- 量詞（控制匹配次數）：
*：匹配前一個元素零次或多次，例如'ab*c'匹配 "ac"、"abc"、"abbc" 等。+：匹配前一個元素一次或多次，例如'ab+c'匹配 "abc"、"abbc"，但不匹配 "ac"。?：匹配前一個元素零次或一次，例如'ab?c'匹配 "ac" 或 "abc"。{n}：匹配前一個元素恰好 $n$ 次，例如'a{3}'匹配 "aaa"。{n,}：匹配前一個元素至少 $n$ 次，例如'a{2,}'匹配 "aa"、"aaa" 等。{n,m}：匹配前一個元素 $n$ 到 $m$ 次，例如'a{2,4}'匹配 "aa"、"aaa" 或 "aaaa"。
- 分組和或操作：
|：表示“或”，例如'apple|banana'匹配 "apple" 或 "banana"。()：用于分組，例如'(ab)+'匹配 "ab"、"abab" 等。
- 轉義字符：
- 使用
\\轉義特殊字符，例如'\\.'匹配字面點號 "."。
- 使用
3. MySQL 正則表達式函數（MySQL 8.0+）
從 MySQL 8.0 開始，引入了更強大的函數：
- REGEXP_LIKE()：檢查是否匹配模式，語法
REGEXP_LIKE(字符串, '模式')。SELECT REGEXP_LIKE('hello', '^h') AS result; -- 返回 1 - REGEXP_INSTR()：返回匹配子串的起始位置，語法
REGEXP_INSTR(字符串, '模式')。SELECT REGEXP_INSTR('abc123', '[0-9]+') AS position; -- 返回 4（數字起始位置） - REGEXP_SUBSTR()：提取匹配的子串，語法
REGEXP_SUBSTR(字符串, '模式')。SELECT REGEXP_SUBSTR('email: test@example.com', '[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}') AS email; -- 返回 "test@example.com" - REGEXP_REPLACE()：替換匹配的子串，語法
REGEXP_REPLACE(字符串, '模式', '替換文本')。SELECT REGEXP_REPLACE('Phone: 123-456', '[0-9]{3}-[0-9]{3}', '***-***') AS masked; -- 返回 "Phone: ***-***"
4. 示例查詢
- 匹配數字序列：查詢包含至少 3 位數字的字符串。
SELECT * FROM data WHERE content REGEXP '[0-9]{3,}'; - 驗證郵箱格式：檢查是否符合標準郵箱模式。
SELECT * FROM users WHERE email REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$'; - 提取和替換：在 MySQL 8.0 中，提取日期部分。
SELECT REGEXP_SUBSTR('2023-10-05 event', '[0-9]{4}-[0-9]{2}-[0-9]{2}') AS date; -- 返回 "2023-10-05"
5. 注意事項
- 區分大小寫：MySQL 默認不區分大小寫。如需區分，使用
BINARY關鍵字或REGEXP_LIKE的選項，例如REGEXP_LIKE(str, 'pattern', 'c')（'c' 表示大小寫敏感）。 - 性能影響：正則表達式比簡單
LIKE查詢更慢，避免在大表上頻繁使用。優先考慮索引或全文搜索。 - 版本兼容性：
REGEXP/RLIKE支持所有 MySQL 版本。- 高級函數（如
REGEXP_REPLACE）僅限 MySQL 8.0+。 - 舊版本（如 MySQL 5.7）可能不支持某些模式（如 Unicode 字符類）。
- 轉義規則：在 SQL 字符串中，需雙重轉義特殊字符。例如，匹配點號使用
'\\.'，而非單反斜杠。 - 錯誤處理：無效模式會導致查詢錯誤。測試模式前，可使用簡單值驗證。
6. 總結
MySQL 正則表達式提供靈活的文本匹配能力，適用于數據驗證、清洗和提取。核心操作符 REGEXP 適用于基礎匹配，而 MySQL 8.0+ 的函數擴展了功能。使用時，注意模式語法、性能優化和版本限制。推薦先在小型數據集上測試模式，再應用到生產環境。
)
)