-- 查看存储过程状态
show procedure status;
-- 创建名为resetMsgNum()存储过程
-- 存储过程执行 update sc_user SET msg_num = 0;
create procedure resetMsgNum()
BEGIN
update sc_user SET msg_num = 0;
end;
-- 删除事件eventResetMsgNum
drop event if EXISTS eventResetMsgNum;
-- 查看事件
SHOW EVENTS;
-- 创建mysql的定时器event:
-- create event if not exists eventResetMsgNum
-- on schedule
-- every 20 second
-- on completion PRESERVE
-- do call resetMsgNum();
-- 创建定时器(event) eventResetMsgNum
-- 定时器开始时间 '2019-6-12 23:06:00'
-- 执行频率 1天/次
-- 执行时间 resetMsgNum();
create event eventResetMsgNum
on schedule
EVERY 1 day
STARTS '2019-6-12 23:06:00'
do call resetMsgNum();
-- mysql想利用定时器必须把mysql的定时器给开启了:
SET GLOBAL event_scheduler = 1; -- 启动定时器
SET GLOBAL event_scheduler = 0; -- 停止定时器
-- 还要开启事件:
ALTER EVENT eventResetMsgNum ON COMPLETION PRESERVE ENABLE; -- 开启事件
ALTER EVENT eventResetMsgNum ON COMPLETION PRESERVE DISABLE; -- 关闭事件
SHOW VARIABLES LIKE '%sche%'; -- 查看定时器状态