mysql定时任务案例以及教程

SHOW VARIABLES LIKE ‘event_scheduler’;

Value为ON则已打开，OFF则关闭

如果是OFF，就先打开：

SET GLOBAL event_scheduler = ON;

然后创建我们想要的定时器

每天凌晨一点执行（清空表数据）

DELIMITER $$

DROP EVENT IF EXISTS deletefol_bigorders_rfm;

CREATE EVENT deletefol_bigorders_rfm

ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR)

ON COMPLETION PRESERVE

DO BEGIN

delete from fol_bigorders_rfm;

END$$

DELIMITER ;

每分钟执行一次

DELIMITER $$

DROP EVENT IF EXISTS deletefol_bigorders_rfm;

CREATE EVENT deletefol_bigorders_rfm

ON SCHEDULE EVERY 1 MINUTE

ON COMPLETION PRESERVE

DO BEGIN

delete from fol_bigorders_rfm;

END$$

DELIMITER ;

每个小时执行一次

DELIMITER $$

DROP EVENT IF EXISTS deletefol_bigorders_rfm;

CREATE EVENT deletefol_bigorders_rfm

ON SCHEDULE EVERY 1 HOUR

ON COMPLETION PRESERVE

DO BEGIN

delete from fol_bigorders_rfm;

END$$

DELIMITER ;

每隔三天执行一次

DELIMITER $$

DROP EVENT IF EXISTS deletefol_bigorders_rfm;

CREATE EVENT deletefol_bigorders_rfm

ON SCHEDULE EVERY 3 DAY STARTS NOW()

ON COMPLETION PRESERVE

DO BEGIN

delete from fol_bigorders_rfm;

END$$

DELIMITER ;

每个月的五号一点执行一次

DELIMITER $$

DROP EVENT IF EXISTS deletefol_bigorders_rfm;

CREATE EVENT deletefol_bigorders_rfm

ON SCHEDULE EVERY 5 MONTH STARTS DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 1 MONTH),INTERVAL 1 HOUR)

ON COMPLETION PRESERVE

DO BEGIN

delete from fol_bigorders_rfm;

END$$

DELIMITER ;

每个季度三号两点执行一次

DELIMITER $$

DROP EVENT IF EXISTS deletefol_bigorders_rfm;

CREATE EVENT deletefol_bigorders_rfm

ON SCHEDULE EVERY 3 QUARTER STARTS DATE_ADD(DATE_ADD(DATE( CONCAT(YEAR(CURDATE()),'-',ELT(QUARTER(CURDATE()),1,4,7,10),'-',1)),INTERVAL 1 QUARTER),INTERVAL 2 HOUR)

ON COMPLETION PRESERVE

DO BEGIN

delete from fol_bigorders_rfm;

END$$

DELIMITER ;

每年一月一号凌晨三点执行一次

DELIMITER $$

DROP EVENT IF EXISTS deletefol_bigorders_rfm;

CREATE EVENT deletefol_bigorders_rfm

ON SCHEDULE  EVERY 1 YEAR STARTS DATE_ADD(DATE(CONCAT(YEAR(CURDATE()) + 1,'-',1,'-',1)),INTERVAL 3 HOUR)

ON COMPLETION PRESERVE

DO BEGIN

delete from fol_bigorders_rfm;

END$$

DELIMITER ;

创建完成后，查看定时器

select * from  deletefol_bigorders_rfm.event;

关闭定时任务

DROP event deletefol_bigorders_rfm.event;

临时关闭事件

ALTER EVENT deletefol_bigorders_rfm.event DISABLE;

开启事件

ALTER EVENT deletefol_bigorders_rfm.event ENABLE;

删除事件

DROP EVENT [IF EXISTS] deletefol_bigorders_rfm.event;

重命名事件并加上备注

ALTER EVENT deletefol_bigorders_rfm.event RENAME TO deletefol_bigorders_rfm_new COMMENT '重命名事件';

查看事件的创建SQL语句

SHOW CREATE EVENT deletefol_bigorders_rfm.event;