MariaDB定时事件：周度视图导出与条件触发清理

原代码存在的关键问题 #

• 事件DO块无法直接声明变量，必须通过存储过程包裹逻辑
• 变量声明语法错误（MariaDB局部变量无需@前缀，声明格式应为DECLARE 变量名 类型;）
• 时间戳未按需求格式化，直接使用current_timestamp会导致文件名含特殊字符（如:）
• INTO OUTFILE导出的是文本格式（实际为CSV），并非真正的XLSX二进制文件
• 清理事件无法依赖导出事件的执行结果，单独定时会导致导出失败时仍执行清理
• STARTS子句的时间计算错误，无法准确定位每周六午夜

修正后的实现方案 #

1. 创建存储过程（包含导出+条件清理逻辑） #

DELIMITER //

CREATE PROCEDURE weekly_report_and_cleanup()
BEGIN
    -- 声明局部变量
    DECLARE v_path VARCHAR(100);
    DECLARE v_view_name VARCHAR(50);
    DECLARE v_site_id INT;
    DECLARE v_timestamp_str VARCHAR(20);
    DECLARE v_filetype VARCHAR(5);
    DECLARE v_full_filename VARCHAR(200);
    
    -- 错误处理：导出失败则终止，不执行清理
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT '导出失败，清理操作已取消' AS result;
    END;
    
    START TRANSACTION;
    
    -- 初始化变量
    SET v_path = '/home/reports/';
    SET v_view_name = 'overall_traffic_weekly';
    SET v_site_id = 3;
    -- 按需求格式化时间戳（示例为日+月+年，可根据需要调整DATE_FORMAT参数）
    SET v_timestamp_str = DATE_FORMAT(CURRENT_TIMESTAMP, '%d%m%Y');
    SET v_filetype = '.xlsx';
    -- 拼接文件名（匹配示例格式：overall_weekly_3_01082022.xlsx）
    SET v_full_filename = CONCAT(v_path, 'overall_weekly_', v_site_id, '_', v_timestamp_str, v_filetype);
    
    -- 导出视图数据（注：实际导出为CSV文本，后缀改为xlsx兼容表格软件）
    SELECT * INTO OUTFILE v_full_filename
    FIELDS TERMINATED BY ';'
    OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    FROM (
        -- 添加表头
        SELECT 'Column_name_1', 'Column_name2', ...
        UNION ALL
        -- 从视图取数据
        SELECT * FROM overall_traffic_weekly WHERE site_id = v_site_id
    ) AS resulting_set;
    
    -- 导出成功后执行清理
    TRUNCATE TABLE sourcetable1;
    TRUNCATE TABLE sourcetable2;
    
    COMMIT;
    SELECT '导出与清理操作均完成' AS result;
END //

DELIMITER ;

2. 创建每周六午夜执行的事件 #

CREATE EVENT overall_weekly_event
ON SCHEDULE EVERY 1 WEEK
-- 计算下一个周六午夜的时间：WEEKDAY(5)=周六，若当前是周六则当天，否则加(5-WEEKDAY)天
STARTS DATE_ADD(DATE(CURRENT_TIMESTAMP), INTERVAL IF(WEEKDAY(CURRENT_TIMESTAMP) = 5, 0, 5 - WEEKDAY(CURRENT_TIMESTAMP)) DAY) + INTERVAL 0 HOUR
ON COMPLETION PRESERVE
ENABLE
DO
    CALL weekly_report_and_cleanup();

补充说明 #

• 权限要求：执行INTO OUTFILE需要FILE权限，且目标目录/home/reports/需MariaDB进程可读写
• XLSX格式说明：MariaDB无法直接导出二进制XLSX，上述方案是导出CSV文本后修改后缀，Excel/WPS等软件可正常打开；若需真XLSX，需结合外部脚本（如Python/Shell）调用MariaDB导出后转换格式
• 错误日志：可通过SHOW EVENTS查看事件状态，或开启MariaDB慢查询/错误日志排查执行问题

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