MySQL事务中的多行多值更新

时间：2021-01-29 08:22:06

I am using innodb tables in MySQL. I want to update several rows in a table, with each row getting a different value, e.g.:

我在MySQL中使用innodb表。我想更新表中的几行,每行获得不同的值,例如:

UPDATE tbl_1 SET
   col1=3  WHERE id=25,
   col1=5  WHERE id=26

In Postgres I believe this is possible:

在Postgres中我相信这是可能的:

UPDATE tbl_1 SET col1 = t.col1 FROM (VALUES
        (25, 3)
        (26, 5)
) AS t(id, col1)
WHERE tbl_1.id = t.id;

How do you do this efficiently and effectively in a transaction?

您如何在交易中有效地执行此操作?

Issues I hit so far:

到目前为止我遇到的问题:

  • using an intermediate temporary MEMORY table turns out to not be transaction safe

    • 使用中间临时MEMORY表证明不是事务安全

  • using a TEMPORARY table - persumably MEMORY type again - is virtually undocumented and I can find no real explanation of how it works and how well it works in my case, for example any discussion on whether the table is truncated after each transaction on the session

    • 使用TEMPORARY表 - 显然是MEMORY类型 - 实际上没有文档记录,我找不到它的工作方式以及它在我的情况下的工作情况的真实解释,例如关于在会话中的每个事务之后表是否被截断的任何讨论

  • using an InnoDB table as a temporary table and filling, joining to update and then truncating it in the transaction seems a very expensive thing to do; I've been fighting MySQL's poor throughput enough as it is

    • 使用InnoDB表作为临时表并填充,加入更新然后在事务中截断它似乎是一件非常昂贵的事情;我一直在努力解决MySQL的糟糕吞吐量问题

1 个解决方案

#1

2  

Do you update with a case and set value for col1 depending on id

您是否根据id更新了case并为col1设置了值

UPDATE tbl_1 SET col1=CASE id WHEN 25 THEN 3 WHEN 26 THEN 5 END WHERE id IN (25,26)

#1

2  

Do you update with a case and set value for col1 depending on id

您是否根据id更新了case并为col1设置了值

UPDATE tbl_1 SET col1=CASE id WHEN 25 THEN 3 WHEN 26 THEN 5 END WHERE id IN (25,26)
标签：

相关文章