MySQL REPLACE() 批量替換實戰：單欄位多詞同步替換、WHERE條件配合、效能碾壓應用層

業務常需批量清洗欄位文本：去除非法字符、統一格式、脫敏。應用層SELECT → str_replace → UPDATE極其低效。MySQL內建REPLACE()單條SQL原地完成，支援多詞同步、條件過濾，效能碾壓應用層迴圈。

核心語法

-- 單詞替換
UPDATE `table` SET `field` = REPLACE(`field`, '舊字符', '新字符');

-- 多詞同步替換（鏈式調用）
UPDATE `table` SET 
  `field` = REPLACE(`field`, '舊1', '新1'),
  `field` = REPLACE(`field`, '舊2', '新2'),
  `field` = REPLACE(`field`, '舊3', '新3')
WHERE `condition` = 1;

實戰範例

場景一：文章內容域名遷移

UPDATE articles 
SET content = REPLACE(content, 'https://old-domain.com', 'https://new-domain.com')
WHERE content LIKE '%old-domain.com%';

場景二：手機號脫敏（中間4位→****）

UPDATE users 
SET phone = CONCAT(SUBSTRING(phone, 1, 3), '****', SUBSTRING(phone, 8, 4))
WHERE phone REGEXP '^1[3-9][0-9]{9}$';

注意：此處用CONCAT+SUBSTRING而非REPLACE，因需定位替換。

場景三：多詞同步清洗

UPDATE products
SET description = REPLACE(description, '舊品牌', '新品牌'),
    description = REPLACE(description, '舊型號', '新型號'),
    description = REPLACE(description, '停產', '經典款')
WHERE category_id = 10;

效能對比

避坑指南

• 大文本欄位：TEXT/BLOB用REPLACE會隱式轉換，極大值可能超max_allowed_packet，分批處理
• 字符集：確保連接字符集與表字符集一致，避免多字節字符截斷
• 觸發器：會觸發BEFORE/AFTER UPDATE觸發器，注意副作用
• Binlog：行級複製會記錄完整前後值，大批次導致Binlog暴增，考慮SET sql_log_bin=0（需超權）
• 不可逆：執行前必備份或SELECT REPLACE(...)預覽結果

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

-- REGEXP_REPLACE 正則替換（8.0.4+）
UPDATE articles SET content = REGEXP_REPLACE(content, '', '')
WHERE content LIKE '%