使用mysql的存储过程定时删除日志文件

USE `test`;
DROP procedure IF EXISTS `p_delete_operate_log`;

DELIMITER $$
USE `test`$$
CREATE PROCEDURE `p_delete_operate_log` ()
BEGIN
declare rn int default 0;
select count(*) into rn from `operate_log` where systemTime < DATE_ADD(CURDATE(), INTERVAL -1 MONTH);
while (rn > 0) do
delete from operate_log where systemTime < date_sub(now(),interval 1 month);
set rn = rn -1000;
end while;
END$$

#每天凌晨1点开始执行
CREATE EVENT IF NOT EXISTS `e_delete_operate_log`
ON SCHEDULE EVERY 1 DAY
    STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR)
DO call new_procedure()$$

DELIMITER ;