MySQL 定时器实战：从入门到进阶

一、定时器基础：从认识到启用

1. 检查功能状态

-- 查看全局状态
    SHOW VARIABLES LIKE 'event_scheduler';
-- 输出结果为ON则已启用，OFF则需要开启
    SET GLOBAL event_scheduler = ON;

2. 基础语法结构

CREATE EVENT [IF NOT EXISTS] event_name
ON SCHEDULE 
schedule[ON COMPLETION [NOT] PRESERVE][ENABLE | DISABLE | 
DISABLE ON SLAVE]DO event_body;
-- 其中schedule支持多种时间格式
schedule ::=
AT timestamp [+ INTERVAL interval] ...
| EVERY interval [STARTS timestamp [+ INTERVAL interval] ...]
[ENDS timestamp [+ INTERVAL interval] ...]
interval ::=
quantity { YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND }

二、实战案例：三大典型应用场景

案例 1：电商订单自动取消（循环执行）

-- 创建事件
DELIMITER $$CREATE EVENT 
cancel_unpaid_orders
ON SCHEDULE EVERY 1 MINUTESTARTS
 CURRENT_TIMESTAMPDOBEGINUPDATE orders
 SET status = 'canceled', 
 update_time = NOW()WHERE 
 status = 'pending'AND create_time < DATE_SUB(NOW(), 
 INTERVAL 30 MINUTE);
 -- 释放库存（假设库存表通过订单ID关联）
 UPDATE order_items oi
 INNER JOIN 
 products p ON 
 oi.product_id = p.idSET 
 p.stock = p.stock + oi.quantityWHERE 
 oi.order_id IN (SELECT id FROM orders
 WHERE 
 status = 'canceled'AND update_time = NOW());END$$DELIMITER ;
 -- 查看事件状态
 SHOW EVENTS LIKE 'cancel_unpaid_orders';

案例 2：日志数据定期归档（单次执行）

-- 创建一次性事件
CREATE EVENT archive_operation_logs
ON SCHEDULE AT '2023-10-01 02:00:00'DOBEGIN
-- 创建归档表（如果不存在）
SET @archive_table = CONCAT('operation_log_', DATE_FORMAT(CURRENT_DATE - INTERVAL 1 MONTH, '%Y%m'));
SET @create_sql = CONCAT('CREATE TABLE IF NOT EXISTS ', @archive_table, ' LIKE operation_logs');
PREPARE stmt FROM @create_sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;
-- 移动数据
INSERT INTO `operation_logs_202309` 
(SELECT * FROM operation_logs WHERE create_time < '2023-10-01');
DELETE FROM operation_logs WHERE create_time < '2023-10-01';END;
-- 设置事件执行后自动删除（默认会保留事件定义）
ALTER EVENT archive_operation_logs ON COMPLETION NOT PRESERVE;

案例 3：性能监控数据定时清理（条件循环）

CREATE EVENT clean_slow_query_logsON 
SCHEDULE EVERY 1 DAYSTARTS '2023-09-01 00:00:00'DOBEGINDELETE FROM slow_query_logsWHERE create_time < DATE_SUB(NOW(), INTERVAL 30 DAY);-- 可选：添加重试机制（处理删除锁表问题）IF ROW_COUNT() < 0 THENRESIGNAL; -- 抛出异常，可结合错误处理机制END IF;END;

三、进阶技巧：让事件更可靠

1. 事务控制

DELIMITER $$CREATE EVENT safe_transaction_eventON SCHEDULE EVERY 1 HOURDOBEGINSTART TRANSACTION;UPDATE user_balance SET balance = balance - 100 WHERE user_id = 1;-- 模拟可能失败的操作INSERT INTO transaction_record (user_id, amount) VALUES (1, -100);COMMIT;-- 错误处理（需在存储过程中使用）DECLARE EXIT HANDLER FOR SQLEXCEPTIONBEGINROLLBACK;INSERT INTO event_error_log VALUES (NOW(), 'safe_transaction_event', ERROR_MESSAGE());END;END$$DELIMITER ;

2. 动态 SQL 应用

SET @table_name = CONCAT('report_', DATE_FORMAT(NOW(), '%Y%m'));
SET @sql = CONCAT('CREATE TABLE IF NOT EXISTS ', @table_name, ' (id INT PRIMARY KEY)');
PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;

3. 权限管理

-- 创建事件专用用户
CREATE USER 'event_user'@'%' IDENTIFIED BY 'StrongPassword123!';
GRANT EVENT, INSERT, UPDATE, DELETE ON mydb.* TO 'event_user'@'%';
-- 指定事件使用该用户执行（需超级权限）
ALTER EVENT event_name DISABLE; 
-- 先禁用事件
ALTER EVENT event_name OWNER TO 'event_user'@'%';ALTER EVENT event_name ENABLE;

四、避坑指南：生产环境注意事项

DELETE FROM old_data LIMIT 1000;

[mysqld]log_events = 
ONlog_output = 
FILEevent_scheduler_log_file = 
/var/log/mysql/event_scheduler.log

五、最佳实践：事件生命周期管理

-- 查看所有事件
SHOW EVENTS;
-- 查看事件定义
SHOW CREATE EVENT event_name;
-- 临时禁用事件（不删除定义）
ALTER EVENT event_name DISABLE;
-- 重新启用事件
ALTER EVENT event_name ENABLE;
-- 修改执行时间（无需重建事件）
ALTER EVENT event_name ON SCHEDULE EVERY 2 HOUR;
-- 删除事件
DROP EVENT IF EXISTS event_name;

CREATE TABLE event_management 
(event_name VARCHAR(64) 
PRIMARY KEY,description TEXT,
owner VARCHAR(32),
schedule_rule VARCHAR(128),
last_execute_time TIMESTAMP,
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

总结：何时该用 MySQL 定时器？