如何使用Knex在MsSQL中通过单次查询更新多行数据？

兄弟，我太懂你翻遍SO找不到合适方案的憋屈了！你要的批量更新多条不同实体的位置参数完全可以实现，分原生SQL和Knex.js两种场景给你捋清楚：

不同数据库的批量更新语法略有差异，这里给你最常用的两种写法：

MySQL 写法 #

兼容所有MySQL版本的CASE WHEN方案 #

这种写法最通用，不管版本高低都能跑：

UPDATE entities
SET 
  x = CASE id
    WHEN 1 THEN 2
    WHEN 2 THEN 5
    WHEN 3 THEN 10
    WHEN 4 THEN 20
  END,
  y = CASE id
    WHEN 1 THEN 2
    WHEN 2 THEN 5
    WHEN 3 THEN 10
    WHEN 4 THEN 20
  END,
  o = CASE id
    WHEN 1 THEN 90
    WHEN 2 THEN 0
    WHEN 3 THEN 270
    WHEN 4 THEN 180
  END
WHERE id IN (1, 2, 3, 4);

MySQL 8.0+ 更简洁的JOIN方案 #

如果你的MySQL版本是8.0及以上，可以用VALUES行构造器结合JOIN，写法更直观：

UPDATE entities e
JOIN (
  VALUES 
    (1, 2, 2, 90),
    (2, 5, 5, 0),
    (3, 10, 10, 270),
    (4, 20, 20, 180)
) AS temp(id, x, y, o) ON e.id = temp.id
SET e.x = temp.x, e.y = temp.y, e.o = temp.o;

PostgreSQL 写法 #

PostgreSQL支持直接用FROM子句关联临时数据集更新，语法很清晰：

UPDATE entities e
SET x = temp.x, y = temp.y, o = temp.o
FROM (
  VALUES 
    (1, 2, 2, 90),
    (2, 5, 5, 0),
    (3, 10, 10, 270),
    (4, 20, 20, 180)
) AS temp(id, x, y, o)
WHERE e.id = temp.id;

Knex本身没有原生的批量更新语法，但可以通过两种方式实现需求：

动态生成CASE WHEN语句 #

这种方式兼容所有数据库，和原生SQL的CASE WHEN思路一致：

const updatedEntities = [
  {id:1, x:2, y:2, o:90}, 
  {id:2, x:5, y:5, o:0}, 
  {id:3, x:10, y:10, o:270}, 
  {id:4, x:20, y:20, o:180}
];

// 封装一个生成CASE WHEN的工具函数
const buildCaseClause = (column) => {
  let caseStmt = knex.raw(`CASE id`);
  updatedEntities.forEach(entity => {
    caseStmt = caseStmt.when(entity.id, entity[column]);
  });
  return caseStmt.end();
};

// 执行批量更新
await knex('entities')
  .update({
    x: buildCaseClause('x'),
    y: buildCaseClause('y'),
    o: buildCaseClause('o')
  })
  .whereIn('id', updatedEntities.map(item => item.id));

用Knex.raw结合行构造器（适合高版本MySQL/PostgreSQL） #

如果你的数据库支持行构造器，可以直接用knex.raw拼接原生SQL的简洁写法：

// MySQL 8.0+ 版本
await knex.raw(`
  UPDATE entities e
  JOIN (VALUES ?) AS temp(id, x, y, o) ON e.id = temp.id
  SET e.x = temp.x, e.y = temp.y, e.o = temp.o
`, [updatedEntities.map(item => [item.id, item.x, item.y, item.o])]);

// PostgreSQL 版本
await knex.raw(`
  UPDATE entities e
  SET x = temp.x, y = temp.y, o = temp.o
  FROM (VALUES ?) AS temp(id, x, y, o)
  WHERE e.id = temp.id
`, [updatedEntities.map(item => [item.id, item.x, item.y, item.o])]);

额外提醒 #

不管用哪种方式，都建议把批量更新放在事务里执行，避免部分更新失败导致数据不一致。比如Knex里可以用knex.transaction包裹更新逻辑。

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