如何创建MySQL存储过程按周期清理数据（明细1天/历史3周）

需求说明 #

针对两类表做数据清理：

• 明细数据表（示例表名：detail_table）：仅保留最近1天的数据
• 历史数据表（示例表名：history_table）：仅保留最近3周的数据

存储过程代码 #

DELIMITER //

CREATE PROCEDURE CleanExpiredData()
BEGIN
    -- 清理明细数据：删除1天前的记录（假设时间字段为create_time）
    DELETE FROM detail_table 
    WHERE create_time < DATE_SUB(NOW(), INTERVAL 1 DAY);
    
    -- 清理历史数据：删除3周前的记录（假设时间字段为create_time）
    DELETE FROM history_table 
    WHERE create_time < DATE_SUB(NOW(), INTERVAL 3 WEEK);
    
    -- 可选：返回清理行数
    SELECT ROW_COUNT() AS total_deleted_rows;
END //

DELIMITER ;

代码说明 #

• DELIMITER //：临时修改语句结束符为//，避免存储过程内的分号和默认结束符冲突
• DATE_SUB(NOW(), INTERVAL 1 DAY)：计算当前时间往前推1天的时间点，作为明细数据的保留阈值
• DATE_SUB(NOW(), INTERVAL 3 WEEK)：计算当前时间往前推3周的时间点，作为历史数据的保留阈值
• ROW_COUNT()：返回本次存储过程执行后总共删除的行数，方便验证清理效果

创建与调用方法 #

1. 创建存储过程：
   直接在MySQL客户端（或Navicat等工具）执行上述完整SQL代码即可。执行前确保当前账号拥有CREATE ROUTINE权限。

2. 调用存储过程：

   CALL CleanExpiredData();
   

关键注意事项 #

• 索引优化：务必在两张表的create_time字段上建立索引，否则DELETE操作会触发全表扫描，严重影响数据库性能
• 事务控制：如果需要保证两张表的清理操作原子性（要么都成功，要么都失败），可以给存储过程添加事务逻辑：

  DELIMITER //
  
  CREATE PROCEDURE CleanExpiredData()
  BEGIN
      DECLARE EXIT HANDLER FOR SQLEXCEPTION
      BEGIN
          ROLLBACK;
          RESIGNAL;
      END;
      
      START TRANSACTION;
      
      DELETE FROM detail_table WHERE create_time < DATE_SUB(NOW(), INTERVAL 1 DAY);
      DELETE FROM history_table WHERE create_time < DATE_SUB(NOW(), INTERVAL 3 WEEK);
      
      COMMIT;
      SELECT ROW_COUNT() AS total_deleted_rows;
  END //
  
  DELIMITER ;
  

• 定时执行：如果需要自动定期清理，可以通过MySQL的EVENT事件调度器，或者Linux的crontab、Windows任务计划来定时调用该存储过程

内容的提问来源于stack exchange，提问作者user21387267