域名迁移要换全站URL、敏感词过滤要批量改星号、数据清洗要统一字段格式、价格调整要给所有商品打8折，这四个场景的批量替换方案，踩坑多的反而救得快

一、先看你的替换场景属于哪种，不同场景方案完全不同

数据库批量替换不是一条SQL通吃。单表几万行和几千万行、全字段替换和条件替换、有索引的字段和没索引的字段，方案差距巨大。先对号入座再选工具。

二、SQL内置函数：最简单直接的方案

如果你只需要替换某个字段中的字符串内容，数据库自带的字符串函数就够了。MySQL、PostgreSQL、SQL Server都有对应的函数，但语法略有差异。

MySQL / MariaDB：REPLACE()

-- ⚠️ 安全操作三步走：先查、再开事务、最后提交-- 第一步：先SELECT确认影响范围SELECT COUNT(*), COUNT(DISTINCT id)FROM wp_postsWHERE post_content LIKE '%old-domain.com%';-- 再预览几条替换后的效果SELECT id, post_title,LEFT(post_content, 100) AS before_text,LEFT(REPLACE(post_content, 'old-domain.com', 'new-domain.com'), 100) AS after_textFROM wp_postsWHERE post_content LIKE '%old-domain.com%'LIMIT 5;-- 第二步：开启事务执行替换START TRANSACTION;UPDATE wp_postsSET post_content = REPLACE(post_content, 'old-domain.com', 'new-domain.com')WHERE post_content LIKE '%old-domain.com%';-- 检查结果SELECT COUNT(*) FROM wp_posts WHERE post_content LIKE '%old-domain.com%';-- 第三步：确认无误后提交，有问题就ROLLBACKCOMMIT;-- ROLLBACK;  -- 有问题执行这个回滚

PostgreSQL：REPLACE() + REGEXP_REPLACE()

-- 普通字符串替换UPDATE articlesSET content = REPLACE(content, 'http://old.com', 'https://new.com')WHERE content LIKE '%http://old.com%';-- 正则替换：把所有手机号中间四位改成****UPDATE usersSET phone = REGEXP_REPLACE(phone, '(\d{3})\d{4}(\d{4})', '\1****\2')WHERE phone ~ '\d{11}';-- 正则替换：清除HTML标签保留纯文本UPDATE articlesSET content = REGEXP_REPLACE(content, '<[^>]+>', '', 'g')WHERE content ~ '<[^>]+>';

SQL Server：REPLACE() + STUFF()

-- 字符串替换BEGIN TRANSACTION;UPDATE ProductsSET Description = REPLACE(CAST(Description AS NVARCHAR(MAX)), N'旧品牌', N'新品牌')WHERE Description LIKE N'%旧品牌%';-- 手机号中间四位掩码（SQL Server用STUFF）UPDATE CustomersSET Phone = STUFF(Phone, 4, 4, '****')WHERE LEN(Phone) = 11;COMMIT TRANSACTION;

三、CASE WHEN + UPDATE：条件分支批量修改

不是所有替换都是简单的字符串查找替换。电商价格调整、用户状态批量变更、分类批量迁移——这些场景需要根据不同的条件执行不同的替换逻辑。

-- 场景1：分类批量迁移（把多个旧分类ID映射到新分类ID）UPDATE productsSET category_id = CASEWHEN category_id IN (12, 15, 18) THEN 200    -- 旧电子产品类 → 新数码类WHEN category_id IN (25, 28) THEN 300          -- 旧服装类 → 新服饰类WHEN category_id BETWEEN 50 AND 59 THEN 400    -- 旧家居系列 → 新家居类ELSE category_id                          -- 其他保持不变ENDWHERE category_id IN (12, 15, 18, 25, 28) OR category_id BETWEEN 50 AND 59;-- 场景2：按条件分级打折UPDATE productsSET price = CASEWHEN price >= 1000 THEN price * 0.7    -- 高价商品打7折WHEN price >= 500 THEN price * 0.8     -- 中价商品打8折WHEN price >= 100 THEN price * 0.9     -- 低价商品打9折ELSE price                            -- 低于100不打折END,updated_at = NOW()WHERE status = 'active';-- 场景3：多字段联动更新（一条SQL更新多个字段）UPDATE ordersSET status = CASEWHEN paid_at IS NOT NULL AND shipped_at IS NULL THEN 'paid'WHEN shipped_at IS NOT NULL THEN 'shipped'ELSE 'pending'END,priority = CASEWHEN total_amount > 10000 THEN 1WHEN total_amount > 1000 THEN 2ELSE 3ENDWHERE status = 'pending';

