Mysql监控时间变化实现实时更新表数据
# 查看event是否开启 OFF:未开启；ON:开启
SHOW VARIABLES LIKE '%event_scheduler%';# 开启event
SET GLOBAL event_scheduler = 1;#创建一个存储过程
CREATE PROCEDURE procedure_name() BEGIN END;#创建一个事件任务
CREATE EVENT event_name BEGIN END;# 定时任务关键字
周期执行–关键字 EVERY；
单位second,minute,hour,day,week(周),quarter(季度),month(月),year
# 举例，每隔一秒执行一次
ON SCHEDULE EVERY 1 SECOND DO sql 语句# 查看存储过程
show procedure status ;# 查看事件
show events ;# 创建存储过程,用于更新表数据
DROP PROCEDURE IF EXISTS UPDATE_CHANNEL_TRANSFER;CREATE PROCEDURE UPDATE_CHANNEL_TRANSFER()COMMENT '超出审核时间则更新表数据'BEGINIF EXISTS(SELECT B.IDFROM DC_WAREHOUSE_CHANNEL_TRANSFER BWHERE ROUND(TIME_TO_SEC(TIMEDIFF(NOW(), B.APPLY_TIME)) / 60)- (SELECT CAST(T.AUDIT_TIME AS DECIMAL(3, 1)) * 60 FROM DC_WAREHOUSE_CHANNEL_TRANSFER_TIMER T) >0)THENUPDATE DC_WAREHOUSE_CHANNEL_TRANSFER XSET X.STATUS = '2',X.cancle_time = NOW(),X.remark='超时,系统自动拒绝'WHERE X.ID IN (SELECT Y.IDFROM (SELECT B.IDFROM DC_WAREHOUSE_CHANNEL_TRANSFER BWHERE ROUND(TIME_TO_SEC(TIMEDIFF(NOW(), B.APPLY_TIME)) / 60)- (SELECT CAST(T.AUDIT_TIME AS DECIMAL(3, 1)) * 60FROM DC_WAREHOUSE_CHANNEL_TRANSFER_TIMER T) > 0) AS Y);END IF;END;
# 创建事件调度,每隔1s调用一次存储过程 UPDATE_CHANNEL_TRANSFER
DROP EVENT IF EXISTS EVENT_REMIND_STATUS;CREATE EVENT EVENT_REMIND_STATUSON SCHEDULE EVERY 1 SECOND DOBEGINCALL UPDATE_CHANNEL_TRANSFER();END
#启动定时器
ALTER EVENT event_remind_status ON COMPLETION PRESERVE ENABLE;#关闭定时器
ALTER EVENT event_remind_status ON COMPLETION PRESERVE DISABLE ;