INSERT INTO ...重复更新所有值 [英] INSERT INTO ... ON DUPLICATE UPDATE all values

问题描述

我可以安全更新像这样的所有值:

Can I safely update all values like this:

INSERT INTO tbl_name SET `a`=:a, `b`=:b, ... `z`=:z
ON DUPLICATE KEY UPDATE
`a`=VALUES(`a`), `b`=VALUES(`b`), ... `z`=VALUES(`z`);

我已经尝试过了，而且行得通.但是它仅在一个innodb表上进行了测试.

I have tried it, and it works. But it's tested only on one innodb table.

我发现了以下相关问题 1 ， 2 ，

I have found these related questions 1, 2, 3 but there's no mention that updating ALL columns (unique and/or pk) is 100% safe.

在更新具有相同值的PK时不会产生任何错误.
用相同的值更新Unique col不会产生任何错误.

It doesn't create any error on updating PK with same value.
It doesn't create any error on updating Unique col with same value.

那么，使用100％安全吗?如果不是，什么情况下发生崩溃?

--------- 编辑 -------------
已添加...最多达到z col

--------- edit -------------
added ... up to z col

推荐答案

INSERT INTO … ON DUPLICATE KEY UPDATE以这种方式工作:

• 它尝试照常插入行
• 如果任何列中发生任何约束违例，它将在INSERT语句与之冲突的现有行上执行UPDATE语句

• it attempts to insert a row as usual
• if any constraint violation in any column occurs, it executes the UPDATE statement instead on the existing row with which the INSERT statement clashed

这只是意味着，如果仅UPDATE语句将成功，则它的ON DUPLICATE KEY UPDATE等效项也将起作用.这意味着您显然也不能违反UPDATE语句中的任何约束，否则整个操作都会失败(例如，尝试在已经存在于另一行中的UNIQUE列上设置一个值).

That simply means if the UPDATE statement alone would be successful, its ON DUPLICATE KEY UPDATE equivalent will also work. That means you obviously can't violate any constraints in the UPDATE statement either, or the whole thing will fail (e.g. trying to set a value on a UNIQUE column which already exists in another row).

如果您再次在同一列上设置相同的值，则基本上是无操作.列中的值不变，因此也不会引起任何错误(假设您没有任何可能行为异常的真正BEFORE UPDATE触发器).这里的列数无关紧要，您可以根据需要在一个语句中更改任意数目.

If you set the same value on the same column again, that's essentially a no-op. The value in the column doesn't change and therefore also can't cause any errors (let's assume you don't have any really funky BEFORE UPDATE triggers which can misbehave). The number of columns is irrelevant here, you can change as many or as few in one statement as you like.

这篇关于INSERT INTO ...重复更新所有值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！