SQL如何批量更新替换表指定列中包含特定字符串的字段值？

基础实现语句 #

该语句可直接实现需求，仅修改包含Victor的行，不会影响其他数据：

-- 以MySQL为例，其他数据库仅需替换对应字符串拼接、替换函数即可
UPDATE table1 
SET RequestName = REPLACE(RequestName, 'Victor', CONCAT('Victor-', ID)) 
WHERE RequestName LIKE '%Victor%';

如果数据量在十万级以下，直接执行该语句即可满足需求。

百万级以上大表优化方案 #

数据量过大时直接执行全量更新容易引发长事务锁表、IO占满、业务不可用等问题，可采用以下优化手段：

• 加速筛选：普通B+树索引无法优化%Victor%的模糊匹配，可针对RequestName字段创建全文索引，大幅降低筛选符合条件行的时间，避免全表扫描。
• 分批更新：将大任务拆分为多个小事务执行，每次仅更新固定数量的行，执行完成后立即提交事务，避免锁表和日志爆满。参考MySQL分批实现：

-- 每次更新1000行，循环执行直到没有符合条件的数据
WHILE EXISTS (SELECT 1 FROM table1 WHERE RequestName LIKE '%Victor%') DO
    UPDATE table1 
    SET RequestName = REPLACE(RequestName, 'Victor', CONCAT('Victor-', ID)) 
    WHERE RequestName LIKE '%Victor%' 
    LIMIT 1000;
    COMMIT;
END WHILE;

其他数据库可根据自身语法调整，比如PostgreSQL可按ID范围分片、SQL Server使用TOP关键字限制行数。

• 错峰执行：选择业务低峰期执行更新操作，降低IO资源争抢和锁冲突概率，减少对正常业务的影响。
• 提前备份：更新前先备份待修改的数据，避免逻辑错误导致数据损坏无法回滚，备份参考语句：

CREATE TABLE table1_victor_backup AS SELECT * FROM table1 WHERE RequestName LIKE '%Victor%';

• 临时关闭非必要索引：如果表上存在大量二级索引，更新时会同步修改索引拖慢执行速度，可在业务允许停写的前提下临时关闭二级索引，更新完成后再重建，可提升数倍执行效率。

内容的提问来源于stack exchange，提问作者user1989