MYSQL中定时事件计划

1.     写procedure

BEGIN

#Routine body goes here...

insertinto alarm_record

(rule_did,BatteryBarCode,craft_did,process_did,facility_did,unit_did,alarm_time,alarm_count,dispose_state,dispose_time,handler,duration,address,remark)

selectrule_did,BatteryBarCode,craft_did,process_did,facility_did,unit_did,alarm_time,alarm_count,dispose_state,dispose_time,handler,duration,address,remark

fromalarm_temporary where dispose_state='2';

deletefrom alarm_temporary where dispose_state='2';

END

2.     创建事件

CREATE DEFINER=`root`@`%` EVENT `eventeveryminutejob` ONSCHEDULE EVERY 1 MINUTE STARTS '2017-04-25 17:07:53' ON COMPLETION PRESERVEENABLE DO call 把已处理的报警转移到历史报警里()

 

3.     开启sql定时器

set global event_scheduler=1

SET GLOBAL event_scheduler = 0; -- 停止定时器

 

4.     开启事件 

alter eventeventeveryminutejob on COMPLETION preserve enable

ALTER EVENT eventeveryminutejob ON COMPLETION PRESERVEDISABLE; -- 关闭事件

 

SHOW VARIABLES LIKE '%sche%'; -- 查看定时器状态