萬盛學電腦網

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

MySQL REPLACE 與 REGEXP 正則表達式替換完整指南

許多開發者只知道 REPLACE 做簡單字串替換,其實 MySQL 的 REPLACE 函數配合 REGEXP 正則表達式,能處理更複雜的資料清洗需求。本文系統整理兩者用法與實戰技巧。

REPLACE 函數兩種用法

1. REPLACE INTO(資料寫入)

REPLACE INTO table (id, name) VALUES ('1','aa'), ('2','bb');

作用:向表插入記錄。若主鍵 id 為 1 或 2 的記錄不存在,等同 INSERT INTO;若已存在,則刪除舊記錄再插入新記錄(注意:會觸發 DELETE + INSERT,而非 UPDATE)。

2. REPLACE(str, from_str, to_str)(字串替換)

SELECT REPLACE('www.111cn.net', 'w', 'Ww');  -- 結果: WwWwWw.111cn.net
UPDATE table SET name = REPLACE(name, 'aa', 'bb');

將字串中所有 from_str 替換為 to_str。

REGEXP 正則表達式模式匹配

MySQL 支援擴展正則表達式,使用 REGEXP / NOT REGEXP(同義詞 RLIKE / NOT RLIKE)。

常用元字元

元字元說明範例
.匹配任意單一字元'abc' REGEXP 'a.c' → 1
[...]字元類別,匹配括號內任一字元'a' REGEXP '[abc]' → 1
*匹配前面字元 0 次或多次'abbb' REGEXP 'ab*' → 1
^匹配字串開頭'foo' REGEXP '^f' → 1
$匹配字串結尾'foo' REGEXP 'o$' → 1
{n}精確匹配 n 次'abc' REGEXP '^.{3}$' → 1
|或邏輯'pi' REGEXP 'pi|apa' → 1

實戰範例

-- 以 b 開頭的名字
SELECT * FROM pet WHERE name REGEXP '^b';

-- 以 fy 結尾的名字
SELECT * FROM pet WHERE name REGEXP 'fy$';

-- 包含 w 的名字
SELECT * FROM pet WHERE name REGEXP 'w';

-- 長度恰好 5 字元的名字
SELECT * FROM pet WHERE name REGEXP '^.....$';
-- 或
SELECT * FROM pet WHERE name REGEXP '^.{5}$';

字元類別簡寫

[[:alnum:]]  字母數字   [[:alpha:]]  字母
[[:digit:]]  數字       [[:space:]]  空白字元
[[:lower:]]  小寫       [[:upper:]]  大寫
[[:punct:]]  標點       [[:xdigit:]] 十六進位

單詞邊界

[[:<:]]  單詞開頭    [[:>:]]  單詞結尾
SELECT 'a word a' REGEXP '[[:<:]]word[[:>:]]';  -- 1
SELECT 'a xword a' REGEXP '[[:<:]]word[[:>:]]'; -- 0

實戰案例:刪除 標籤內容

資料表 mt2:

+----+------------------------------------------+
| id | name                                     |
+----+------------------------------------------+
| 1  | sdfsfbeijingsldjfsld  |
| 2  | sdfsfshanghaisldjfsld |
| 3  | sdfsfjnsldjfsld       |
| 4  | sdfsfqdsldjfsld       |
+----+------------------------------------------+

需求:刪除 到 之間的內容。

REPLACE 不支援正則,採用 LOCATE + SUBSTRING + REPLACE 組合:

UPDATE mt2 SET name = REPLACE(
  name,
  SUBSTRING(
    name,
    LOCATE('', name),
    LOCATE('', name) - LOCATE('', name) + 10
  ),
  ''
);

結果:

+----+-------------------+
| id | name              |
+----+-------------------+
| 1  | sdfsfactsldjfsld  |
| 2  | sdfsfactsldjfsld  |
| 3  | sdfsfactsldjfsld  |
| 4  | sdfsfactsldjfsld  |
+----+-------------------+

函數說明

  • LOCATE(substr, str):回傳子字串首次出現位置,不存在回傳 0
  • SUBSTRING(str, pos, len):從位置 pos 取長度 len 的子字串
  • REPLACE(str, from_str, to_str):在 str 中將 from_str 替換為 to_str

限制

此法僅能處理單一出現的標籤對,多組標籤需分多次執行或改用程式語言處理。

copyright © 萬盛學電腦網 all rights reserved