MySQL 使用 REPLACE 與 REGEXP 實現資料庫欄位內容替換技巧

MySQL 本身不支援完整的正則表達式替換（REGEXP_REPLACE 於 8.0 才引入），但結合 REPLACE() 與 REGEXP 判斷，即可針對符合特定模式的資料進行精準替換。本文整理實用語法範例。

基本替換：針對符合模式的列替換特定字串

-- 將 comment 表中 author_url 含有 www.sohu.com 的列，把 sohu 替換為 sina
UPDATE `comment`
SET `author_url` = REPLACE(`author_url`, 'sohu', 'sina')
WHERE `author_url` REGEXP 'www\\.sohu\\.com';

• REGEXP 用於 WHERE 篩選符合條件的列。
• REPLACE(str, from_str, to_str) 對整個欄位值做字串替換（非正則，為全字串匹配）。
• 此技巧適用於「先用正則找出目標列，再做固定字串替換」的場景。

進階：搭配 IF 條件進行不同替換邏輯

UPDATE `comment`
SET `url` = IF(
    `url` REGEXP 'test\\.yahoo\\.com\\.cn',
    REPLACE(`url`, 'www1.sohu.com', 'www.sina.com'),
    REPLACE(`url`, 'www2.yahoo.com', 'www.sina.com')
)
WHERE 1=1;

• 根據同一欄位是否符合不同正則，執行不同的 REPLACE。
• WHERE 1=1 表示全表掃描（生產環境建議加上主鍵範圍或時間範圍限制，避免鎖表過久）。

MySQL 8.0+ 原生支援 REGEXP_REPLACE

若使用 MySQL 8.0 或更新版本，可直接使用標準函數：

UPDATE `comment`
SET `author_url` = REGEXP_REPLACE(`author_url`, 'sohu', 'sina')
WHERE `author_url` REGEXP 'www\\.sohu\\.com';

REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]]) 完整支援正則替換，功能更強大、語意更清晰。

效能與注意事項

• 全表掃描： REGEXP 無法使用索引，大表操作會造成鎖定與效能下降。建議：
• 在低峰期執行。
• 先用 SELECT 確認影響筆數：SELECT COUNT(*) FROM comment WHERE author_url REGEXP 'www\\.sohu\\.com';
• 分批次更新（加 LIMIT 配合主鍵範圍）。
• 備份先行：執行 UPDATE 前務必備份資料或建立備份表：CREATE TABLE comment_backup AS SELECT * FROM comment;
• 字元集與逸出：正則模式中的特殊字元（如 . * ?）需以雙反斜線 \\ 逸出（在 SQL 字串中）。

替代方案：應用層處理

對於複雜的正則替換需求（如擷取群組、條件替換），建議在應用程式層（PHP、Python、Node.js）讀取資料、處理後再批次寫回，彈性更高且不阻塞資料庫。

善用 REPLACE + REGEXP 組合，可在不升級 MySQL 版本的情況下解決大部分「依模式篩選後替換」的需求；若專案已升級至 8.0，請直接使用 REGEXP_REPLACE 獲得更完整的正則替換能力。