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

MySQL 批量替換字段字符串：REPLACE 函數實戰與避坑指南

編輯：MySQL 資料庫相關知識

MySQL 批量字符串替換：UPDATE ... REPLACE 完整指南

利用 MySQL 內建 REPLACE(str, from_str, to_str) 函數，可在單條 SQL 中完成百萬級數據的字段級批量替換，無需導出腳本處理。本文涵蓋語法、條件過濾、實戰案例與效能優化。

一、核心語法

UPDATE table_name
SET column_name = REPLACE(column_name, 'old_string', 'new_string')
WHERE condition;

二、實戰案例

1. 站點遷移：端口號批量替換

-- 將 content_type__theme 表中 ID<300 的 field_down_value 字段 86→80
UPDATE content_type__theme
SET field_down_value = REPLACE(field_down_value, '86', '80')
WHERE ID < 300;

2. 內容修正：敏感詞/錯別字替換

UPDATE article
SET content = REPLACE(content, '解決', '解放')
WHERE ID < 5000;

3. 多字段同時替換

UPDATE products
SET name = REPLACE(name, '舊品牌', '新品牌'),
    description = REPLACE(description, '舊品牌', '新品牌')
WHERE category_id = 10;

三、進階技巧

正則替換（MySQL 8.0+）：REGEXP_REPLACE(col, 'pattern', 'repl') 支援正則表達式。
大小寫敏感替換：REPLACE 依賴字符集排序規則。需區分大小寫時，可用 COLLATE utf8mb4_bin：
```
UPDATE t SET c = REPLACE(c COLLATE utf8mb4_bin, 'ABC', 'xyz');
```
避免誤替換：針對特定上下文，結合 WHERE col LIKE '%old_string%' 縮小掃描範圍，減少無效行鎖。

四、大表效能優化

策略	操作	適用場景
分批提交	按主鍵範圍循環 UPDATE，每批 5000-10000 行	超 10 萬行，避免長事務鎖升級
暫停索引/約束	SET FOREIGN_KEY_CHECKS=0; SET UNIQUE_CHECKS=0; (導入後恢復)	全表大面積更新
pt-online-schema-change	Percona Toolkit 無鎖變更，亦可用於大批量更新	核心業務表，不能停機
新表寫入+重命名	CREATE TABLE new_t AS SELECT ..., REPLACE(...) FROM old_t; RENAME TABLE old_t TO old_t_bak, new_t TO old_t;	全表重寫，鎖表時間極短

五、避坑清單

❌ 忘記 WHERE 條件 → 全表掃描 + 全行鎖定，業務阻塞。
❌ 替換字符串包含在其他詞中 → 如將 86 替換為 80 可能誤傷 1860。對策：WHERE col LIKE '% 86 %' 或正則邊界 REGEXP '[[:<:]]86[[:>:]]'。
❌ 字符集不匹配 → 如表為 GBK，SQL 文件為 UTF-8，導致替換失效或亂碼。對策：客戶端 SET NAMES gbk 或統一 UTF8MB4。
❌ 觸發器/外鍵級聯 → 替換觸發級聯更新/刪除，放大影響。對策：暫停觸發器或評估業務影響。