Select updated rows in mysql

Is there simple way to select updated rows?

I’m trying to store timestamp each time I am read row to be able to delete data that was not readed for a long time.

First I tried execute SELECT query first and even found little bit slow but simple solution like

 UPDATE foo AS t, (SELECT id FROM foo WHERE statement=1)q
 SET t.time=NOW() WHERE t.id=q.id

but I still want to find a normal way to do this.

I also think that updating time first and then just select updated rows should be much easier, but I didn’t find anything even for this

Answer

Declare the time column as follows:

CREATE TABLE foo (
    ...
    time TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(),
    ...)

Then whenever a row is updated, the column will be updated automatically.

UPDATE:

I don’t think there’s a way to update automatically during SELECT, so you have to do it in two steps:

UPDATE foo
SET time = NOW()
WHERE <conditions>;

SELECT <columns>
FROM foo
WHERE <conditions>;

As long as doesn’t include the time column I think this should work. For maximum safety you’ll need to use a transaction to prevent other queries from interfering.