How to create a SQL Agent Job for MySQL for scheduling the Database Task Using Event Scheduler
To enable or disable the execution of scheduled events, it is necessary to set the value of the global event_scheduler system variable:
GRANT EVENT ON myschema.mytable TO 'root'@'%';
To rescind the EVENT privilege, use the REVOKE statement. In this example, the EVENT privilege on the table mytable of the schema myschema is removed from the 'root'@'%' user account:
REVOKE EVENT ON myschema.mytable FROM 'root'@'%';
Create an event to insert in mytable at every 7 minutes:
CREATE EVENT `insert_event`
ON SCHEDULE
EVERY 7 MINUTE
DO
INSERT INTO `myschema`.`mytable`
VALUES (1, 'hiroshi');
You can alter/disable/drop the created events:
ALTER EVENT `insert_event` ON SCHEDULE EVERY 2 MINUTE;
ALTER EVENT `insert_event` DISABLE;
DROP EVENT `insert_event`;
You can also list all created events:
SELECT * FROM INFORMATION_SCHEMA.EVENTS;
If you want to schedule an event at a specific time every day:
CREATE EVENT IF NOT EXISTS `insert_event_fixed`
ON SCHEDULE
EVERY 1 DAY
STARTS (CURRENT_DATE + INTERVAL 1 DAY + INTERVAL 1 HOUR)
ON COMPLETION PRESERVE
COMMENT 'Insert at 01:00 daily!'
DO
INSERT INTO `myschema`.`mytable` VALUES (1, 'hiroshi');
评论
登录后参与技术讨论,分享你的经验与见解。
登录 / 注册暂无评论