MySQL中事件调度器用法与使用场景详解

-- 查看事件调度器是否启用
show variables like 'event_scheduler';

-- 查看当前运行的事件
show processlist;

-- 方法1：动态启用（重启后失效）
set global event_scheduler = on;

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

-- 应该显示 on 或 1
select @@event_scheduler;

-- 查看事件调度器进程
show processlist;
-- 应该能看到 "daemon" 用户的 "event_scheduler" 进程

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;

-- 在指定时间执行一次
on schedule at timestamp

-- 示例
on schedule at '2024-12-31 23:59:59'
on schedule at current_timestamp + interval 1 hour

-- 按间隔重复执行
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'

-- 创建每天凌晨2点清理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天前的日志数据'
do
begin
    -- 删除30天前的访问日志
    delete from access_logs 
    where created_at < date_sub(now(), interval 30 day);
    
    -- 删除30天前的错误日志
    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 ;

-- 创建每小时统计用户活跃度的事件
delimiter $$

create event hourly_user_stats
on schedule every 1 hour
on completion preserve
enable
comment '每小时统计用户活跃度'
do
begin
    declare current_hour datetime;
    set current_hour = date_format(now(), '%y-%m-%d %h:00:00');
    
    -- 插入或更新用户活跃统计
    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 ;

-- 创建每周日凌晨进行数据备份的事件
delimiter $$

create event weekly_backup
on schedule every 1 week
starts '2024-01-07 03:00:00'  -- 从第一个周日开始
on completion preserve
enable
comment '每周数据备份'
do
begin
    -- 创建备份表
    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;

-- 查看特定数据库的事件
show events from database_name;

-- 查看事件详细信息
select * from information_schema.events 
where event_schema = 'your_database';

-- 查看特定事件的创建语句
show create event event_name;

-- 修改事件调度
alter event cleanup_old_logs
on schedule every 2 day;

-- 启用/禁用事件
alter event cleanup_old_logs enable;
alter event cleanup_old_logs disable;

-- 修改事件内容
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;

-- 定期清理临时数据
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;

-- 定期收集性能指标
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分钟内的平均qps
        (select variable_value from information_schema.global_status 
         where variable_name = 'slow_queries');
end;

-- 定期处理订单状态
create event process_pending_orders
on schedule every 10 minute
do
begin
    -- 自动取消超时未支付订单
    update orders 
    set status = 'cancelled', updated_at = now()
    where status = 'pending' 
      and created_at < date_sub(now(), interval 30 minute);
    
    -- 自动确认收货超时订单
    update orders 
    set status = 'completed', updated_at = now()
    where status = 'shipped' 
      and shipped_at < date_sub(now(), interval 7 day);
end;

-- 避免在高峰期执行重型任务
create event heavy_maintenance
on schedule every 1 day
starts '2024-01-01 02:00:00'  -- 选择业务低峰期
do
begin
    -- 分批处理大量数据
    declare done int default false;
    declare batch_size int default 1000;
    
    repeat
        delete from large_table 
        where condition 
        limit batch_size;
        
        -- 检查是否还有数据需要处理
        select row_count() = 0 into done;
        
        -- 短暂休息，避免长时间锁表
        do sleep(0.1);
    until done end repeat;
end;

-- 添加错误处理和日志记录
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;
    
    -- 执行清理操作
    delete from old_data where created_at < date_sub(now(), interval 30 day);
    
    -- 记录成功执行
    insert into event_execution_log (event_name, execution_time, status)
    values ('robust_cleanup', now(), 'success');
    
    commit;
end$$

delimiter ;

-- 查看事件调度器状态
select 
    event_schema,
    event_name,
    status,
    last_executed,
    next_execution_time
from information_schema.events;

-- 查看事件执行历史（需要开启general_log）
select * from mysql.general_log 
where command_type = 'query' 
  and argument like '%event%'
order by event_time desc;

-- 手动执行事件内容进行测试
-- 将事件内容复制出来单独执行

-- 创建测试事件（短间隔）
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;