MySQL Update与Delete+Insert性能对比及批量更新方案问询

你不需要逐条执行UPDATE语句，以下方案可以在兼顾性能、数据一致性的前提下完成批量写入，同时避免全删全插带来的各类副作用。

两种现有方案的实际生产缺陷 #

• 逐条INSERT新增+逐条UPDATE修改：当单店修改的商品量较大时，大量单条SQL会产生多次数据库网络往返，每一次SQL执行都有独立的通信开销，1000条更新的耗时会是批量操作的数十倍，高并发场景下很容易打满数据库连接。
• 全删全插：除了你提到的全量数据传输浪费、自增ID快速消耗问题，还有两个致命缺陷：一是删除和插入不是原子操作，中途报错会直接导致店铺商品数据全部丢失；二是如果其他业务表（比如商品销量、用户收藏表）关联了product_id作为外键，删除操作会直接触发外键约束报错，根本无法执行。

推荐方案：基于INSERT ... ON DUPLICATE KEY UPDATE实现批量Upsert #

这是MySQL原生支持的原子写入语法，最适配你当前的场景：一次SQL请求就能同时处理新增和修改逻辑，不需要区分记录状态，也不会改动未提交的存量数据。

实现原理 #

Product表的product_id是自增主键，本身自带唯一约束。当执行INSERT语句时，如果传入的product_id已经存在，MySQL不会报错，而是自动执行ON DUPLICATE KEY UPDATE后面定义的字段更新逻辑；如果product_id不存在（新增记录传null即可），就正常走插入逻辑，使用自增生成新ID。

Node.js + mysql2 实现代码 #

你只需要让前端提交有变动的记录即可：也就是标记saved_in_db=false的新增记录、标记changed=true的修改记录，完全不需要传未改动的存量数据，大幅减少前后端传输量。

// 前端提交的变动商品列表
const changedProducts = req.body.changedProducts;
// 构造批量写入的参数数组
const insertValues = changedProducts.map(product => [
  product.product_id ?? null, // 新增记录无product_id，传null触发自增
  product.p_name,
  product.p_price,
  product.shop_id
]);
// 开启事务保证原子性
const conn = await pool.getConnection();
try {
  await conn.beginTransaction();
  // 单条SQL完成所有新增+修改
  const upsertSql = `
    INSERT INTO Product (product_id, p_name, p_price, shop_id)
    VALUES ?
    ON DUPLICATE KEY UPDATE
      p_name = VALUES(p_name),
      p_price = VALUES(p_price)
  `;
  await conn.query(upsertSql, [insertValues]);
  await conn.commit();
} catch (err) {
  await conn.rollback();
  throw err;
} finally {
  conn.release();
}

方案优势 #

• 性能极高：不管是1条还是1000条变动记录，都只需要一次数据库请求，没有额外的网络往返开销，性能比逐条更新高1~2个数量级
• 无自增ID浪费：只有新增记录会占用新的自增ID，修改原有记录不会消耗ID，不存在ID耗尽风险
• 数据安全：事务包裹下所有操作原子生效，不会出现部分写入成功的脏数据；未提交的存量商品完全不受影响，也不会触发外键约束报错
• 逻辑简洁：不需要在服务端拆分新增、修改逻辑分别处理，代码维护成本低

可选补充方案：CASE WHEN 构造单条批量UPDATE #

如果某一次提交只有修改记录、没有新增记录，也可以通过CASE WHEN语法把所有更新逻辑拼接成单条UPDATE语句执行，示例SQL如下：

UPDATE Product
SET
  p_name = CASE product_id
    WHEN 101 THEN '无线耳机'
    WHEN 102 THEN '充电宝'
  END,
  p_price = CASE product_id
    WHEN 101 THEN 199
    WHEN 102 THEN 89
  END
WHERE product_id IN (101, 102);

这个方案性能和Upsert相当，但需要你在服务端单独拆分新增、修改的记录分别处理，代码复杂度更高，优先推荐用Upsert方案。

注意事项 #

• 如果单次提交的变动记录超过5000条，建议按每1000条拆分批次执行，避免单条SQL体积超过MySQL的max_allowed_packet限制导致执行失败
• 绝对不要在生产环境用全删全插的方案，除了前面提到的问题，还会导致和商品ID关联的历史业务数据（比如销售记录、用户评价、收藏数据）全部关联失效，引发严重业务故障

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