一定时间后如何删除MySQL记录 [英] How to delete a MySQL record after a certain time

问题描述

我想在7天后从我的MySQL数据库中删除一些消息.

I want to delete some messages from my MySQL database after 7 days.

我的消息表行具有以下格式: id |留言|日期

My message table rows have this format: id | message | date

日期是标准格式的时间戳； 2012-12-29 17:14:53

The date is a timestamp in the normal format; 2012-12-29 17:14:53

我当时以为MySQL事件将是一种替代cron的方式.

I was thinking that an MySQL event would be the way to go instead of a cron job.

对于一个有经验的SQL人员，我想这是一个简单的问题，如何在下面的括号中编写删除消息部分的代码?

I have what I guess is a simple question to an experienced SQL person, how do I code the delete messages portion in brackets below?

一个例子，将不胜感激，谢谢.

An example would be appreciated, Thanks.

 DELIMITER $$
   CREATE EVENT delete_event
   ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
   ON COMPLETION PRESERVE
  DO
    BEGIN
      DELETE messages WHERE date >= (the current date - 7 days);
    END;
$$;

推荐答案

您可以尝试使用以下条件:

You can try using this condition:

WHERE date < DATE_SUB(NOW(), INTERVAL 7 DAY)

因此整个SQL脚本如下所示:

So that the whole SQL script looks like this:

CREATE EVENT delete_event
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
ON COMPLETION PRESERVE

DO BEGIN
      DELETE messages WHERE date < DATE_SUB(NOW(), INTERVAL 7 DAY);
END;

但是，在您的位置，我将使用简单的cron脚本解决给定的问题.这样做的原因很简单:维护代码更容易，没有难看的SQL解决方法，可以与您的系统顺利集成.

However, on your place I would solve the given problem with a simple cron script. The reasons to do this is simple: it's easier to maintain the code, no ugly SQL workarounds, integrates smoothly with your system.

这篇关于一定时间后如何删除MySQL记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！