四、正则替换：格式清洗的利器

替换手机号格式、清洗HTML标签、统一日期格式、移除特殊字符——这些不是精确字符串替换，需要正则表达式。

MySQL 8.0 正则替换常用场景

-- 手机号脱敏：13812345678 → 138****5678UPDATE usersSET phone = REGEXP_REPLACE(phone, '([0-9]{3})[0-9]{4}([0-9]{4})', '$1****$2')WHERE phone REGEXP '^[0-9]{11}$';-- 清除所有HTML标签UPDATE articlesSET content = REGEXP_REPLACE(content, '<[^>]*>', '')WHERE content REGEXP '<[^>]*>';-- 统一日期格式：2024/01/15 和 2024-1-5 → 2024-01-15UPDATE recordsSET date_str = REGEXP_REPLACE(REGEXP_REPLACE(date_str, '/', '-'),'-([0-9])(?![0-9])', '-0$1')WHERE date_str REGEXP '[0-9]{4}[/-][0-9]{1,2}[/-][0-9]{1,2}';-- 移除开头和结尾的空白字符UPDATE productsSET name = REGEXP_REPLACE(name, '^[[:space:]]+|[[:space:]]+$', '')WHERE name REGEXP '^[[:space:]]|[[:space:]]$';

MySQL 5.7 没有REGEXP_REPLACE怎么办

MySQL 5.7仍然有大量生产环境在用，但它不支持REGEXP_REPLACE。这时候需要借助存储过程来实现正则替换：

DELIMITER $$CREATE FUNCTION regex_replace(pattern VARCHAR(1000),replacement VARCHAR(1000),original VARCHAR(1000))RETURNS VARCHAR(1000)DETERMINISTICBEGINDECLARE temp VARCHAR(1000);DECLARE ch VARCHAR(1);DECLARE i INT DEFAULT 0;SET temp = '';IF original NOT REGEXP pattern THENRETURN original;END IF;-- 逐个字符遍历，找到匹配位置后用replacement替换WHILE i <= CHAR_LENGTH(original) DOSET i = i + 1;SET ch = SUBSTRING(original, i, 1);IF ch REGEXP pattern THENSET temp = CONCAT(temp, replacement);ELSESET temp = CONCAT(temp, ch);END IF;END WHILE;RETURN temp;END$$DELIMITER ;-- 使用自定义函数做正则替换（注意：这个简易版只能做单字符替换）UPDATE articlesSET content = regex_replace('[0-9]', '*', content)WHERE content REGEXP '[0-9]';

五、Python脚本方案：最灵活的全能选手

数据库内置函数搞不定的场景——跨表关联替换、条件逻辑太复杂SQL写不下、需要先做数据校验再替换、MySQL 5.7没有正则替换函数——这时候上Python脚本。

