mysql 存储过程与事件

现在有个需求，每隔一小时，改变一下数据库中userinfo的记录

实现步骤

1 创建存储过程 （有入参 （IN P_a int））

create procedure  pro_del_userinfoBEGIN        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;