4

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

moses toh
  • 12,344
  • 71
  • 243
  • 443

3 Answers3

8

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

Community
  • 1
  • 1
1000111
  • 13,169
  • 2
  • 28
  • 37
  • Thank you very much. Whether it is working on localhost? – moses toh Mar 23 '16 at 14:50
  • You are welcome. If you want to execute this event in localhost mysql server then do the above job in localhost mysql server. And the same holds for the remote mysql server. – 1000111 Mar 23 '16 at 15:56
  • I need you help. Look here : http://stackoverflow.com/questions/38175735/how-to-get-data-in-function-extend-controller – moses toh Jul 04 '16 at 02:24
3

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.

Zafar Malik
  • 6,734
  • 2
  • 19
  • 30
  • I need you help. Look here : http://stackoverflow.com/questions/38175735/how-to-get-data-in-function-extend-controller – moses toh Jul 04 '16 at 02:22
2

You can use a Cronjob to delete all records in Table A every 2 minutes. Assign your PHP script (with delete function) to a Cronjob on your webserver.

Cornest
  • 269
  • 1
  • 15