一、查询事件状态，mysql查看事件是否开启，设置启动时自动开启方法 1.查看事件是否开启 SHOW VARIABLES LIKE ‘event_scheduler’
2.设置当前事件开启 SET GLOBAL event_scheduler = 1; 或 SET GLOBAL event_scheduler = ON;
3.设置事件在mysql启动时自动开启方法
1. 开启事件，通过动态参数修改：SET GLOBAL event_scheduler = ON; 2. 在my.cnf(Linux系统)或 my.ini(Windows系统)中添加event_scheduler=ON。（如果没有添加的话，mysql重启事件又会回到原来的状态）
-- 删除日记中超过一百万的旧数据 create procedure pro_dellog() BEGIN DECLARE tasklogrownum int DEFAULT 0; DECLARE comlogrownum int DEFAULT 0; select count(*) into tasklogrownum from tab_task_info_log; if tasklogrownum > 1000000 THEN set tasklogrownum = tasklogrownum - 1000000; DELETE FROM tab_task_info_log ORDER BY LogTime LIMIT tasklogrownum; END IF; select count(*) into comlogrownum from communication_log; if comlogrownum > 1000000 THEN set comlogrownum = comlogrownum - 1000000; DELETE FROM communication_log ORDER BY communication_time LIMIT comlogrownum; END IF; END -- 每天凌晨1点开始触发 CREATE EVENT IF NOT EXISTS event_dellog ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY),INTERVAL 1 HOUR) ON COMPLETION PRESERVE ENABLE DO CALL pro_dellog(); -- 每分钟执行 CREATE event IF NOT EXISTS temp_event ON SCHEDULE EVERY 1 MINUTE ON COMPLETION PRESERVE DO CALL pro_dellog(); delimiter // DROP PROCEDURE IF EXISTS `updateOrderStatus` // CREATE PROCEDURE updateOrderStatus() BEGIN DECLARE order_id int DEFAULT 0; DECLARE goods_id int DEFAULT 0; DECLARE done int DEFAULT 0; DECLARE overtime_order CURSOR FOR select id,shop_id from `order` where `status`='等待支付' and `order_time`<=(unix_timestamp(now())-1800); DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; OPEN overtime_order; read_loop:LOOP FETCH overtime_order into order_id,goods_id; if done = 1 THEN leave read_loop; END IF; update `order` set `status` = '未支付',order_end_time=now() where `id`=order_id; update `shop` set `num` = `num`+1 where `id`=goods_id; END LOOP; CLOSE overtime_order; END // delimiter ; create event event_dellog -- //事件名 ON SCHEDULE EVERY 3 hour -- //on schedule代表计划任务 every代表事件间隔 STARTS '2019-11-30 09:00:00' -- //设置事件开始算起的时间 on completion preserve -- //让此事件执行完毕后不被drop ENABLE -- //代表该事件的执行状态，系统会根据此来判断是否执行 do call pro_dellog();