mysql定时删除表数据

查看mysql是否开启定时任务

mysql> show variables like '%scheduler%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+
1 row in set

开启mysql定时任务

-- 临时开启
SET GLOBAL event_scheduler = ON;

-- 永久开启,my.cnf中[mysqld]添加,并重启服务
event_scheduler=on

删除某表2天前的数据SQL

-- task_history:表名,start_time:日期字段
delete From task_history where DATE(start_time) <= DATE(DATE_SUB(NOW(),INTERVAL 2 day));

创建event

• 方法一：直接创建删除数据event

create event del_task_history on SCHEDULE every 1 DAY do delete from compare.task_history where DATE(start_time) <= DATE(DATE_SUB(NOW(),INTERVAL 2 day));

• 方法二：先创建删除数据的存储过程

-- 创建存储过程,先use database_name;
DELIMITER // 
create procedure del_task_history()
BEGIN
    DELETE FROM task_history WHERE DATE(start_time) <= DATE(DATE_SUB(NOW(),INTERVAL 2 day));
END//
DELIMITER ;
-- 查看存储过程
select * from mysql.proc where db='数据库名';
-- 调用存储过程
call del_task_history();
-- 删除存储过程
DROP PROCEDURE IF EXISTS del_task_history;


-- 创建event
create event del_task_history  
on schedule 
EVERY 1 DAY  
STARTS '2020-10-01 00:00:00'  
ON COMPLETION NOT PRESERVE ENABLE DO CALL del_task_history();

开启和关闭event

--  开启某事件
ALTER EVENT del_task_history ON COMPLETION PRESERVE ENABLE;
--  关闭某事件
ALTER EVENT del_task_history ON COMPLETION PRESERVE DISABLE;

查询event

-- 查询event列表
show events;
-- 根据event名称查询
show create event del_task_history;

-- 查询执行记录
select * from information_schema.events;
select * from mysql.event;

删除event

DROP EVENT IF EXISTS del_task_history;

写一个带参数的存储过程

存储过程一般不能以表名作为参数，不然会报错，所以采用prepare方式.

• prepare: 预处理
• execute: 执行
• deallocate: 释放资源

-- 参数1：要删除的表, 参数2：时间戳列名, 参数3：删除多少天前的数据
DELIMITER // 
create procedure delete_table_data(IN table_name varchar(50),IN column_name varchar(50),IN interval_day int)
BEGIN
    set @statements = concat('DELETE FROM ',table_name, ' WHERE DATE(',column_name,') <= DATE(DATE_SUB(NOW(),INTERVAL ',interval_day,' day))');
    prepare stmt from @statements;
    execute stmt;
    deallocate prepare stmt;
END//
DELIMITER ;

-- 调用
call delete_table_data('t_test','create_time',90);