mysql定时清理数据

最新推荐文章于 2025-03-12 21:30:37 发布

那记忆微凉

最新推荐文章于 2025-03-12 21:30:37 发布

阅读量2.6k

点赞数 1

分类专栏： Mysql 文章标签： ubuntu mysql linux

本文链接：https://blog.csdn.net/BigData_Mining/article/details/121526397

版权

Mysql 专栏收录该内容

23 篇文章

订阅专栏

设置MySQL

SHOW VARIABLES LIKE 'event_scheduler'; # 查看开关

SET GLOBAL event_scheduler = ON; #打开事件调度器

永久开启方法：my.cnf中[mysqld]添加event_scheduler=on #重启服务

查看事件

SHOW EVENTS;  # 其中 EVENT_DEFINITION字段为事件执行的sql语句
select EVENT_NAME,LAST_EXECUTED from information_schema.EVENTS;
DROP EVENT [IF EXISTS] event_name;   #删除事件

每天定时清理只保留最近30天数据table_name表

CREATE EVENT IF NOT EXISTS table_name ON SCHEDULE EVERY 1 DAY 
STARTS concat(CURRENT_DATE,' 00:00:00') 
DO
delete from table_name where timestamp < DATE_FORMAT( date_sub(curdate(),interval 30 day)  ,'%Y-%m-%d');

三个表格设置不同的事件启动

CREATE EVENT IF NOT EXISTS repoissue_event ON SCHEDULE EVERY 1 DAY STARTS CONCAT(CURRENT_DATE, ' 22:20:00') DO DELETE FROM repoissue where timecheck < DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 30 DAY),'%Y%m%d');


CREATE EVENT IF NOT EXISTS langrepo_event ON SCHEDULE EVERY 1 DAY STARTS CONCAT(CURRENT_DATE, ' 22:50:00') DO DELETE FROM langrepo where timecheck < DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 30 DAY),'%Y%m%d');


CREATE EVENT IF NOT EXISTS lang_trend_weekly_event ON SCHEDULE EVERY 1 DAY STARTS CONCAT(CURRENT_DATE, ' 23:30:00') DO DELETE FROM language_trend_weekly where timecheck < DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 30 DAY),'%Y-%m-%d');