您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL REPLACE 函數實現字段部分內容批量替換指南

編輯：MySQL 資料庫相關知識

MySQL 內建 REPLACE(str, from_str, to_str) 函數可實現字符串的全量替換，配合 UPDATE 語句即可完成字段內特定文本的批量修改（如價格調整、域名遷移、敏感詞過濾）。本文詳細說明語法、實戰示例、效能考量與避坑指南。

核心語法

UPDATE `table_name` 
SET `column_name` = REPLACE(`column_name`, '舊文本', '新文本')
WHERE `column_name` LIKE '%舊文本%';  -- 建議加條件縮小掃描範圍

REPLACE 會替換所有出現的 from_str，區分大小寫（取決於字符集校對規則，utf8mb4_general_ci 不區分）。
WHERE 子句非必需，但強烈建議加上，避免全表掃描觸發鎖升級、Binlog 膨脹、主從延遲。

實戰示例

1. 文章標題批量更新價格

UPDATE dede_archives 
SET title = REPLACE(title, '155', '170')
WHERE title LIKE '%155%';

連續執行多條替換時，注意順序衝突：先替換長模式，再替換短模式，防止「155→170」後又被「170→230」二次替換。

2. 域名/圖片路徑遷移

UPDATE `posts` 
SET `content` = REPLACE(`content`, 'https://old-cdn.example.com', 'https://new-cdn.example.com')
WHERE `content` LIKE '%old-cdn.example.com%';

3. 清洗特殊字符/HTML 實體

UPDATE `comments` 
SET `text` = REPLACE(`text`, '<', '&lt;')
WHERE `text` LIKE '%<%';

進階：多重替換單條語句完成

嵌套 REPLACE 可一次執行多個替換，減少表鎖次數：

UPDATE `products`
SET `description` = REPLACE(
                        REPLACE(
                          REPLACE(`description`, '舊品牌', '新品牌'),
                          '舊型號', '新型號'),
                        '舊參數', '新參數')
WHERE `description` LIKE '%舊品牌%' OR `description` LIKE '%舊型號%';

效能與鎖控制策略（大表必讀）

策略	說明
分批處理	單次 `UPDATE` 限制 `LIMIT 1000`（需配合主鍵範圍或游標循環），避免長事務阻塞業務。
低峰期執行	業務閒時執行，減少鎖衝突。
索引覆蓋	若 `WHERE` 列有索引，優化器可走 `range` 掃描而非 `ALL`。
延遲複製	主從架構下，大批量 UPDATE 會導致主從延遲；可臨時停止從庫 SQL 線程，主庫分批執行完再恢復。
pt-online-schema-change 思路	極大表可建影子表、同步數據、原子切換，但替換場景通常直接分批 UPDATE 更簡單。

避坑指南

字符集不一致：表字符集 utf8、連接字符集 utf8mb4 可能導致替換失效或亂碼。統一使用 utf8mb4。
大小寫敏感：utf8mb4_bin 校對規則區分大小寫，REPLACE('ABC', 'a', 'X') 不會替換。可用 LOWER/UPPER 統一或指定 COLLATE utf8mb4_general_ci。
觸發器副作用：表上若有 BEFORE UPDATE 觸發器，替換邏輯可能被修改或記錄審計日誌，導致意外行為。
外鍵約束：替換主鍵/外鍵列值會觸發約束檢查甚至報錯，極少直接替換關鍵列。

總結

UPDATE ... SET col = REPLACE(col, 'old', 'new') WHERE col LIKE '%old%' 是 MySQL 字段部分內容批量替換的標準模式。關鍵在於：加 WHERE 縮小範圍、分批執行控制鎖、注意替換順序避免衝突、驗證字符集校對規則。生產環境執行前，務必在測試庫驗證結果與影響行數。