现在有个需求,每隔一小时,改变一下数据库中userinfo的记录
实现步骤
1 创建存储过程 (有入参 (IN P_a
int))
create procedure pro_del_userinfo
BEGIN
delete from userinfo;
case p_a
when 1 then
INSERT INTO userinfo (
aaa,
bbb
)
VALUES
(111,222);
when 2 then
INSERT INTO userinfo (
aaa,
bbb
)
VALUES
(333,444);
else
INSERT INTO userinfo (
aaa,
bbb
)
VALUES
(555,666);
end case;
END
2 创建事件
create event e_del_userinfo
on schedule
EVERY 3600 second
STARTS '2017-01-01 03:00:00'
ON COMPLETION PRESERVE ENABLE
do call pro_del_userinfo( FLOOR(1 + (RAND() * 10)))
3 开启事件任务并设置事件持续开启
alter event del_event ON COMPLETION PRESERVE ENABLE;
SHOW VARIABLES LIKE 'event_scheduler';
SET GLOBAL event_scheduler = 1;