MySQL中的多个更新

Question

我知道你可以一次插入多个行，有没有办法在MySQL中一次更新多行(就像在一个查询中一样)？

编辑:例如，我有以下内容

Name   id  Col1  Col2
Row1   1    6     1
Row2   2    2     3
Row3   3    9     5
Row4   4    16    8

我希望将以下所有更新合并到一个查询中

UPDATE table SET Col1 = 1 WHERE id = 1;
UPDATE table SET Col1 = 2 WHERE id = 2;
UPDATE table SET Col2 = 3 WHERE id = 3;
UPDATE table SET Col1 = 10 WHERE id = 4;
UPDATE table SET Col2 = 12 WHERE id = 4;

Answer 1

是的，这是可能的-您可以使用INSERT ...在重复密钥更新时。

使用您的示例：

INSERT INTO table (id,Col1,Col2) VALUES (1,1,1),(2,2,3),(3,9,3),(4,10,12)
ON DUPLICATE KEY UPDATE Col1=VALUES(Col1),Col2=VALUES(Col2);

Answer 2

这个问题很老了，但我想用另一个答案来扩展这个主题。

我的观点是，实现这一点的最简单方法就是用一个事务包装多个查询。公认的答案INSERT ... ON DUPLICATE KEY UPDATE是一个很好的技巧，但人们应该意识到它的缺点和局限性：

• 如前所述，如果您碰巧启动了主键在表中不存在的行的查询，查询将插入新的“半生不熟”的记录。这可能不是您想要的
• 如果您有一个表有一个非空字段没有默认值，并且不想在查询中接触这个字段，即使您根本不插入一行，您也会得到"Field 'fieldname' doesn't have a default value" MySQL警告。如果你决定严格要求，并在你的应用程序中将mysql警告转变为运行时异常，这将给你带来麻烦。

我对建议的三种变体进行了一些性能测试，包括INSERT ... ON DUPLICATE KEY UPDATE变体、带有"case / when / then“子句的变体和带有事务的朴素方法。您可以获得python代码和结果here。总而言之，带有case语句的变体的速度是其他两个变体的两倍，但是很难为它编写正确且注入安全的代码，所以我个人坚持使用最简单的方法:使用事务。

编辑：Dakusan的调查结果证明我的性能估计不是很有效。有关更详细的研究，请访问this answer。

Answer 3

不确定为什么还没有提到另一个有用的选项：

UPDATE my_table m
JOIN (
    SELECT 1 as id, 10 as _col1, 20 as _col2
    UNION ALL
    SELECT 2, 5, 10
    UNION ALL
    SELECT 3, 15, 30
) vals ON m.id = vals.id
SET col1 = _col1, col2 = _col2;