How to automatically delete every x minutes? [closed]

Question

I using codeigniter framework

For example, I have table A. I want my system automatically delete all records in table A every 2 minutes

Whether it can be done?

Thank you

Answer 1

You can use MySQL event Scheduler.

---

Prerequisite:

You have to have event_scheduler ON in your mysql server.

Check whether event scheduler is ON or OFF

SELECT @@event_scheduler;

To turn event_scheduler ON run the following query:

SET GLOBAL event_scheduler = ON;

---

Note: If you restart MYSQL Server then event scheduler status will be reset unless the following is written in the configuration file.

For Windows: in my.ini file write this under [mysqld] section

[mysqld]
event_scheduler=on

For Linux: in my.cnf file

[mysqld]
event_scheduler=on

---

Event:

The following event will delete data from table named tablename.

CREATE 
EVENT `deleteEvent`
ON SCHEDULE EVERY 2 MINUTE STARTS '2016-03-23 00:00:00'
ON COMPLETION NOT PRESERVE
ENABLE
DO
DELETE FROM tablename;

---

The event will be started for the first time at '2016-03-23 00:00:00' and after that the event will be scheduled in every 2 minutes interval and will delete data from your table.

You can use Truncate instead of DELETE.

TRUNCATE vs DELETE

Answer 2

Best and fast way will be to truncate table instead of delete, so either you can do it by mysql own event scheduler or by cronjob-

By event Scheduler-

DELIMITER $$

ALTER DEFINER=`root`@`localhost` EVENT `trun_table` ON SCHEDULE EVERY 2 MINUTE STARTS '2016-03-23 16:30:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
        truncate table mytable;
    END$$

DELIMITER ;

By cronjob if Mysql running on Linux OR task manager if Mysql is running on Windows machine:

write "truncate table mytable;" syntax in one file and schedule it either from cronjob or task scheduler.

Note: If you don't want to reset your auto_increment id in table then use delete statement instead of truncate as truncate statement will reset it to 1.