删除 mysql 数据库中超过 3 个月的行的作业
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/813434/
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
a job to delete rows older than 3 months in mysql database
提问by Jeffrey Berthiaume
We use mysql server as a centralized logging system and I want to have a job to delete\clean the table entries that are more than 3 months old regularly. What is the best way to do this?
我们使用 mysql 服务器作为集中式日志系统，我想定期删除\清理超过 3 个月的表条目。做这个的最好方式是什么？
Thanks in advance.
提前致谢。
-hinling
-hinling
回答by Jeffrey Berthiaume
Do you store the date an item is created in a field?
您是否将项目的创建日期存储在字段中？
If so,
如果是这样的话，
DELETE FROM myTable WHERE dateEntered < DATE_SUB(NOW(), INTERVAL 3 MONTH);
should work...
应该管用...
You could run it in a scheduled task/cron job...
您可以在计划任务/ cron 作业中运行它...
回答by Emil H
回答by E.J. Brennan
A simple way would be to scheduled a job that runs every night that calls a stored procedure to delete all rows older than three months. Depending on your O/S it should be easy to do. Do it each night and the amount of deleted data won't be as much as doing it once a month or so.
一种简单的方法是安排一个每晚运行的作业，该作业调用存储过程来删除超过三个月的所有行。根据您的操作系统，它应该很容易做到。每天晚上做一次，删除的数据量不会像一个月左右做一次那样多。
I have also in the past, had code in my SP that inserted data to do the deletes of unneeded data at that time, so basically every time a row is inserted, right after the insert in ran a 'cleanup' proc that did a bit of maintenance. This wouldn't be my first choice, but its doable if the number of inserts is low, and the deletes can be done fast (you don't want to slow the user down). Nice side-effect of this is the data is alwaysclean (i.e. all data is always <= 3 months old), but not sure if that matters in your app.
我过去也有过，在我的 SP 中有代码插入数据来删除不需要的数据，所以基本上每次插入一行时，在插入后立即运行一个“清理”过程，它做了一点的维护。这不是我的第一选择，但如果插入次数很少，它是可行的，并且可以快速完成删除（您不想减慢用户的速度）。这样做的好处是数据总是干净的（即所有数据总是 <= 3 个月前的），但不确定这在您的应用程序中是否重要。
You should also consider archiving the rows to another table instead in addition to deleting them, if there is anychance you might want the data someday for another purpose. I alwaysdo this, and you'd be surprised what a hero you can look like when some manager who doesn't even know you are saving the data, suddenly has an urgent need for it....and you can deliver for them.
你也应该考虑归档行到另一个表中，而不是除了删除它们，如果有任何你可能需要用于其他目的的数据有一天机会。我总是这样做，当一些甚至不知道您正在保存数据的经理突然迫切需要它时，您会惊讶自己看起来像个英雄……而您可以为他们提供.
回答by tpdi
Paraphrasing your question, "I have code that doesn't depend on the client, which I want to run on the database multiple times, and I don't need to see output", then the answer is "write a stored proc, and call it from whatever client on a regular basis".
解释你的问题，“我有不依赖于客户端的代码，我想在数据库上多次运行，我不需要看到输出”，那么答案是“写一个存储过程，然后定期从任何客户端调用它”。
