MySQL 在“过期”后删除行
我正在尝试删除 MySQL 数据库中的过期条目，在创建或更新时，名为 lastBeat 的字段会使用 CURRENT_TIME 进行更新，并且我使用以下查询来检查/删除超过 20 秒的行：
DELETE * FROM `rmachines` WHERE
`lastBeat` < (NOW() - 20);
我还尝试了 CURRENT_TIME 而不是 NOW()
有 2 个主循环：
每秒更新 rmachines 中的一行
执行删除查询
如果 2 执行得很快，则为时间滚动到下一分钟（即 59-60 秒），它会删除该行，就好像它已经过期一样（即使它肯定没有！），否则它表现得很好。
如果 2 每秒执行一次，则不太明显，“错误到期”很少发生，但我每秒运行 5 次以暴露“问题”。
我找到了一个解决方案，经过测试，似乎可以在相同的场景下工作：
但是谁能告诉我为什么我的方法不起作用？
I am trying to delete expired entries in a MySQL database, on creation or update a field called lastBeat is updated with CURRENT_TIME and I use the following query to check/delete rows older than 20 seconds:
DELETE * FROM `rmachines` WHERE
`lastBeat` < (NOW() - 20);
I have also tried CURRENT_TIME instead of NOW()
There are 2 main loops:
Updates a row in rmachines every second
Executes the delete query
If 2 is executed rapidly, as the time rolls over to the next minute (i.e. 59-60 seconds) it deletes the row as if it has expired (even thought it definitely has not!), otherwise it behaves fine.
If 2 executes once a second this is not so noticable, the "false expiry" happens rarely but I run it 5 times per second to expose the 'issue'.
I found a solution, tested and seems to work under the same scenarios:
a job to delete rows older than 3 months in mysql database
But can anyone tell me why my method does not work?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您将 NOW() 转换为一个数字，然后从中减去 20。相反，您应该使用间隔减去 20 秒：
可以在此处看到差异：
时间
00:06:59将在00:06:49之后但之前进行比较>000689。You are casting NOW() to a number and then subtracting 20 from it. Instead you should subtract 20 seconds using an interval:
The difference can be seen here:
The time
00:06:59will compare after00:06:49but before000689.