业务系统运行一段时间后，数据库会产生大量历史业务数据，这些数据长期留存会占用过多存储空间，同时拖慢查询效率。通过MySQL的存储过程搭配定时任务，可以实现数据的自动清理与智能归档，无需依赖外部脚本就能完成数据生命周期管理。
实现思路梳理
整体实现分为两个核心部分，首先是编写存储过程处理数据逻辑，其次是配置定时任务按周期触发存储过程执行。具体流程如下：
- 编写存储过程，逻辑包含筛选符合归档条件的数据，将数据插入归档表，最后删除原表中的已归档数据
- 在MySQL中创建定时任务，设置执行周期，关联编写好的存储过程
- 开启MySQL的事件调度功能，确保定时任务可以正常触发执行
前置准备：创建测试表
首先创建业务表和对应的归档表，归档表结构需要和原业务表保持一致，仅用来存储历史归档数据。示例表结构如下：
-- 创建业务表，存储当前有效数据 CREATE TABLE `business_data` ( `id` int(11) NOT NULL AUTO_INCREMENT, `content` varchar(255) DEFAULT NULL, `create_time` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_create_time` (`create_time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 创建归档表，存储超过30天的历史数据 CREATE TABLE `business_data_archive` ( `id` int(11) NOT NULL AUTO_INCREMENT, `content` varchar(255) DEFAULT NULL, `create_time` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_create_time` (`create_time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
编写数据归档清理存储过程
存储过程需要实现三个核心逻辑：查询超过30天的业务数据、将数据插入归档表、删除原业务表中已归档的数据。编写示例如下：
DELIMITER //
CREATE PROCEDURE `proc_archive_business_data`()
BEGIN
-- 声明异常处理，出现错误时回滚事务
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
RESIGNAL;
END;
-- 开启事务，保证数据操作原子性
START TRANSACTION;
-- 1. 将超过30天的业务数据插入归档表
INSERT INTO business_data_archive (id, content, create_time)
SELECT id, content, create_time
FROM business_data
WHERE create_time < DATE_SUB(NOW(), INTERVAL 30 DAY);
-- 2. 删除原业务表中已归档的数据
DELETE FROM business_data
WHERE create_time < DATE_SUB(NOW(), INTERVAL 30 DAY);
-- 提交事务
COMMIT;
END //
DELIMITER ;
上述存储过程中，首先通过DATE_SUB(NOW(), INTERVAL 30 DAY)计算30天前的时间点，筛选出符合条件的历史数据。插入和删除操作放在同一个事务中，避免数据不一致的问题。
配置MySQL定时任务
MySQL自带的事件调度器可以实现定时触发存储过程的功能，首先确认事件调度器是否开启：
-- 查看事件调度器状态 SHOW VARIABLES LIKE 'event_scheduler';
如果返回值是OFF，需要手动开启事件调度器，开启命令如下：
-- 开启事件调度器，重启MySQL后会恢复默认，如需永久生效需要修改配置文件 SET GLOBAL event_scheduler = ON;
接下来创建定时任务，设置每天凌晨2点执行上述存储过程，示例如下：
CREATE EVENT IF NOT EXISTS `event_archive_business_data` ON SCHEDULE EVERY 1 DAY STARTS '2024-01-01 02:00:00' ON COMPLETION PRESERVE COMMENT '每天凌晨2点执行业务数据归档清理操作' DO CALL proc_archive_business_data();
定时任务创建完成后，可以通过以下命令查看任务状态：
-- 查看所有定时任务 SHOW EVENTS;
注意事项
- 归档表需要提前创建，且结构要和原业务表保持一致，避免插入数据时出现字段不匹配的问题
- 存储过程中建议加入事务控制，防止插入归档表成功但删除原表数据失败导致的数据重复问题
- 如果数据量较大，建议分批处理数据，避免单次操作锁表时间过长影响业务正常运行
- 测试阶段可以先手动调用存储过程验证逻辑是否正确，再开启定时任务
手动调用测试
在定时任务正式生效前，可以先手动调用存储过程验证功能是否正常：
-- 手动调用存储过程测试 CALL proc_archive_business_data();
执行完成后，查询原业务表和归档表的数据，确认超过30天的数据已经移动到归档表中，原表中不再存在这些数据，说明逻辑运行正常。