Automatically delete a record from a table after a time interval

Asked

Viewed 770 times

1

How do I after a time interval the record is automatically deleted, as in a shopping cart, for example?

mysql sql

  • You will have to schedule tasks on your server where Mysql is installed. But this is not a function of Mysql but an Operating System function.

  • You can use linux cronjobs. How to schedule a recurring task on linux?

  • 1

    You can do it with Mysql yes, you can schedule an event and run an instruction, follow link: https://dev.mysql.com/doc/refman/5.7/en/event-scheduler.html

  • @arllondias is just what I need. Thank you very much.

1 answer

1


I use Scheduled Tasks with the CREATE EVENT command from Mysql itself. Unofficial reference from Event Scheduler here.

Example of the above reference:

DELIMITER $$

CREATE 
    EVENT `archive_blogs` 
    ON SCHEDULE EVERY 1 WEEK STARTS '2011-07-24 03:00:00' 
    DO BEGIN

    -- copy deleted posts
    INSERT INTO blog_archive (id, title, content) 
    SELECT id, title, content
    FROM blog
    WHERE deleted = 1;

    -- copy associated audit records
    INSERT INTO audit_archive (id, blog_id, changetype, changetime) 
    SELECT audit.id, audit.blog_id, audit.changetype, audit.changetime 
    FROM audit
    JOIN blog ON audit.blog_id = blog.id
    WHERE blog.deleted = 1;

    -- remove deleted blogs and audit entries
    DELETE FROM blog WHERE deleted = 1;

END */$$

DELIMITER ;

Note: the feature needs to be enabled through the command SET GLOBAL event_scheduler = ON;

Official documentation for Mysql: https://dev.mysql.com/doc/refman/5.7/en/create-event.html

  • They had already mentioned the Event Schedule and was of great help, his example helped even more. Much fought.

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.