如何高效为250万条数据库记录追加指定值？

原始表结构及数据 #

预期更新结果 #

1. 核心更新SQL语句 #

根据你使用的数据库类型，选择对应的语句：

MySQL/MariaDB #

UPDATE 你的表名
SET center = CASE
    WHEN center IS NOT NULL AND center != '' THEN CONCAT(center, ', apple')
    ELSE 'apple'
END;

PostgreSQL #

UPDATE 你的表名
SET center = CASE
    WHEN center IS NOT NULL AND center != '' THEN center || ', apple'
    ELSE 'apple'
END;

SQL Server #

UPDATE 你的表名
SET center = CASE
    WHEN center IS NOT NULL AND center != '' THEN CONCAT(center, ', apple')
    ELSE 'apple'
END;

2. 250万条数据高效更新的关键技巧 #

直接全表更新会导致长时间锁表、占用大量资源，建议按以下方式操作：

• 分批更新：按主键或索引字段拆分批次，每次更新1万-5万条（根据数据库性能调整），示例（MySQL）：

  SET @batch_size = 10000;
  SET @offset = 0;
  
  REPEAT
      UPDATE 你的表名
      SET center = CASE
          WHEN center IS NOT NULL AND center != '' THEN CONCAT(center, ', apple')
          ELSE 'apple'
      END
      WHERE id BETWEEN @offset AND @offset + @batch_size - 1; -- 假设id是自增主键
  
      SET @offset = @offset + @batch_size;
      SELECT SLEEP(1); -- 可选，降低数据库负载
  UNTIL ROW_COUNT() = 0 END REPEAT;
  

• 关闭自动提交：开启事务批量提交，减少磁盘IO次数。比如MySQL中先执行SET autocommit = 0;，所有批次更新完成后再执行COMMIT;。
• 依赖索引过滤：分批更新的WHERE条件必须走索引（比如主键、唯一索引），避免全表扫描拖慢速度。
• 错峰操作：选择业务低峰期执行更新，避免影响正常业务流程。
• 先备份再操作：更新前务必备份目标表，或者创建快照，防止更新出错无法回滚。
• 小批量测试：先用100-1000条数据验证SQL逻辑，确认结果符合预期后再执行全量更新。

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