如何在一段时间后删除 MySQL 记录

2021-11-20 00:00:00 mysql

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

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

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

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.

相关文章