import pymysqlimport refrom typing import List, Tuple, Callableclass DBBatchReplacer:"""数据库批量替换器：支持字符串替换、正则替换、自定义替换函数"""def __init__(self, host, user, password, database, port=3306):self.conn_config = {"host": host, "user": user, "password": password,"database": database, "port": port, "charset": "utf8mb4"}def simple_replace(self, table, field, old_str, new_str, where_clause="", batch_size=1000):"""简单字符串替换（分批处理，不锁表）"""conn = pymysql.connect(**self.conn_config)cursor = conn.cursor()try:# 先统计总数count_sql = f"SELECT COUNT(*) FROM {table} WHERE {field} LIKE %s"if where_clause:count_sql += f" AND {where_clause}"cursor.execute(count_sql, (f"%{old_str}%",))total = cursor.fetchone()[0]print(f"匹配到 {total} 条记录")if total == 0:return# 分批更新updated = 0update_sql = f"UPDATE {table} SET {field} = REPLACE({field}, %s, %s) WHERE {field} LIKE %s"if where_clause:update_sql += f" AND {where_clause}"update_sql += f" LIMIT {batch_size}"while updated < total:cursor.execute(update_sql, (old_str, new_str, f"%{old_str}%"))affected = cursor.rowcountif affected == 0:breakconn.commit()updated += affectedprint(f"进度: {updated}/{total} ({updated*100//total}%)")print(f"✅ 完成：{updated} 条记录已更新")except Exception as e:conn.rollback()print(f"❌ 出错已回滚: {e}")raisefinally:cursor.close()conn.close()def regex_replace(self, table, field, pattern, replacement, where_clause="", batch_size=500, dry_run=False):"""正则替换：读取→Python正则处理→写回（MySQL 5.7友好）"""conn = pymysql.connect(**self.conn_config)# 需要 SSCursor 避免大结果集撑爆内存conn_read = pymysql.connect(**self.conn_config, cursorclass=pymysql.cursors.SSCursor)try:select_sql = f"SELECT id, {field} FROM {table} WHERE {field} IS NOT NULL"if where_clause:select_sql += f" AND {where_clause}"read_cursor = conn_read.cursor()read_cursor.execute(select_sql)update_cursor = conn.cursor()updated = 0batch = []for row in read_cursor:row_id, content = rowif content is None:continuenew_content = re.sub(pattern, replacement, content)if new_content != content:if dry_run:print(f"[DRY RUN] id={row_id}: {content[:80]} → {new_content[:80]}")else:batch.append((new_content, row_id))if len(batch) >= batch_size:update_cursor.executemany(f"UPDATE {table} SET {field} = %s WHERE id = %s",batch)conn.commit()updated += len(batch)print(f"已更新 {updated} 条")batch = []# 处理最后一批if batch and not dry_run:update_cursor.executemany(f"UPDATE {table} SET {field} = %s WHERE id = %s",batch)conn.commit()updated += len(batch)print(f"✅ 完成：{updated} 条记录已更新" if not dry_run else "[DRY RUN 完毕]")finally:read_cursor.close()conn_read.close()conn.close()# ─── 使用示例 ───if __name__ == "__main__":replacer = DBBatchReplacer("localhost", "root", "password", "mydb")# 1. 域名替换replacer.simple_replace("wp_posts", "post_content", "http://old.com", "https://new.com")# 2. 正则替换：手机号脱敏（先dry_run预览）replacer.regex_replace("users", "phone",r'(\d{3})\d{4}(\d{4})', r'\1****\2',where_clause="phone REGEXP '^[0-9]{11}$'",dry_run=True   # 先预览不真改)# 3. 确认无误后执行replacer.regex_replace("users", "phone",r'(\d{3})\d{4}(\d{4})', r'\1****\2',where_clause="phone REGEXP '^[0-9]{11}$'",dry_run=False)

全库多表多字段替换脚本

网站换域名最常见也最头疼的场景：不是只改wp_posts一张表，而是wp_postmeta、wp_options、wp_comments等几十张表里都可能存了旧URL。手动一个个字段去替换不现实：

def scan_and_replace_all(replacer, old_str, new_str, skip_tables=None):"""扫描库中所有表的所有文本字段，替换匹配的字符串"""skip_tables = skip_tables or []conn = pymysql.connect(**replacer.conn_config)cursor = conn.cursor()# 获取所有表cursor.execute("SHOW TABLES")tables = [row[0] for row in cursor.fetchall()]text_types = {'varchar', 'char', 'text', 'mediumtext', 'longtext', 'tinytext'}for table in tables:if table in skip_tables:print(f"⏭️ 跳过表: {table}")continue# 获取表中所有文本类型字段cursor.execute(f"DESCRIBE {table}")text_fields = []has_id = Falsefor row in cursor.fetchall():field_name = row[0]field_type = row[1].lower().split('(')[0]if field_name == 'id':has_id = Trueif field_type in text_types:text_fields.append(field_name)if not text_fields or not has_id:continuefor field in text_fields:# 先检查有没有需要替换的内容cursor.execute(f"SELECT COUNT(*) FROM {table} WHERE {field} LIKE %s", (f"%{old_str}%",))count = cursor.fetchone()[0]if count > 0:print(f"🔍 {table}.{field}: {count} 条匹配")replacer.simple_replace(table, field, old_str, new_str)cursor.close()conn.close()

六、大表替换：千万级数据不锁表的方案

如果表只有几万行，一条UPDATE最多锁几秒，业务可以接受。但如果表有几百万甚至几千万行，直接UPDATE会锁住整张表，线上服务直接瘫痪。这时候需要分批处理。

