Skip to main content

MySQL 批量插入/更新

在 Node.js 中使用 mysql2 时，直接通过一个 connection.execute() 或 query() 语句并不能像批量插入那样（比如 INSERT INTO ... VALUES ?）直接传入一个数组来批量更新（UPDATE）。

SQL 的 UPDATE 语法标准中没有像 INSERT 这样原生支持批量参数绑定的语法。

以下是实现批量更新的几种主流方案，按推荐程度排序：

方案一：使用 INSERT ... ON DUPLICATE KEY UPDATE (最推荐)​

这是性能最好且最简洁的方法。它本意是“插入，如果主键冲突则更新”。你可以利用这一点来做批量更新。

  • 前提：必须有主键（Primary Key）或唯一索引（Unique Index）。
  • 优点：只需发送一条 SQL，速度极快。
const data = [
  [1, '新名称1', 20],
  [2, '新名称2', 25],
  [3, '新名称3', 30]
];

// SQL 解释：
// 尝试插入 id, name, age。如果 id 已存在，则执行后面的 UPDATE
// VALUES(column_name) 指的是引用前面传入的对应列的新值
const sql = `
  INSERT INTO users (id, name, age) 
  VALUES ? 
  ON DUPLICATE KEY UPDATE 
    name = VALUES(name), 
    age = VALUES(age)
`;

// 注意：这里使用 .query() 而不是 .execute()，
// 因为 .execute() 不支持这种将二维数组展开为 VALUES 的简写
await connection.query(sql, [data]);

方案二：使用 CASE WHEN 语句 (纯更新逻辑)​

如果你不想使用 INSERT 语义，可以使用 SQL 的 CASE 表达式。这种方法通过一个复杂的 SQL 语句一次性更新多条记录。

const users = [
  { id: 1, name: 'Alice', age: 21 },
  { id: 2, name: 'Bob', age: 26 }
];

const ids = users.map(u => u.id);
const nameCases = users.map(u => `WHEN ${u.id} THEN '${u.name}'`).join(' ');
const ageCases = users.map(u => `WHEN ${u.id} THEN ${u.age}`).join(' ');

// 生成的 SQL 类似于：
// UPDATE users SET 
//   name = CASE id WHEN 1 THEN 'Alice' WHEN 2 THEN 'Bob' END,
//   age = CASE id WHEN 1 THEN 21 WHEN 2 THEN 26 END
// WHERE id IN (1, 2)
const sql = `
  UPDATE users SET 
    name = CASE id ${nameCases} END,
    age = CASE id ${ageCases} END
  WHERE id IN (${ids.join(',')})
`;

await connection.query(sql);

注意：此方法在数据量巨大时生成的 SQL 字符串会非常长，有 SQL 注入风险，建议配合参数化处理。

方案三：使用 Promise.all 迸发执行 (简单但不推荐用于大量数据)​

如果你只有几条或十几条数据，直接循环执行 execute 是最简单的。

const users = [{id: 1, name: 'A'}, {id: 2, name: 'B'}];

await Promise.all(users.map(user => {
  return connection.execute(
    'UPDATE users SET name = ? WHERE id = ?',
    [user.name, user.id]
  );
}));
  • 缺点：如果数组有 1000 条数据，会瞬间向数据库发送 1000个请求，可能导致连接池耗尽或数据库压力过大。

方案四：开启 multipleStatements: true (不推荐)​

在建立连接时设置 multipleStatements: true，允许一次执行多条以分号分隔的 SQL。

// 连接配置
const connection = await mysql.createConnection({
  multipleStatements: true,
  // ... 其他配置
});

// 执行
const sql = "UPDATE users SET name='A' WHERE id=1; UPDATE users SET name='B' WHERE id=2;";
await connection.query(sql);
  • 风险：极易受到 SQL 注入攻击，通常在生产环境中被禁用。

总结与回答你的疑问​

  1. await connection.execute() 能一次更新多条吗？

    • 不能直接实现。execute 主要是为“预处理语句（Prepared Statements）”设计的，它期望一个 SQL 模板和一组对应占位符的数据。
    • 如果你传入 UPDATE ... SET ? WHERE ...，它无法自动为你循环。

  2. 最佳实践建议：

    • 如果数据量大且有主键：使用 方案一 (INSERT ... ON DUPLICATE KEY UPDATE)。
    • 如果只是更新几条：使用 方案三 (Promise.all)。
    • 如果你使用的是像 Sequelize 或 TypeORM 这样的 ORM，它们底层通常也是通过方案一或方案二来封装批量更新方法的。