如何在一段时间后删除 MySQL 记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14096429/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to delete a MySQL record after a certain time
提问by Mike
I want to delete some messages from my MySQL database after 7 days.
我想在 7 天后从我的 MySQL 数据库中删除一些消息。
My message table rows have this format: id | message | date
我的消息表行具有以下格式： id | 留言 | 日期
The date is a timestamp in the normal format; 2012-12-29 17:14:53
日期是正常格式的时间戳；2012-12-29 17:14:53
I was thinking that an MySQL event would be the way to go instead of a cron job.
我在想 MySQL 事件将是一种方法，而不是一个 cron 工作。
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?
我想对有经验的 SQL 人员来说是一个简单的问题，我该如何编码下面括号中的删除消息部分？
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;
$$;
回答by Jordan Jambazov
You can try using this condition:
您可以尝试使用此条件：
WHERE date < DATE_SUB(NOW(), INTERVAL 7 DAY)
So that the whole SQL script looks like this:
这样整个 SQL 脚本看起来像这样：
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;
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.
但是，在您那里，我会用一个简单的 cron 脚本解决给定的问题。这样做的原因很简单：维护代码更容易，没有丑陋的 SQL 解决方法，与您的系统顺利集成。
回答by Michael Mior
This should do the trick.
这应该可以解决问题。
DELETE FROM messages WHERE date < (CURDATE() - INTERVAL 7 DAY);
回答by Vic Seedoubleyew
For those out there who are on a shared hosting, like 1and1's, and can't create events, an alternative is to use webcron
对于那些使用共享主机（例如 1and1）并且无法创建事件的人，另一种方法是使用webcron
You just need to tell webcron the url of the php script you'd like to be run, and they'll trigger it for you at the intervals you want
你只需要告诉 webcron 你想要运行的 php 脚本的 url，他们会在你想要的时间间隔为你触发它
