MySQL编程进阶之：事件调度器的执行时机：`AT`和`EVERY`参数的用法与区别。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

各位观众老爷们，大家好！我是你们的老朋友，今天咱们来聊聊MySQL里一个相当有趣，但又容易被忽视的家伙——事件调度器（Event Scheduler）。这玩意儿，用好了能让你轻松实现定时任务，摆脱手动执行SQL的苦海。而要玩转它，理解AT和EVERY这两个参数至关重要。今天咱们就来好好扒一扒它们，保证你听完之后，能像指挥交通一样调度你的数据库事件！

开场白：事件调度器是个啥？

简单来说，事件调度器就像一个数据库里的“闹钟”，你设定好时间，让它到点自动执行一些SQL语句。这在很多场景下都很有用，比如定期清理过期数据、生成报表、备份数据库等等。

AT参数：精确打击，准时赴约

AT参数，就像一个严谨的管家，一丝不苟地按照你指定的时间执行任务。它只执行一次，执行完就功成身退，不再回来。

语法格式：

CREATE EVENT event_name
ON SCHEDULE AT timestamp
DO
  sql_statement;

event_name: 事件的名字，随便你起，只要不和其他事件重名就行。
timestamp: 事件执行的具体时间点。这个时间点必须是未来的时间，不能是过去的！
sql_statement: 要执行的SQL语句，可以是单条语句，也可以是BEGIN...END块包含的多条语句。

举个栗子：

假设我们想在2024年12月31日晚上11点59分59秒，给所有用户发送一条新年祝福短信。

CREATE EVENT send_new_year_message
ON SCHEDULE AT '2024-12-31 23:59:59'
DO
  INSERT INTO message_queue (user_id, message)
  SELECT id, '新年快乐！祝您在新的一年里万事如意！'
  FROM users;

这行代码的意思是：创建一个名为 send_new_year_message 的事件，让它在 2024年12月31日23点59分59秒执行一条SQL语句，这条SQL语句会将新年祝福插入到 message_queue 消息队列表中，准备发送给所有用户。

注意事项：

timestamp 的格式一定要正确，MySQL支持多种时间格式，常用的有 'YYYY-MM-DD HH:MM:SS' 和 YYYYMMDDHHMMSS。
AT 后面必须跟一个具体的时间点，不能是相对时间，比如 "明天早上8点" 这种说法是不行的。
事件一旦创建，就会按照你设定的时间执行。如果时间已经过去了，事件就不会执行。

EVERY参数：循环往复，永不停歇

EVERY参数，则像一个勤劳的闹钟，每隔一段时间就会响一次，直到你手动关闭它。它用于执行周期性的任务。

语法格式：

CREATE EVENT event_name
ON SCHEDULE EVERY interval
STARTS timestamp
ENDS timestamp
DO
  sql_statement;

interval: 时间间隔，指定事件执行的频率。
STARTS timestamp: 事件开始执行的时间点（可选）。如果省略，则事件在创建后立即开始执行。
ENDS timestamp: 事件结束执行的时间点（可选）。如果省略，则事件会一直执行下去，直到你手动禁用或删除它。

interval 的格式：

interval 由一个数字和一个单位组成，例如：

1 DAY: 每天
1 HOUR: 每小时
30 MINUTE: 每30分钟
5 SECOND: 每5秒

举个栗子：

假设我们想每隔1小时备份一次数据库。

CREATE EVENT backup_database
ON SCHEDULE EVERY 1 HOUR
STARTS '2024-01-01 00:00:00'  -- 可选，指定开始时间
DO
  -- 这里替换成你的数据库备份命令，例如：
  -- ! mysqldump -u root -p你的密码 你的数据库名 > /path/to/backup/backup.sql
  SELECT 'Database backup executed' as message; -- 仅为示例，实际应执行备份命令

这行代码的意思是：创建一个名为 backup_database 的事件，让它从 2024年1月1日0点0分0秒开始，每隔1小时执行一次数据库备份操作。

再举个栗子，带 ENDS 参数：

假设我们想从今天开始，每天凌晨3点清理一次日志，但只清理一个月。

CREATE EVENT cleanup_logs
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL (HOUR(CURRENT_TIMESTAMP) - 3 + 24) % 24 HOUR -- 确保在凌晨3点开始
ENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH
DO
  -- 这里替换成你的日志清理SQL语句，例如：
  -- DELETE FROM logs WHERE log_time < DATE_SUB(NOW(), INTERVAL 30 DAY);
  SELECT 'Logs cleaned' as message; -- 仅为示例，实际应执行清理命令