方案一：LIMIT分批更新（最简单）

-- MySQL存储过程：分批更新，每批1000行，批次间暂停0.5秒DELIMITER $$CREATE PROCEDURE batch_replace_url(IN table_name VARCHAR(64),IN field_name VARCHAR(64),IN old_val VARCHAR(512),IN new_val VARCHAR(512),IN batch_size INT,IN sleep_ms INT)BEGINDECLARE done INT DEFAULT FALSE;DECLARE affected INT DEFAULT 1;DECLARE total_updated INT DEFAULT 0;batch_loop: LOOPSET @sql = CONCAT('UPDATE ', table_name,' SET ', field_name, ' = REPLACE(', field_name, ', ?, ?)',' WHERE ', field_name, ' LIKE ?',' LIMIT ', batch_size);PREPARE stmt FROM @sql;SET @old = old_val, @new = new_val, @pattern = CONCAT('%', old_val, '%');EXECUTE stmt USING @old, @new, @pattern;DEALLOCATE PREPARE stmt;SET affected = ROW_COUNT();SET total_updated = total_updated + affected;SELECT CONCAT('批次完成: ', affected, ' 行, 累计: ', total_updated) AS progress;IF affected = 0 THENLEAVE batch_loop;END IF;-- 每批之间暂停，给其他查询留出执行窗口DO SLEEP(sleep_ms / 1000);END LOOP;SELECT CONCAT('✅ 全部完成，共更新 ', total_updated, ' 行') AS result;END$$DELIMITER ;-- 调用：每批1000行，批间暂停500msCALL batch_replace_url('wp_posts', 'post_content', 'http://old.com', 'https://new.com', 1000, 500);

方案二：主键区间分批（更快，适合有自增ID的表）

-- 按主键ID区间分批，每批5000个ID范围SET @min_id = (SELECT MIN(id) FROM large_table WHERE content LIKE '%old_str%');SET @max_id = (SELECT MAX(id) FROM large_table WHERE content LIKE '%old_str%');SET @batch = 5000;SET @current = @min_id;WHILE @current <= @max_id DOUPDATE large_tableSET content = REPLACE(content, 'old_str', 'new_str')WHERE id BETWEEN @current AND @current + @batch - 1AND content LIKE '%old_str%';SET @current = @current + @batch;DO SLEEP(0.2);  -- 批次间暂停200msEND WHILE;

七、跨表关联替换和MERGE方案

有时候替换的值不是固定的，而是要根据另一张表的映射关系来确定。比如有一张"旧分类→新分类"的映射表，需要批量替换products表中的category_id。

-- MySQL: UPDATE JOIN（根据映射表替换）UPDATE products pJOIN category_mapping m ON p.category_id = m.old_category_idSET p.category_id = m.new_category_id,p.updated_at = NOW();-- PostgreSQL: UPDATE FROMUPDATE products pSET category_id = m.new_category_id,updated_at = NOW()FROM category_mapping mWHERE p.category_id = m.old_category_id;-- SQL Server: UPDATE FROMUPDATE pSET p.category_id = m.new_category_id,p.updated_at = GETDATE()FROM products pINNER JOIN category_mapping m ON p.category_id = m.old_category_id;-- PostgreSQL: MERGE (UPSERT风格的条件替换)MERGE INTO inventory iUSING stock_update s ON i.product_id = s.product_idWHEN MATCHED THENUPDATE SET i.quantity = s.new_quantity,i.price = CASEWHEN s.discount_type = 'percent' THEN i.price * (1 - s.discount_value / 100)WHEN s.discount_type = 'fixed' THEN i.price - s.discount_valueELSE i.priceEND;

八、GUI工具：不用写SQL的可视化方案

如果不想写SQL，几款数据库管理工具也内置了批量替换功能：

九、替换前必须做的五件事

最后说一句

数据库批量替换这件事，技术本身不复杂，REPLACE加个WHERE就完事。真正容易翻车的地方全在操作流程上：忘了备份、没开事务、WHERE条件没写好、大表直接锁死。把上面五条操作守则记牢——备份、测试、事务、精确WHERE、低峰期执行——比选什么工具重要得多。

简单场景用数据库自带的REPLACE和CASE WHEN就够了，正则替换上REGEXP_REPLACE或Python脚本，大表分批走存储过程或主键区间。只要流程对，几千行和几千万行一样安全。