我正在使用以下查询:
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