MySQL delete row after 'expiry'
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 e开发者_运维知识库xecutes 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?
You are casting NOW() to a number and then subtracting 20 from it. Instead you should subtract 20 seconds using an interval:
NOW() - interval 20 second
The difference can be seen here:
SELECT NOW() - interval 20 second, NOW() - 20;
2010-06-11 00:06:49, 20100611000689.000000
^^ ^^
The time 00:06:59
will compare after 00:06:49
but before 000689
.
精彩评论