一組SQL集,用來執行定時任務,跟觸發器很像,都是被動執行的,事件是因為時間到了觸發執行,而觸發器是因為某件事件(增刪改)觸發執行;
查看是否開啟:
show variables like 'event_scheduler';
如果顯示OFF,則輸入以下語句開啟:
set global event_scheduler = off;
新建user表:
-- ---------------------------- -- Table structure for `user` -- ---------------------------- DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(200) NOT NULL, `address` varchar(500) NOT NULL, `addtime` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
新建一個時間,每隔一分鐘插入一條數據:
參數說明:
DEFINER:創建者;
ON COMPLETION [NOT] PRESERVE :表示當時間不會發生後,刪除事件(注意特定時間的事件,如果設置了該參數,執行完畢後,事件將被刪除,不想刪除的話可以設置成ON COMPLETION PRESERVE);
ENABLE:表示系統將執行這個事件;
-- ---------------------------- -- Event structure for `event_minute` -- ---------------------------- DROP EVENT IF EXISTS `event_minute`; DELIMITER ;; CREATE DEFINER=`root`@`localhost` EVENT `event_minute` ON SCHEDULE EVERY 1 MINUTE STARTS '2016-01-17 14:49:43' ON COMPLETION NOT PRESERVE ENABLE DO
BEGIN INSERT INTO USER(name, address,addtime) VALUES('test1','test1',now()); INSERT INTO USER(name, address,addtime) VALUES('test2','test2',now()); END ;; DELIMITER ;
新建一個事件,特定時間2016-01-17 15:30:00插入一條數據
-- ---------------------------- -- Event structure for `event_at` -- ---------------------------- DROP EVENT IF EXISTS `event_at`; DELIMITER ;; CREATE DEFINER=`root`@`localhost` EVENT `event_at` ON SCHEDULE AT '2016-01-17 15:30:00' ON COMPLETION NOT PRESERVE ENABLE DO
BEGIN INSERT INTO USER(name, address,addtime) VALUES('AT','AT',now()); END ;; DELIMITER ;
查看結果,結果正確: