在重复键时有条件地更新 (仅在满足特定条件时更新)

Question

我正在使用以下查询：

INSERT INTO userlist (username, lastupdate, programruncount, ip)
VALUES (:username, NOW(), 1, :ip)
ON DUPLICATE KEY UPDATE
lastupdate = NOW(), programruncount = programruncount + 1, ip = :ip;

然而，我还希望将ON DUPLICATE KEY UPDATE设置为有条件的，以便它执行以下操作:

• 如果 lastupdate比20分钟之前更早(lastupdate > NOW() - INTERVAL 20 MINUTE)。
• True: 更新lastupdate = NOW()，将一个添加到programruncount，然后更新ip = :ip。
• False: 所有字段都应该保持不变。

我不太确定如何实现这一点，但在查找了一些资料后，我尝试在ON DUPLICATE KEY UPDATE部分中使用IF语句。

INSERT INTO userlist (username, lastupdate, programruncount, ip)
VALUES ("testuser", NOW(), "1", "127.0.0.1")
ON DUPLICATE KEY UPDATE
IF(lastupdate > NOW() - INTERVAL 20 MINUTE, VALUES(lastupdate, programruncount + 1),
lastupdate, programruncount);

但是我遇到了以下错误：#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF(lastupdate > NOW() - INTERVAL 20 MINUTE, VALUES(lastupdate, programruncount +' at line 6

- Dan

---

我敢打赌，来到这里的每个人都是为了保护他们的网站免受这些烦人的自动化攻击（SQL注入脚本等）。 - c00000fd

Answer 1

你在错误地使用IF语句和VALUES()函数。

INSERT INTO userlist (username, lastupdate, programruncount, ip)
VALUES (:username, NOW(), 1, :ip)
ON DUPLICATE KEY UPDATE
lastupdate = IF(NOW() > lastupdate + INTERVAL 20 MINUTE, NOW(), lastupdate),
programruncount = IF(NOW() > lastupdate + INTERVAL 20 MINUTE, programruncount + 1,   programruncount),
ip = IF(NOW() > lastupdate + INTERVAL 20 MINUTE, VALUES(ip), ip);

所以IF检查一个条件，并返回作为其参数提供的两个值之一。请参见MySQL的流控制运算符。

- Arustamyan G.

---

这将保持字段不变，但无论如何仍会执行更新。如果最后一个更新条件未满足，有没有办法什么都不做？ - anna

Answer 2

我总是这样做：

INSERT INTO userlist (username, lastupdate, programruncount, ip) VALUES (:username, NOW(), 1, :ip)
ON DUPLICATE KEY UPDATE
lastupdate = IF(lastupdate > NOW() - INTERVAL 20 MINUTE, NOW(), lastupdate),
programruncount = IF(lastupdate > NOW() - INTERVAL 20 MINUTE, programruncount + 1, programruncount),
ip = IF(lastupdate > NOW() - INTERVAL 20 MINUTE, VALUE(ip), ip);

VALUE(ip) --> 使用新值更新
ip --> 不会更新该字段

- acecoolz

---

1. 不是value()，而是values()
2. 它已经被弃用了

- Your Common Sense

Answer 3

我对INSERT INTO ... ON DUPLICATE KEY UPDATE语句做出了一个令人震惊的发现。如果UPDATE子句中某些字段的值取决于另一个字段的原始值，那么字段的顺序至关重要。

INSERT INTO tablename (primari_key_column, column_datetime, column3, column4)
     VALUES ('primari_key_value', 'datetime_value', 'value3', 'value4') 
ON DUPLICATE KEY UPDATE column3 = IF(column_datetime <= 'datetime_value', 'value3', column3)
                      , column4 = IF(column_datetime <= 'datetime_value', 'value4', column4)
                      , column_datetime = IF(column_datetime <= 'datetime_value', 'datetime_value', column_datetime);

示例显示了正确的用法，即最后修改的字段是决定新值是放置在受影响行中还是保留旧值的字段。

INSERT INTO tablename (primari_key_column, column_datetime, column3, column4)
     VALUES ('primari_key_value', 'datetime_value', 'value3', 'value4') 
ON DUPLICATE KEY UPDATE column_datetime = IF(column_datetime <= 'datetime_value', 'datetime_value', column_datetime)
                      , column3 = IF(column_datetime <= 'datetime_value', 'value3', column3)
                      , column4 = IF(columnn_datetime <= 'datetime_value', 'value4', column4);

在这个错误的情况下，只有对column_datetime字段进行了修改，其他字段没有进行修改，因为条件在此之后不再满足。

这种行为在AWS RDS Aurora 5.7（MySQL 5.7.12）中确实存在。

- Szila73