MySQL REPLACE()：字串全量替換的利器與陷阱

REPLACE(str, from_str, to_str) 回傳將字串 str 中所有 from_str 替換為 to_str 的新字串。**區分大小寫、不支援正則、會全表掃描**。

基本語法

UPDATE table_name
SET column_name = REPLACE(column_name, 'from_str', 'to_str')
[WHERE condition];

實戰範例

-- 1. 簡單替換
UPDATE abc SET xxx = REPLACE(xxx, 'a', 'c');

-- 2. 清理 HTML 標籤 (DedeCMS 典型場景)
UPDATE `dede_addonarticle` SET body = REPLACE(body, '', '');
UPDATE `dede_addonarticle` SET body = REPLACE(body, '', '');
UPDATE `dede_addonarticle` SET body = REPLACE(body, '', '');

-- 3. 移除標題前綴
UPDATE `dede_archives` SET title = REPLACE(title, '大洋新聞 - ', '');

效能與安全警示

風險	說明	對策
全表掃描	無 WHERE 條件會鎖定全表，大表導致長時間阻塞	加 WHERE 過濾、分批 LIMIT、低峰期執行
誤替換	子字串匹配可能誤傷 (如 'a' → 'b' 影響 'abc')	WHERE column LIKE '%from_str%' 精確鎖定
觸發器/外鍵副作用	UPDATE 觸發 AFTER UPDATE 觸發器、檢查外鍵	評估影響範圍、必要時暫停觸發器
Binlog 膨脹	每行變更記錄 Binlog，大量替換導致 Binlog 暴增	分批、或暫時停用 Binlog (sql_log_bin=0)

分批執行模板 (大表安全替換)

DELIMITER //
CREATE PROCEDURE batch_replace()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE batch_size INT DEFAULT 1000;
  REPEAT
    UPDATE large_table
    SET col = REPLACE(col, 'old', 'new')
    WHERE col LIKE '%old%'
    LIMIT batch_size;
    SET done = ROW_COUNT();
  UNTIL done = 0 END REPEAT;
END//
DELIMITER ;

CALL batch_replace();

進階：正則替換 (MySQL 8.0+)

-- REGEXP_REPLACE (需啟用 regexp 支援)
UPDATE t SET col = REGEXP_REPLACE(col, '[0-9]+', 'NUM');

最佳實務： 小表直接執行；大表**必須分批 + WHERE 過濾 + 低峰期**；替換前**備份資料**，替換後 **ANALYZE TABLE** 更新統計資訊；生產環境先在測試庫驗證結果正確性。