有条件的ON DUPLICATE KEY UPDATE(仅在某些条件为真时更新) [英] Conditional ON DUPLICATE KEY UPDATE (Update only if certain condition is true)

问题描述

我正在使用以下查询:

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作为条件，因此它将执行以下操作:

However, I also want to make the ON DUPLICATE KEY UPDATE conditional, so it will do the following:

• IF lastupdate不到20分钟前(lastupdate > NOW() - INTERVAL 20 MINUTE).
• 是:更新lastupdate = NOW()，将其添加到programruncount，然后更新ip = :ip.
• 否:所有字段均应保持不变.

• IF lastupdate was less than 20 minutes ago (lastupdate > NOW() - INTERVAL 20 MINUTE).
• True: Update lastupdate = NOW(), add one to programruncount and then update ip = :ip.
• False: All fields should be left the same.

我不太确定该怎么做，但是环顾四周之后，我尝试在ON DUPLICATE KEY UPDATE部分中使用IF语句.

I am not really sure how I would do this but after looking around, I tried using an IF Statement in the ON DUPLICATE KEY UPDATE part.

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

However I am getting the following error: #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

推荐答案

您使用的IF语句不正确

you're using IF statement incorrectly

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, :ip, ip);

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

so IF checks for a condition and return one of two values provided as it's parameters. See MySQL's Flow Control Operators.

这篇关于有条件的ON DUPLICATE KEY UPDATE(仅在某些条件为真时更新)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！