-- 查看事件調(diào)度器是否啟用
SHOW VARIABLES LIKE 'event_scheduler';

-- 查看當(dāng)前運(yùn)行的事件
SHOW PROCESSLIST;

-- 方法1：動(dòng)態(tài)啟用（重啟后失效）
SET GLOBAL event_scheduler = ON;

-- 方法2：在配置文件中永久啟用
-- 在my.cnf或my.ini中添加：
-- [mysqld]
-- event_scheduler = ON

-- 應(yīng)該顯示 ON 或 1
SELECT @@event_scheduler;

-- 查看事件調(diào)度器進(jìn)程
SHOW PROCESSLIST;
-- 應(yīng)該能看到 "Daemon" 用戶的 "event_scheduler" 進(jìn)程

CREATE
    [DEFINER = user]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'string']
    DO
      event_body;

-- 在指定時(shí)間執(zhí)行一次
ON SCHEDULE AT timestamp

-- 示例
ON SCHEDULE AT '2024-12-31 23:59:59'
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR

-- 按間隔重復(fù)執(zhí)行
ON SCHEDULE EVERY interval
[STARTS timestamp]
[ENDS timestamp]

-- 示例
ON SCHEDULE EVERY 1 DAY
ON SCHEDULE EVERY 1 HOUR STARTS '2024-01-01 00:00:00'
ON SCHEDULE EVERY 30 MINUTE STARTS NOW() ENDS '2024-12-31 23:59:59'

-- 創(chuàng)建每天凌晨2點(diǎn)清理30天前日志的事件
DELIMITER $$

CREATE EVENT IF NOT EXISTS cleanup_old_logs
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 02:00:00'
ON COMPLETION PRESERVE
ENABLE
COMMENT '每天清理30天前的日志數(shù)據(jù)'
DO
BEGIN
    -- 刪除30天前的訪問(wèn)日志
    DELETE FROM access_logs 
    WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
    
    -- 刪除30天前的錯(cuò)誤日志
    DELETE FROM error_logs 
    WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
    
    -- 記錄清理操作
    INSERT INTO maintenance_log (operation, executed_at, description)
    VALUES ('cleanup_old_logs', NOW(), 
            CONCAT('Cleaned logs older than ', DATE_SUB(NOW(), INTERVAL 30 DAY)));
END$$

DELIMITER ;

-- 創(chuàng)建每小時(shí)統(tǒng)計(jì)用戶活躍度的事件
DELIMITER $$

CREATE EVENT hourly_user_stats
ON SCHEDULE EVERY 1 HOUR
ON COMPLETION PRESERVE
ENABLE
COMMENT '每小時(shí)統(tǒng)計(jì)用戶活躍度'
DO
BEGIN
    DECLARE current_hour DATETIME;
    SET current_hour = DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00');
    
    -- 插入或更新用戶活躍統(tǒng)計(jì)
    INSERT INTO user_activity_stats (hour, active_users, total_actions)
    SELECT 
        current_hour,
        COUNT(DISTINCT user_id) as active_users,
        COUNT(*) as total_actions
    FROM user_actions 
    WHERE created_at >= current_hour 
      AND created_at < DATE_ADD(current_hour, INTERVAL 1 HOUR)
    ON DUPLICATE KEY UPDATE
        active_users = VALUES(active_users),
        total_actions = VALUES(total_actions),
        updated_at = NOW();
END$$

DELIMITER ;

-- 創(chuàng)建每周日凌晨進(jìn)行數(shù)據(jù)備份的事件
DELIMITER $$

CREATE EVENT weekly_backup
ON SCHEDULE EVERY 1 WEEK
STARTS '2024-01-07 03:00:00'  -- 從第一個(gè)周日開(kāi)始
ON COMPLETION PRESERVE
ENABLE
COMMENT '每周數(shù)據(jù)備份'
DO
BEGIN
    -- 創(chuàng)建備份表
    SET @backup_table = CONCAT('user_data_backup_', DATE_FORMAT(NOW(), '%Y%m%d'));
    SET @sql = CONCAT('CREATE TABLE ', @backup_table, ' AS SELECT * FROM user_data');
    
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    -- 記錄備份操作
    INSERT INTO backup_log (backup_table, created_at, status)
    VALUES (@backup_table, NOW(), 'completed');
END$$

DELIMITER ;

-- 查看所有事件
SHOW EVENTS;

-- 查看特定數(shù)據(jù)庫(kù)的事件
SHOW EVENTS FROM database_name;

-- 查看事件詳細(xì)信息
SELECT * FROM information_schema.EVENTS 
WHERE EVENT_SCHEMA = 'your_database';

-- 查看特定事件的創(chuàng)建語(yǔ)句
SHOW CREATE EVENT event_name;

