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();
mysql存储过程定时删除数据
最新推荐文章于 2024-06-14 15:18:15 发布