mysql定时任务

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`;