許多開發者只知道 REPLACE 做簡單字串替換,其實 MySQL 的 REPLACE 函數配合 REGEXP 正則表達式,能處理更複雜的資料清洗需求。本文系統整理兩者用法與實戰技巧。
REPLACE INTO table (id, name) VALUES ('1','aa'), ('2','bb');
作用:向表插入記錄。若主鍵 id 為 1 或 2 的記錄不存在,等同 INSERT INTO;若已存在,則刪除舊記錄再插入新記錄(注意:會觸發 DELETE + INSERT,而非 UPDATE)。
SELECT REPLACE('www.111cn.net', 'w', 'Ww'); -- 結果: WwWwWw.111cn.net
UPDATE table SET name = REPLACE(name, 'aa', 'bb');
將字串中所有 from_str 替換為 to_str。
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 | sdfsfbeijing sldjfsld |
| 2 | sdfsfshanghai sldjfsld |
| 3 | sdfsfjn sldjfsld |
| 4 | sdfsfqd sldjfsld |
+----+------------------------------------------+
需求:刪除 到 之間的內容。
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):回傳子字串首次出現位置,不存在回傳 0SUBSTRING(str, pos, len):從位置 pos 取長度 len 的子字串REPLACE(str, from_str, to_str):在 str 中將 from_str 替換為 to_str此法僅能處理單一出現的標籤對,多組標籤需分多次執行或改用程式語言處理。