MySQL REPLACE 函數實戰：字串替換、批量更新與陷阱避坑

語法

REPLACE(str, from_str, to_str)
-- 返回將 str 中所有 from_str 替換為 to_str 後的新字串

基本用法

SELECT REPLACE('www.mysql.com', 'w', 'Ww');
-- → 'WwWwWw.mysql.com'

UPDATE tb1 SET f1 = REPLACE(f1, 'abc', 'def');

實戰批量清洗範例

-- 移除 HTML 標籤
UPDATE `dede_addonarticle` SET body = REPLACE(body, '', '');
UPDATE `dede_addonarticle` SET body = REPLACE(body, '', '');
UPDATE `dede_addonarticle` SET body = REPLACE(body, '', '');

-- 替換標題前綴
UPDATE `dede_archives` SET title = REPLACE(title, '大洋新聞 - ', '');

-- 修正圖片路徑
UPDATE `dede_addonarticle` SET body = REPLACE(body, '../../../../../../', 'https://special.dayoo.com/meal/');

REPLACE vs REPLACE INTO

效能與陷阱

• 全表掃描：UPDATE ... SET col = REPLACE(col, ...) 無法使用索引，大表會鎖表久。建議：
  • 加 WHERE col LIKE '%from_str%' 縮小範圍（仍全掃但可配合索引下推）。
  • 分批處理：WHERE id BETWEEN ... 每批 1 萬行，間隔 SLEEP(1)。
  • 離線維護窗口執行。
• 字符集一致：from_str/to_str 與欄位字符集不一致會導致亂碼或報錯。
• 多位元組安全：REPLACE 支援 UTF-8 等多位元組字符集。
• 遞迴替換風險：REPLACE('aaa', 'a', 'aa') → 'aaaaaa'（每個 'a' 獨立替換），非遞迴，但結果長度指數增長，慎用。

進階：正則替換（MySQL 8.0+）

SELECT REGEXP_REPLACE('abc123def', '[0-9]+', 'NUM');
-- → 'abcNUMdef'

舊版本需自寫 UDF 或應用層處理。