MySQL自动删除历史数据

注：数据库5.1版本开始支持事件调度

相关命令

-- 查看是否开启事件调度
SHOW VARIABLES LIKE 'event_scheduler';
-- 开启事件调度
SET GLOBAL event_scheduler = ON;
-- 关闭事件调度
SET GLOBAL event_scheduler = OFF;
-- 查看事件
SELECT * FROM information_schema.EVENTS;

-- 整理表
OPTIMIZE TABLE tb_test;

-- 启用事件
ALTER EVENT e_clear_test ENABLE;
-- 停用事件
ALTER EVENT e_clear_test DISABLE;

-- 进程列表
show processlist;
select * from information_schema.processlist where command != 'Sleep' order by time desc;

 

样例说明

保留3个月数据，每月执行一次
tb_test: 表名
createtime: 创建时间字段名

 

调用SQL语句样例1

DROP EVENT IF EXISTS e_clear_test;
CREATE EVENT e_clear_test
ON SCHEDULE EVERY '1' MONTH STARTS '2021-06-01 00:00:00'
COMMENT '删除历史数据'
DO delete from tb_test where createtime<DATE_SUB(CURDATE(), INTERVAL 3 MONTH);

调用存储过程样例2 - (大表推荐)

drop procedure if exists proc_clear_test;
CREATE PROCEDURE proc_clear_test()
BEGIN
WHILE ((select case when max(createtime) is null then 0 else 1 end from tb_test t where createtime<DATE_SUB(CURDATE(), INTERVAL 3 MONTH) limit 1) > 0) DO
delete from tb_test where createtime<DATE_SUB(CURDATE(), INTERVAL 3 MONTH) limit 1000;
do SLEEP(5);
END WHILE;
--OPTIMIZE TABLE tb_test;
END;

DROP EVENT IF EXISTS e_clear_test2;
CREATE EVENT e_clear_test2
ON SCHEDULE EVERY '1' MONTH STARTS '2021-06-01 00:00:00'
COMMENT '删除历史数据'
DO CALL proc_clear_test();

注：
1.【OPTIMIZE TABLE tb_test】操作会锁表
2.存储过程参数不支持直接传入表名和字段名，如果需要可以通过拼接生成语句的方式

 

常见问题

问题描述：Lost connection to MySQL server during query
解决方案1：在线调整交互超时(10天)

set session interactive_timeout = 864000;
set global interactive_timeout = 864000;
set session wait_timeout = 864000;
set global wait_timeout = 864000;

解决方案2：在线调整交互超时(10天)，在my.ini文件中添加或者修改以下变量

wait_timeout = 864000
interactive_timeout = 864000

max_allowed_packet = 20M

验证方式

show session variables like '%timeout';
show global variables like '%timeout';