MySQL定时任务,清理表数据方式

lonecloud 2022-11-19 我要评论

MySQL定时清理表数据

针对一些每天都会产生大量数据的表，比如说报警、日志表等，数据量太大占用了较多的空间，可以选择定时清理这些表，只保留一段时间内的数据

对某个表只保留一个月内的数据

查看是否开启MySQL定时任务

# 显示为ON则已开启
SHOW VARIABLES LIKE 'event_scheduler';

显示为ON表示已经开启

若未开启，可以手动开启

在my.cnf（windows下是my.ini）添加配置 event_scheduler=ON

添加完毕后，重启MySQL服务即可

也可以通过语句方式开启，但是这种方式只在当前启动的实例有效，重启MySQL服务后就会失效

set global event_scheduler=1;

创建存储过程

CREATE PROCEDURE `del_data_count`(IN date_inter INT)
BEGIN
DELETE FROM alarminfo
WHERE
(TO_DAYS(NOW()) - TO_DAYS(alarmTime))>=date_inter; # 当前系统时间减去表的时间字段名
END

创建事件

# 创建事件
CREATE EVENT `del_tbl_expired_data`
# 表示创建时间定时周期，以及开始时间，该处表示从明天1点从开始，每天执行一次该任务
ON SCHEDULE EVERY '1' DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR)
# 表示创建后并不开始生效
ON COMPLETION PRESERVE DISABLE
# 执行存储过程，删除30天之前的数据
DO CALL del_data_count (30);

相关语句：

# 查看已有定时事件任务
SHOW EVENTS; 
# 开启事件
alter event event_name on completion preserve enable;
# 关闭事件
alter event event_name on completion preserve disable;

MySQL定时自动删除数据表

描述

自MySQL5.1.6起，增加了一个非常有特色的功能–事件调度器(EventScheduler)，可以用做定时执行某些特定任务（例如：删除记录、对数据进行汇总等等），来取代原先只能由操作系统的计划任务来执行的工作。

更值得一提的是MySQL的事件调度器可以精确到每秒钟执行一个任务，而操作系统的计划任务（如：Linux下的CRON或Windows下的任务计划）只能精确到每分钟执行一次。

对于一些对数据实时性要求比较高的应用（例如：股票、赔率、比分等）就非常适合。

开启事件调度器

1、确认是否开启

SHOW VARIABLES LIKE 'event_scheduler';

2、开启命令

SET GLOBAL event_scheduler = ON;

创建事件<创建后默认开启>

CREATE EVENT [IF NOT EXISTS] event_name ON SCHEDULE schedule
 [ON COMPLETION [NOT] PRESERVE] [ENABLE | DISABLE] [COMMENT 'comment']

 DO sql_statement;

例如：

1.每分钟(60S)清空一次记录表

use database_name;
create event table_truncate on schedule every 60 second do truncate table operator_record_log;

2.每30天(2592000S)清空30天前的所有记录，仅保留近30天数据

use database_name;
create event table_truncate on schedule every 2592000 second do delete from operator_record_log where create_date < DATE_SUB(CURDATE(),INTERVAL 30 DAY);

3.指定时间将表清空

use database_name;
create event table_truncate on schedule at timestamp '2022-01-01 00:00:00' do truncate table operator_record_log;

4.每天定时清空

use database_name;
create event table_truncate on schedule every 1 day starts '2022-01-01 00:00:00' do truncate table operator_record_log;

关闭事件

alter event event_name disable;

开启事件

alter event event_name enable;

删除事件

drop event if exists event_name;

查看事件

show events;

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。