mysql存储过程定时删除数据

drop PROCEDURE if EXISTS `sp_delete_tb_push_message_data`;

delimiter ;;

CREATE  PROCEDURE `sp_delete_tb_push_message_data`()

BEGIN

DECLARE v_id int;

DECLARE stopflag int;

DECLARE v_num int;

DECLARE cursor_id cursor for select id from tb_push_message_data  where modify_time < now() - interval 7 day;

DECLARE continue handler for not found set stopflag = 1;

open cursor_id;

repeat

fetch cursor_id into v_id;

begin

delete from tb_push_message_data  where id = v_id;

end;

set v_num = v_num + 1;

if mod(v_num,5000) = 0 then

commit;

end if;

until stopflag = 1

end repeat;

close cursor_id;

END ;;-- 结束存储过程

delimiter ;

-- 定时任务每天调用一次

drop event if EXISTS event_delete_tb_push_message_data;

create event event_delete_tb_push_message_data on SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 2 HOUR)

on COMPLETION PRESERVE ENABLE

do call sp_delete_tb_push_message_data();