mysql 存储过程与事件

现在有个需求，每隔一小时，改变一下数据库中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;