事件调度器

-- 查看事件调度器当前状态
SHOW VARIABLES LIKE 'event_scheduler';

-- 临时启用（重启后失效）
SET GLOBAL event_scheduler = ON;

-- 确认已开启
SHOW VARIABLES LIKE 'event_scheduler';

-- 永久启用（需修改 my.cnf 配置文件）
-- 在 [mysqld] 段添加：
-- event_scheduler = ON

CREATE EVENT event_name
ON SCHEDULE
    {AT timestamp [+ INTERVAL interval] | EVERY interval [STARTS timestamp] [ENDS timestamp]}
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT '说明文字']
DO
    sql_statement;

-- 创建日志表
CREATE TABLE logs (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_id INT,
    action VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入测试数据（模拟不同时间产生的日志）
INSERT INTO logs (emp_id, action, created_at) VALUES
(1, 'LOGIN', DATE_SUB(NOW(), INTERVAL 10 DAY)),
(2, 'LOGOUT', DATE_SUB(NOW(), INTERVAL 5 DAY)),
(1, 'UPDATE', DATE_SUB(NOW(), INTERVAL 0 DAY));

DELIMITER //

CREATE EVENT IF NOT EXISTS event_delete_old_logs
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
ON COMPLETION NOT PRESERVE
ENABLE
DO
BEGIN
    DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 7 DAY);
END //

DELIMITER ;

SHOW EVENTS WHERE Name = 'event_delete_old_logs';

-- 1 分钟后查询日志表
SELECT * FROM logs;

-- 继续使用 logs 表，补充更多历史数据
INSERT INTO logs (emp_id, action, created_at) VALUES
(1, 'LOGIN', DATE_SUB(NOW(), INTERVAL 35 DAY)),
(2, 'LOGOUT', DATE_SUB(NOW(), INTERVAL 32 DAY)),
(1, 'DELETE', DATE_SUB(NOW(), INTERVAL 31 DAY)),
(2, 'UPDATE', DATE_SUB(NOW(), INTERVAL 15 DAY)),
(1, 'LOGIN', DATE_SUB(NOW(), INTERVAL 2 DAY));

DELIMITER //

CREATE EVENT IF NOT EXISTS event_daily_cleanup
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
ON COMPLETION PRESERVE
ENABLE
COMMENT '每日清理30天前的日志并优化表'
DO
BEGIN
    DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
    OPTIMIZE TABLE logs;
END //

DELIMITER ;

SHOW EVENTS WHERE Name = 'event_daily_cleanup';

-- 等待 1 分钟后查询
SELECT * FROM logs;

-- 创建员工表（含入职时间）
CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    score DECIMAL(5,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建每日统计表
CREATE TABLE daily_stats (
    stat_id INT AUTO_INCREMENT PRIMARY KEY,
    stat_date DATE,
    new_employees INT,
    total_employees INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入测试数据（模拟不同日期入职的员工）
INSERT INTO employees (emp_name, dept, score, created_at) VALUES
('大翔', '技术部', 100, DATE_SUB(NOW(), INTERVAL 2 DAY)),
('白歌', '技术部', NULL, DATE_SUB(NOW(), INTERVAL 0 DAY));

SELECT * FROM daily_stats;

DELIMITER //

CREATE EVENT IF NOT EXISTS event_daily_report
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_DATE + INTERVAL 1 DAY
ON COMPLETION PRESERVE
ENABLE
COMMENT '每日凌晨统计当日新增员工数和总员工数'
DO
BEGIN
    INSERT INTO daily_stats (stat_date, new_employees, total_employees)
    SELECT
        CURDATE(),
        (SELECT COUNT(*) FROM employees WHERE DATE(created_at) = CURDATE()),
        (SELECT COUNT(*) FROM employees);
END //

DELIMITER ;

-- 手动执行事件中的逻辑，查看效果
INSERT INTO daily_stats (stat_date, new_employees, total_employees)
SELECT
    CURDATE(),
    (SELECT COUNT(*) FROM employees WHERE DATE(created_at) = CURDATE()),
    (SELECT COUNT(*) FROM employees);

DELIMITER //

CREATE EVENT IF NOT EXISTS event_fix_null_score
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
ENDS CURRENT_TIMESTAMP + INTERVAL 30 DAY
ON COMPLETION PRESERVE
ENABLE
COMMENT '每日检查并修正 score 为 NULL 的记录，30天后自动停止'
DO
BEGIN
    UPDATE employees
    SET score = 0
    WHERE score IS NULL;
END //

DELIMITER ;

SHOW EVENTS WHERE Name = 'event_fix_null_score';

SELECT * FROM employees;

SHOW EVENTS;

ALTER EVENT event_daily_cleanup DISABLE;

-- 确认已禁用
SHOW EVENTS WHERE Name = 'event_daily_cleanup';

ALTER EVENT event_daily_cleanup ENABLE;

-- 确认已启用
SHOW EVENTS WHERE Name = 'event_daily_cleanup';

ALTER EVENT event_daily_cleanup
ON SCHEDULE EVERY 12 HOUR
STARTS CURRENT_TIMESTAMP + INTERVAL 10 MINUTE;

SHOW CREATE EVENT event_daily_cleanup;

DROP EVENT IF EXISTS event_daily_cleanup;

-- 确认已删除
SHOW EVENTS WHERE Name = 'event_daily_cleanup';

-- 创建事件执行日志表
CREATE TABLE event_execution_logs (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    event_name VARCHAR(100),
    start_time DATETIME,
    end_time DATETIME,
    rows_affected INT,
    status VARCHAR(20),
    error_message TEXT
);

-- 继续使用 logs 表，补充测试数据
INSERT INTO logs (emp_id, action, created_at) VALUES
(1, 'OLD_LOGIN', DATE_SUB(NOW(), INTERVAL 10 DAY)),
(2, 'OLD_LOGOUT', DATE_SUB(NOW(), INTERVAL 8 DAY)),
(1, 'NEW_LOGIN', DATE_SUB(NOW(), INTERVAL 1 DAY));

DELIMITER //

CREATE EVENT IF NOT EXISTS event_cleanup_with_log
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
ON COMPLETION PRESERVE
ENABLE
DO
BEGIN
    DECLARE v_start DATETIME DEFAULT NOW();
    DECLARE v_rows INT DEFAULT 0;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        INSERT INTO event_execution_logs (event_name, start_time, end_time, status, error_message)
        VALUES ('event_cleanup_with_log', v_start, NOW(), 'FAILED', SQLERRM());
    END;

    DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 7 DAY);
    SET v_rows = ROW_COUNT();

    INSERT INTO event_execution_logs (event_name, start_time, end_time, rows_affected, status)
    VALUES ('event_cleanup_with_log', v_start, NOW(), v_rows, 'SUCCESS');
END //

DELIMITER ;

SELECT * FROM logs;

SELECT * FROM event_execution_logs;