mysql按月归档日志表

最新推荐文章于 2024-07-31 00:00:52 发布

遗失的曾经！

最新推荐文章于 2024-07-31 00:00:52 发布

阅读量2k

点赞数 1

分类专栏： mysql 文章标签： mysql 归档

本文链接：https://blog.csdn.net/u014609263/article/details/89348903

版权

mysql 专栏收录该内容

60 篇文章 1 订阅

订阅专栏

由于日志表越来越大，数据将近1亿，查数据非常慢，因此使用事件每月28日调用存储过程自动创建归档表，将上月数据按照create_time字段和id字段插入归档表并删除原表相应数据。

DELIMITER ;;
    CREATE DEFINER=`root`@`localhost` PROCEDURE `clear_log`()
    BEGIN
    		#获取当上月的表名
    		SET @currentym =left(DATE_SUB(CURRENT_DATE,INTERVAL 1 MONTH),7);
    		SET @logTable=CONCAT('log_',left(@currentym,4),right(@currentym,2));
    		#判断是否需要建表，没有则创建
    		SET @STMT :=CONCAT("CREATE TABLE IF NOT EXISTS ",@logTable," LIKE log;");
    		PREPARE STMT FROM @STMT;   
    		EXECUTE STMT;
    		#日志迁移
    		SET @dateOne=concat(left(DATE_SUB(CURRENT_DATE,INTERVAL 1 MONTH),7),"-01");
        SET @datemonthone=concat(left(CURDATE(),8),"01");
    		SET @STMT :=CONCAT("insert into ",@logTable," select * from log WHERE create_time>'",@dateOne,"' and create_time<'",@datemonthone,"' order by id;");
    		PREPARE STMT FROM @STMT;   
    		EXECUTE STMT;
    		#删除log表中对应数据
    		delete from log WHERE create_time>@dateOne and create_time<@datemonthone order by id;
    END
    ;;
    DELIMITER ;


DELIMITER ;;
CREATE DEFINER=`root`@`localhost` EVENT `schedule_clear_log` ON SCHEDULE EVERY 1 MONTH STARTS '2019-04-28 22:30:00' ON COMPLETION NOT PRESERVE ENABLE DO CALL clear_log()
;;
DELIMITER ;