CODEWITHSUNDEEP
mysql
Mike
Mike

Reputation: 415

How to delete a MySQL record after a certain time

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

My message table rows have this format: id | message | date

The date is a timestamp in the normal format; 2012-12-29 17:14:53

I was thinking that an MySQL event would be the way to go instead of a cron job.

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;
$$;

Upvotes: 25

Views: 65008

Answers (3)

Michael Mior
Michael Mior

Reputation: 28753

This should do the trick.

DELETE FROM messages WHERE date < (CURDATE() - INTERVAL 7 DAY);

Upvotes: 10

Vic Seedoubleyew
Vic Seedoubleyew

Reputation: 10526

For those out there who are on a shared hosting, like 1and1's, and can't create events, an alternative is to use 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

Upvotes: 2

Jordan Jambazov
Jordan Jambazov

Reputation: 3620

You can try using this condition:

WHERE date < DATE_SUB(NOW(), INTERVAL 7 DAY)

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;

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.

Upvotes: 28

Related Questions