一个删除mysql数据库中超过3个月的记录的工作[英] a job to delete rows older than 3 months in mysql database

本文是小编为大家收集整理的关于一个删除mysql数据库中超过3个月的记录的工作的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我们将MySQL Server用作集中伐木系统,我想拥有一份工作来删除\固定超过3个月大的表条目.什么是最好的方法?

预先感谢.

hinling

推荐答案

您是否存储了在字段中创建的日期?

如果是这样,

DELETE FROM myTable WHERE dateEntered < DATE_SUB(NOW(), INTERVAL 3 MONTH);

应该工作...

您可以在计划的任务/cron作业中运行它...

其他推荐答案

mysql 5.1支持 evest .我实际上没有自己使用它们,所以我不会为他们保证.但是,如果您要做的就是定期运行删除语句,我认为它可以很好地工作.

其他推荐答案

一种简单的方法是安排每天晚上运行的作业,该作业调用存储过程以删除超过三个月的所有行.根据您的O/S,应该很容易做到.每天晚上这样做,删除的数据量不会像每月一次左右一样多.

我过去也有SP中的代码,该代码插入了当时的数据以执行不需要的数据的删除,因此基本上每次插入一排时,在插入"清理" proc之后,进行了一些维护.这不是我的首选,但是如果插入数量较低,并且可以快速完成删除(您不想减慢用户).很棒的副作用是数据是始终清洁(即所有数据始终<= 3个月大),但不确定在您的应用中是否重要.

您还应该考虑将行归档到另一个表中,而不是删除它们,如果有任何 的机会,您可能有一天可能希望这些数据出于另一个目的.我总是 这样做,当某些人甚至不知道自己保存数据时,您会感到惊讶的是,您的英雄会是什么样,突然突然有迫切需要它...您可以为他们提供交付.

本文地址:https://www.itbaoku.cn/post/597552.html

问题描述

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?

Thanks in advance.

-hinling

推荐答案

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

其他推荐答案

MySQL 5.1 supports Events. I haven't actually used them myself, so I won't vouch for them. However, if all you want to do is to run a DELETE statement on a regular basis, I think that it would work well enough.

其他推荐答案

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 always clean (i.e. all data is always <= 3 months old), but not sure if that matters in your app.

You should also consider archiving the rows to another table instead in addition to deleting them, if there is any chance you might want the data someday for another purpose. I always do 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.