Conditional ON DUPLICATE KEY UPDATE(仅在特定条件为真时更新)

2021-12-26 00:00:00 sql mysql pdo

我使用了以下查询:

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).
  • True: 更新lastupdate = NOW()，给programrunco​​unt 加一个，然后更新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 - 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在 'IF(lastupdate > NOW() - INTERVAL 20 MINUTE, VALUES(lastupdate, programrunco​​unt +' 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.

相关文章