存储过程的创建及定时使用

存储过程的创建及定时器的使用

//1.开启event_scheduler sql指令
SHOW VARIABLES LIKE '%event_scheduler%';

SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@global.event_scheduler = 1;


//2.定义存储过程
DELIMITER |
DROP PROCEDURE IF EXISTS update_task_send_status | 
//创建名字为update_task_send_status
CREATE PROCEDURE update_task_send_status() 
//开始执行sql
BEGIN
			//根据状态和结束时间小于当前时间为条件修改
			UPDATE t_task_send SET send_status='3'
			WHERE id IN (SELECT id FROM ( SELECT * FROM t_task_send WHERE send_status='2' AND SYSDATE()>=end_time) AS c);
	
END;
 |
DELIMITER; 

//3.创建定时器，每间隔60秒调用一次存储过程。其中//符号相当于sql中的;结束符
DELIMITER //
CREATE EVENT  event_task_send_status
ON SCHEDULE EVERY 1 SECOND  DO
BEGIN
CALL update_task_send_status();
END //

//4.启动定时器
ALTER EVENT event_task_send_status ON  
COMPLETION PRESERVE ENABLE;