CODEWITHSUNDEEP
mysql
paul0080
paul0080

Reputation: 169

Mysql: Deleting all rows older than 5 year in every 10 seconds

I want to delete the rows older than 5 year in mysql table(item).

item
----------------
id       neme         date_and_time
1          a          2015-04-11 11:10:04
2          b          2005-05-22 11:10:04
3          c          2007-06-21 11:10:04

How can i doing this delete action?

Also I want to run this delete action in every 10 seconds, I can do this using event scheduler and trigger? What is the different for this two function?

Upvotes: 1

Views: 788

Answers (2)

Rahul Tripathi
Rahul Tripathi

Reputation: 172548

Try to create an event like this:

CREATE EVENT DO_DELETE_OP
ON SCHEDULE
EVERY 10 SECOND
DO DELETE FROM item WHERE date_and_time < DATE_SUB(NOW(), INTERVAL 60 MONTH);

Upvotes: 3

pala_
pala_

Reputation: 9010

You can use the mysql scheduler.

https://dev.mysql.com/doc/refman/5.1/en/events.html.

Something like:

CREATE EVENT cleanup
    ON SCHEDULE
      EVERY 10 SECOND
    DO
      DELETE FROM item WHERE date_and_time < NOW() - INTERVAL 5 YEAR;

In order for this to work, you will need to enable the mysql scheduler, which can be done like this:

SET GLOBAL event_scheduler = ON;

Upvotes: 2

Related Questions