1.修改配置文件my.conf 或者my.ini(永久配置)
增加
event_scheduler=ON
执行SHOW VARIABLES LIKE 'event_scheduler';查看
2.创建定时任务
CREATE EVENT [IFNOT EXISTS] event_name
ONSCHEDULE schedule
[ONCOMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT 'comment']
DO sql_statement;
简单列子:
create event e_test_0925
on schedule every 1 second
on completion preserve enable
do insert into test value('1');
3.案例
-- 修改设备心跳状态执行SQL脚本
-- USE ecologictest;
USE ecologic;
-- 开启event
SET GLOBAL event_scheduler='ON';
SHOW VARIABLES LIKE 'event_scheduler';
-- 更新心跳状态存储过程
DELIMITER
DROP PROCEDURE IF EXISTS update_device_heart_state_proce;
CREATE PROCEDURE update_device_heart_state_proce() -- 更新设备心跳状态存储过程
BEGIN
DECLARE t_error INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;
START TRANSACTION; -- 开启事务
-- 定时脚本需要执行的SQL
UPDATE info_device SET heart_state=2 WHERE heart=1 AND TIMESTAMPDIFF(MINUTE,heart_last_time,NOW())>15;
UPDATE info_device SET heart_state=3 WHERE heart=1 AND TIMESTAMPDIFF(MINUTE,heart_last_time,NOW())>30;
IF t_error=1 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
SELECT t_errot; -- 返回标识位的结果集
END;
DELIMITER;
-- 创建调用存储过程事件
DROP EVENT IF EXISTS update_device_heart_state_event;
CREATE EVENT update_device_heart_state_event
ON SCHEDULE EVERY 60 SECOND
ON COMPLETION PRESERVE DISABLE
DO CALL update_device_heart_state_proce();
-- 开启事件
ALTER EVENT update_device_heart_state_event ON COMPLETION PRESERVE ENABLE;
-- 关闭事件
-- ALTER EVENT update_device_heart_state_event ON COMPLETION PRESERVE DISABLE;
-- 查看自己创建的event
-- SELECT * FROM mysql.`event`;