当前位置：首页 > news >正文

mysql 大表凌晨定时删除数据

news 2025/7/19 12:58:07

有几张表数据量非常大，一次维护量有点大（一个月有500多万条数据，并且还在往上涨），

于是想了个定时删除数据，每天凌晨执行，这样每天删除数据量就小，

循环删除，每次删除5000条数据，直到当天的数据删除完：

1，做删除过程：

CREATE DEFINER=`root`@`%` PROCEDURE `S_delete_data2`()
BEGIN

   #当次删除行数
   declare delrows_int int default 0;

#删除月份量
declare Month_int int default -12;

#条件为真循环
loop_label:LOOP

       #开启事务（重要！不开的话,100w数据需要论天算）
       start transaction;

       #测试_删除6个月前的数据
       #delete FROM user01 where get_day < DATE_ADD(CURRENT_DATE,INTERVAL Month_int MONTH) LIMIT 10000;
       #SET delrows_int = delrows_int + ROW_COUNT(); #每删除表记录下删除行数
       #select delrows_int;

       #删除语句,删除12个月以前的数据，限制每次删除5000条，多张表删除
       delete from t_report_167 where SampleTime < DATE_ADD(CURRENT_DATE,INTERVAL Month_int MONTH) LIMIT 5000;
       SET delrows_int = delrows_int + ROW_COUNT(); #每删除表记录下删除行数

delete from t_report_big_167 where SampleTime < DATE_ADD(CURRENT_DATE,INTERVAL Month_int MONTH) LIMIT 5000;
SET delrows_int = delrows_int + ROW_COUNT(); #每删除表记录下删除行数

       IF delrows_int=0 THEN
           LEAVE loop_label; #退出循环
       END IF;
       #测试值显示
       #select delrows_int;

       IF @@ERROR_COUNT > 0 THEN    #判断事务成功与否
               ROLLBACK;
       ELSE
               COMMIT;
       END IF;

       SET delrows_int =0; #下次改为0

       #SELECT SLEEP(3) AS 'Waiting'; #暂停5秒，接收数据库其它操作，防止卡死

   END LOOP loop_label;

END
;

2，生成一个定时任务

#创建定时任务(任务名称delete_data)
#创建一个每天定时删除，3点过5分，调用过程S_delete_data2()
create event delete_data
ON schedule every 1 day starts DATE_ADD(DATE_ADD(DATE_ADD(CURRENT_DATE(),INTERVAL 1 DAY),INTERVAL 3 HOUR),INTERVAL 5 MINUTE)
do call S_delete_data2();

3，事件调度器必须开启才有效

查看事件调度器是否开启
SHOW VARIABLES LIKE 'event_scheduler';

#开启事件调度器（如果没有开启的话，执行下面）
SET GLOBAL event_scheduler = ON;

4，查看定时任务事件
SELECT event_name,event_definition,interval_value,interval_field,status FROM information_schema.EVENTS;

5，删除定时任务（delete_data是任务名称）
drop event delete_data;

标记下，以后查用

查看全文

http://www.lryc.cn/news/337427.html