每1个月重新生成一张LOG表,保留前1个月的LOG表,删除前2个月的LOG表。
DELIMITER $$ DROP PROCEDURE IF EXISTS `Delete_two_month_before`$$ CREATE DEFINER=`admin`@`%` PROCEDURE `Delete_two_month_before`() BEGIN -- rename SELECT DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 1 MONTH),'%Y%m') INTO @mtime; SET @str1="RENAME table RUS_Log to RUS_Log_"; SET @cmd1=CONCAT(@str1,@mtime); PREPARE stmt1 FROM @cmd1; EXECUTE stmt1; DEALLOCATE PREPARE stmt1; -- create SET @str2="create table RUS_Log like RUS_Log_"; SET @cmd2=CONCAT(@str2,@mtime); PREPARE stmt2 FROM @cmd2; EXECUTE stmt2; DEALLOCATE PREPARE stmt2; -- delete_two_month_before SELECT DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 2 MONTH),'%Y%m') INTO @two_month; SET @str3="drop table RUS_Log_"; SET @cmd3=CONCAT(@str3,@two_month); PREPARE stmt3 FROM @cmd3; EXECUTE stmt3; DEALLOCATE PREPARE stmt3; END$$ DELIMITER ; |
DELIMITER $$ CREATE EVENT `RUSDB`.`Rus_Log_delete_two_month_before` ON SCHEDULE EVERY 1 MONTH STARTS '2012-09-01 00:00:00' ON COMPLETION PRESERVE ENABLE DO BEGIN CALL Delete_two_month_before(); END$$ DELIMITER ; |