Mysql events (scheduled tasks) and mysql event tasks
1. What is an event?
A set of SQL sets used to execute scheduled tasks. Similar to triggers, they are passively executed. Events are triggered when the time is reached, and triggers are executed when an event (add, delete, modify, and delete) is triggered;
Ii. Enable events
Check whether enabled:
show variables like 'event_scheduler';
If OFF is displayed, enter the following statement to enable it:
set global event_scheduler = off;
3. Custom simple events
Create a user table:
-- ------------------------------ 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;
Create a new time and insert a data entry every minute:
Parameter description:
DEFINER: Creator;
On completion [NOT] PRESERVE: indicates that the event will be deleted when the time does NOT occur. (Note that if this parameter is set, the event will be deleted after execution, if you do not want to delete it, you can set it to on completion preserve );
ENABLE: indicates that the system will execute this event;
-- ------------------------------ 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 ;
Create an event and insert a data entry at 15:30:00 on January 17 ,.
-- ------------------------------ 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 ;
Check the result. The result is correct: