DROP PROCEDURE IF EXISTS `clear_event_demo_pro`;
DELIMITER ;;
CREATE PROCEDURE `clear_event_demo_pro`(
IN compare_date datetime, -- 要删除的日期
IN delete_limit int -- 每次删除的条数
)
COMMENT '根据时间来删除event_demo'
BEGIN
SET @max_loop = 70; -- 最多循环50次
SET @loop_times = 1; -- 当前循环次数
-- 要删除记录的时间
-- SET @compareDate = DATE_ADD(NOW(), INTERVAL - 2 MONTH);
SET @recCount = 0; -- 当前记录数
SELECT COUNT(*) INTO @recCount
FROM event_demo
WHERE
`time` < compare_date;
-- 循环的删除记录
WHILE ( @loop_times < @max_loop AND @recCount>0 ) DO
set @loop_times =@loop_times+1;
DELETE
FROM
event_demo
WHERE
`time` < compare_date
LIMIT delete_limit;
SET @recCount=@recCount-delete_limit;
commit;
END WHILE;
END
;;
DELIMITER ;