在mysql中选择更新的行 [英] Select updated rows in mysql

问题描述

有简单的方法来选择更新的行吗？

Is there simple way to select updated rows?

我试图存储时间戳每次ia读取行，以便能够删除未读取的数据很长时间。

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

首先我尝试执行 SELECT 查询，甚至发现有点慢， / p>

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

推荐答案

声明时间列如下：

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

然后每当更新一行时，该列都会自动更新。

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

UPDATE：

我不认为有一种方法在SELECT期间自动更新，所以你必须在两个步骤：

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>;

只要不包括时间 column我认为这应该工作。为了最大程度的安全，您需要使用交易来防止其他查询产生干扰。

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.

这篇关于在mysql中选择更新的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！