这个例子稍微复杂一点，STARTS 使用了 CURRENT_TIMESTAMP 和 INTERVAL 来计算下一个凌晨3点的时间点。ENDS 则指定事件在一个月后结束。

注意事项：

EVERY 后面必须跟一个时间间隔，不能是具体的时间点。
STARTS 和 ENDS 都是可选的，可以根据实际需求来决定是否使用。
如果没有指定 ENDS，事件会一直执行下去，直到你手动禁用或删除它。

AT vs EVERY: 对比分析，一目了然

为了方便大家理解，我们用一张表格来对比一下 AT 和 EVERY 这两个参数：

特性	`AT`	`EVERY`
执行次数	一次	周期性，多次
时间指定方式	具体的时间点	时间间隔
`STARTS`参数	不支持	支持
`ENDS`参数	不支持	支持
适用场景	执行一次性的任务，例如发送特定时间的通知	执行周期性的任务，例如备份数据库、清理日志

事件状态管理：开启、禁用、删除

创建好事件之后，你可能需要对事件的状态进行管理，例如开启、禁用、删除等。

查看事件状态：

SHOW EVENTS;

这条命令会显示所有事件的信息，包括事件名、调度方式、状态等。

启用事件：

ALTER EVENT event_name ENABLE;

禁用事件：

ALTER EVENT event_name DISABLE;

删除事件：

DROP EVENT event_name;

重要提示：

在执行 ALTER EVENT 或 DROP EVENT 命令之前，请务必确认事件名是否正确，以免误操作。
禁用事件只是暂停事件的执行，事件仍然存在于数据库中。删除事件则会永久移除事件。

实战演练：模拟一个每日报表生成任务

现在，让我们来模拟一个实际的应用场景：每天凌晨1点，生成一份销售报表，并保存到文件中。

创建报表存储表：

CREATE TABLE daily_sales_report (
  report_date DATE PRIMARY KEY,
  total_sales DECIMAL(10, 2),
  num_orders INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

创建事件：

CREATE EVENT generate_daily_sales_report
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL (HOUR(CURRENT_TIMESTAMP) - 1 + 24) % 24 HOUR
DO
  BEGIN
    -- 计算昨天的销售额和订单数量
    SET @yesterday = DATE(DATE_SUB(NOW(), INTERVAL 1 DAY));
    SET @total_sales = (SELECT SUM(amount) FROM orders WHERE DATE(order_time) = @yesterday);
    SET @num_orders = (SELECT COUNT(*) FROM orders WHERE DATE(order_time) = @yesterday);

    -- 插入报表数据
    INSERT INTO daily_sales_report (report_date, total_sales, num_orders)
    VALUES (@yesterday, @total_sales, @num_orders)
    ON DUPLICATE KEY UPDATE total_sales = @total_sales, num_orders = @num_orders; -- 如果已经存在当天的报表，则更新数据

    -- (可选) 可以将报表数据导出到文件，例如：
    -- SELECT * FROM daily_sales_report WHERE report_date = @yesterday
    -- INTO OUTFILE '/path/to/report/daily_sales_report.csv'
    -- FIELDS TERMINATED BY ','
    -- ENCLOSED BY '"'
    -- LINES TERMINATED BY 'n';

    SELECT 'Daily sales report generated' as message;  -- 仅为示例，实际应执行导出操作
  END;

这个事件会每天凌晨1点执行以下操作：

计算昨天的销售额和订单数量。
将报表数据插入到 daily_sales_report 表中。
(可选) 将报表数据导出到CSV文件。

启用事件：

ALTER EVENT generate_daily_sales_report ENABLE;

权限问题：

要创建和管理事件，你需要具有相应的权限。通常，你需要 EVENT 权限。如果遇到权限问题，请联系你的数据库管理员。

总结：

AT 和 EVERY 是 MySQL 事件调度器中两个非常重要的参数，它们分别用于执行一次性和周期性的任务。理解它们的用法和区别，可以帮助你更好地利用事件调度器来自动化数据库管理任务，提高工作效率。

最后，温馨提示：

在生产环境中使用事件调度器时，请务必进行充分的测试，确保事件能够按照预期执行。
监控事件的执行情况，及时发现和解决问题。
合理规划事件的调度时间，避免对数据库性能造成过大的影响。

好了，今天的讲座就到这里。希望大家能够掌握 AT 和 EVERY 的用法，让事件调度器成为你数据库管理的得力助手！如果有什么问题，欢迎随时提问，咱们下次再见！

发表回复 取消回复

发表回复取消回复