一. 查看event_scheduler是否开启
1) SHOW VARIABLES LIKE 'event_scheduler';
2) SELECT @@event_scheduler;
3) SHOW PROCESSLIST;
二. 开启event_scheduler
1) SET GLOBAL event_scheduler = 1;
2) SET GLOBAL event_scheduler = ON;
3) SET @@global.event_scheduler = 1;
4) SET @@global.event_scheduler = ON;
三. event(事件)和procedure(存储过程)配合使用
查看和创建存储过程需要用户具有create routine权限,调用存储过程执行时需要使用excute权限,存储过程调用具体的SQL语句时,需要用户具有执行该SQL的权限。
查看用户权限:SELECT HOST,USER,Event_priv FROM mysql.user;
查看当前登陆的用户和数据库:SELECT CURRENT_USER(), SCHEMA();
下面实例操作
#1. 定义存储过程
DELIMITER $$
DROP PROCEDURE IF EXISTS update_workYear$$
CREATE PROCEDURE update_workYear()
BEGIN
UPDATE sys_user SET work_year = concat (
CONCAT(TIMESTAMPDIFF(YEAR,entry_date,CURDATE()),"年") ,
CONCAT(MOD(TIMESTAMPDIFF(MONTH,entry_date,CURDATE()),12),"月") );
END $$
DELIMITER;
#2. 设置定时事件,每间隔1 MONTH 调用一次存储过程update_workYear();
DELIMITER $$
DROP EVENT IF EXISTS event_update_workYear $$
CREATE EVENT event_update_workYear
ON SCHEDULE EVERY 1 MONTH
ON COMPLETION PRESERVE
DO BEGIN
CALL update_workYear();
END $$
DELIMITER;
#3. 启动定时器
ALTER EVENT event_update_workYear ON COMPLETION PRESERVE ENABLE;