mysql定时删除日志

创建记录表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for delete_log
-- ----------------------------
DROP TABLE IF EXISTS `delete_log`;
CREATE TABLE `delete_log`  (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `delete_time` datetime NULL DEFAULT NULL,
  `table_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `affected_rows` int NULL DEFAULT NULL,
  `execution_time` decimal(10, 2) NULL DEFAULT NULL,
  `status` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `message` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 42 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

创建存储过程

CREATE DEFINER = CURRENT_USER PROCEDURE delete_mdm_issue_log()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE start_time TIMESTAMP;
    DECLARE batch_size INT DEFAULT 1000;
    DECLARE total_deleted INT DEFAULT 0;
    DECLARE batch_count INT DEFAULT 0;
    DECLARE error_msg TEXT;
    
    -- 错误处理
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1 error_msg = MESSAGE_TEXT;
        
        INSERT INTO delete_log 
            (delete_time, table_name, affected_rows, execution_time, status, message)
        VALUES 
            (NOW(), 'mdm_issue_log', total_deleted,
             TIMESTAMPDIFF(SECOND, start_time, NOW()),
             'ERROR', error_msg);
    END;
    
    SET start_time = NOW();
    
    -- 循环删除数据
    REPEAT
        -- 删除一批数据
        DELETE FROM mdm_issue_log 
        WHERE create_time < DATE_SUB(NOW(), INTERVAL 180 DAY)
        LIMIT batch_size;
        
        -- 累计删除数量
        SET total_deleted = total_deleted + ROW_COUNT();
        SET batch_count = batch_count + 1;
        
        -- 当没有记录被删除时设置 done 为 true
        SET done = ROW_COUNT() = 0;
        
        -- 每批之间暂停一秒
        -- DO SLEEP(1);
        
    UNTIL done END REPEAT;
    
    -- 记录完成日志
    INSERT INTO delete_log 
        (delete_time, table_name, affected_rows, execution_time, status, message)
    VALUES 
        (NOW(), 'mdm_issue_log', total_deleted,
         TIMESTAMPDIFF(SECOND, start_time, NOW()),
         'SUCCESS', 
         CONCAT('Completed in ', batch_count, ' batches'));
END;

创建调用事件

-- 首先确保事件调度器是开启的
SET GLOBAL event_scheduler = ON;

-- 创建事件
CREATE EVENT delete_sys_logs_daily
ON SCHEDULE EVERY 1 DAY
STARTS TIMESTAMP(CURRENT_DATE + INTERVAL 2 HOUR)
DO
BEGIN

    CALL delete_mdm_issue_log();

END;