在mysql中使用时间戳条件将数据插入一个表中，从另一个表中删除

Question

In short

Is there a safe way to move database entries - based on a timestamp condition - to another table (without first querying the time)? So that it is ensured, INSERT INTO works on the exact same entries as DELETE?

Background to the question

• This question is not about the importance of using transactions, that is a given! But rather it is about the problem that the time (eg. the value of NOW()) might be different for the two subsequent queries.
• Since a programming framework is used, which does (for safety reasons) not allow multiple queries being executed in a single call, this really becomes an issue

Example table structure

Source
---------------------------------------------
ID        |  Timeout              |  Data
---------------------------------------------
1         |  2014-12-31 12:00:00  |  foo
2         |  2014-12-31 15:00:00  |  bar
3         |  2014-12-31 18:00:00  |  foobar

Archive
---------------------------------------------
ID        |  Data
---------------------------------------------

Demonstration (simplified)

<?php

    beginTransaction();
    try {
        // Imagine this happens at 2014-12-31 14:59:59(.992) [Entry #2 not copied]
        execute( "INSERT INTO Archive (ID, Data) 
                      SELECT ID, Data FROM Source
                      WHERE Timeout <= NOW()" );

        // While here it might be already 2014-12-31 15:00:00(.004) [Entry #2 deleted]
        execute("DELTE FROM Source WHERE Timeout <= NOW()");
        commit();
    }
    catch (Exception $e) {
        rollBack();
        return false;
    }

    return;
?>

So is there a safe way to evade this problem, preferably with performance in mind (I know, in this case php might be the wrong language in the first place ^^). Help much appreciated!

Answer 1

execute("SET @now = NOW()");

execute("
    INSERT INTO Archive (ID, Data) 
    SELECT ID, Data 
    FROM Source
    WHERE Timeout <= @now
");

execute("DELTE FROM Source WHERE Timeout <= @now");

OR

execute("
    INSERT INTO Archive (ID, Data) 
    SELECT ID, Data 
    FROM Source
    WHERE Timeout <= NOW()
");

execute("DELTE FROM Source WHERE Timeout <= (SELECT MAX(Timeout) FROM Archive)");