-- 修改事件調(diào)度
ALTER EVENT cleanup_old_logs
ON SCHEDULE EVERY 2 DAY;

-- 啟用/禁用事件
ALTER EVENT cleanup_old_logs ENABLE;
ALTER EVENT cleanup_old_logs DISABLE;

-- 修改事件內(nèi)容
ALTER EVENT cleanup_old_logs
DO
BEGIN
    DELETE FROM access_logs 
    WHERE created_at < DATE_SUB(NOW(), INTERVAL 60 DAY);
END;

-- 刪除事件
DROP EVENT IF EXISTS cleanup_old_logs;

-- 定期清理臨時(shí)數(shù)據(jù)
CREATE EVENT cleanup_temp_data
ON SCHEDULE EVERY 1 HOUR
DO
BEGIN
    DELETE FROM temp_sessions WHERE expires_at < NOW();
    DELETE FROM temp_files WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 DAY);
END;

-- 定期收集性能指標(biāo)
CREATE EVENT collect_performance_metrics
ON SCHEDULE EVERY 5 MINUTE
DO
BEGIN
    INSERT INTO performance_metrics (
        timestamp, 
        connections, 
        queries_per_second,
        slow_queries
    )
    SELECT 
        NOW(),
        (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS 
         WHERE VARIABLE_NAME = 'Threads_connected'),
        (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS 
         WHERE VARIABLE_NAME = 'Queries') / 300, -- 5分鐘內(nèi)的平均QPS
        (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS 
         WHERE VARIABLE_NAME = 'Slow_queries');
END;

-- 定期處理訂單狀態(tài)
CREATE EVENT process_pending_orders
ON SCHEDULE EVERY 10 MINUTE
DO
BEGIN
    -- 自動(dòng)取消超時(shí)未支付訂單
    UPDATE orders 
    SET status = 'cancelled', updated_at = NOW()
    WHERE status = 'pending' 
      AND created_at < DATE_SUB(NOW(), INTERVAL 30 MINUTE);
    
    -- 自動(dòng)確認(rèn)收貨超時(shí)訂單
    UPDATE orders 
    SET status = 'completed', updated_at = NOW()
    WHERE status = 'shipped' 
      AND shipped_at < DATE_SUB(NOW(), INTERVAL 7 DAY);
END;

-- 避免在高峰期執(zhí)行重型任務(wù)
CREATE EVENT heavy_maintenance
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 02:00:00'  -- 選擇業(yè)務(wù)低峰期
DO
BEGIN
    -- 分批處理大量數(shù)據(jù)
    DECLARE done INT DEFAULT FALSE;
    DECLARE batch_size INT DEFAULT 1000;
    
    REPEAT
        DELETE FROM large_table 
        WHERE condition 
        LIMIT batch_size;
        
        -- 檢查是否還有數(shù)據(jù)需要處理
        SELECT ROW_COUNT() = 0 INTO done;
        
        -- 短暫休息，避免長(zhǎng)時(shí)間鎖表
        DO SLEEP(0.1);
    UNTIL done END REPEAT;
END;

-- 添加錯(cuò)誤處理和日志記錄
DELIMITER $$

CREATE EVENT robust_cleanup
ON SCHEDULE EVERY 1 DAY
DO
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        INSERT INTO event_error_log (event_name, error_time, error_message)
        VALUES ('robust_cleanup', NOW(), 'Event execution failed');
    END;
    
    START TRANSACTION;
    
    -- 執(zhí)行清理操作
    DELETE FROM old_data WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
    
    -- 記錄成功執(zhí)行
    INSERT INTO event_execution_log (event_name, execution_time, status)
    VALUES ('robust_cleanup', NOW(), 'success');
    
    COMMIT;
END$$

DELIMITER ;

-- 查看事件調(diào)度器狀態(tài)
SELECT 
    EVENT_SCHEMA,
    EVENT_NAME,
    STATUS,
    LAST_EXECUTED,
    NEXT_EXECUTION_TIME
FROM information_schema.EVENTS;

-- 查看事件執(zhí)行歷史（需要開(kāi)啟general_log）
SELECT * FROM mysql.general_log 
WHERE command_type = 'Query' 
  AND argument LIKE '%EVENT%'
ORDER BY event_time DESC;

-- 手動(dòng)執(zhí)行事件內(nèi)容進(jìn)行測(cè)試
-- 將事件內(nèi)容復(fù)制出來(lái)單獨(dú)執(zhí)行

-- 創(chuàng)建測(cè)試事件（短間隔）
CREATE EVENT test_event
ON SCHEDULE EVERY 1 MINUTE
STARTS NOW()
ENDS DATE_ADD(NOW(), INTERVAL 5 MINUTE)
DO
BEGIN
    INSERT INTO test_log VALUES (NOW(), 'Event executed');
END;