推荐
评论
收藏

MySql定时任务详解

2021-10-17 3282次浏览

在日常开发中经常会遇到这样的需求：每天或者每小时定时执行某些代码逻辑去操作数据；我之前在处理这些的时候，如果是JAVA开发，直接在代码中开启一个线程或者使用框架提供的定时任务配置就可以；如果是PHP则使用系统的定时任务来定时调用php脚本来实现。

今天了解到在MySql数据库里面，也给我们提供了可以实现定时功能：自MySQL5.1.6起，增加了一个功能：事件调度器（Event Scheduler），使用它可以定时执行某些特定任务（如：数据分析、数据汇总、数据备份等等），来取代我们之前实现定时任务的方法（语言自身提供的定时任务、操作系统定时任务）。此外对于MySQL的事件调度器，它在时间维度上可以精确到每秒钟执行一个任务，而操作系统的计划任务（如：Linux的crontab或Windows下的任务计划）只能精确到每分钟执行一次。对于一些对数据实时性要求比较高的应用（例如：股票、赔率、比分等）就非常适合，正好整理下分享出来。

MySql事件调度器的开启与关闭

检测MySql事件调度器是否开启

在使用这个功能之前必须确保event_scheduler已开启：

查看事件是否开启：SHOW VARIABLES LIKE '%event_sche%'; 或者 SELECT @@event_scheduler;
查看所有事件任务：SHOW EVENTS ;

开启MySql事件调度器

临时开启：SET GLOBAL event_scheduler = 1; SET GLOBAL event_scheduler = ON;
永久开启：在配置my.cnf文件中加上 event_scheduler = 1，也可以直接在启动命令加上“--event_scheduler=1”，例如：mysqld ... --event_scheduler=1
开启事件任务：ALTER EVENT eventName ON COMPLETION PRESERVE ENABLE;

关闭MySql事件调度器

临时关闭：SET GLOBAL event_scheduler = 0;
永久关闭：去掉配置文件中的 event_scheduler = 1 配置或者设置为：event_scheduler = 0
关闭事件任务： ALTER EVENT eventName ON COMPLETION PRESERVE DISABLE;

创建MySql事件调度器（MySql定时任务、MySql事件）

CREATE EVENT [IFNOT EXISTS] event_name
    　　 ONSCHEDULE schedule
    　　 [ONCOMPLETION [NOT] PRESERVE]
    　　 [ENABLE | DISABLE]
    　　 [COMMENT 'comment']
    　　 DO sql_statement;
    
    schedule:
　　 AT TIMESTAMP [+ INTERVAL INTERVAL]
　　 | EVERY INTERVAL [STARTS TIMESTAMP] [ENDS TIMESTAMP]
　　
　　INTERVAL:
　　 quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
　　 WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
　　 DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

DEFINER：可选，用于定义事件执行时检查权限的用户。
IF NOT EXISTS：可选项，用于判断要创建的事件是否存在。
EVENT event_name：必选，用于指定事件名，event_name的最大长度为64个字符，如果为指定event_name，则默认为当前的MySQL用户名（不区分大小写）
ON SCHEDULE schedule：必选，用于定义执行的时间和时间间隔
ON COMPLETION [NOT] PRESERVE：可选，用于定义事件是否循环执行，即是一次执行还是永久执行，默认为一次执行，即 NOT PRESERVE
ENABLE | DISABLE | DISABLE ON SLAVE：可选项，用于指定事件的一种属性。其中，关键字ENABLE表示该事件是活动的，也就是调度器检查事件是否必选调用；关键字DISABLE表示该事件是关闭的，也就是事件的声明存储到目录中，但是调度器不会检查它是否应该调用；关键字DISABLE ON SLAVE表示事件在从机中是关闭的。如果不指定这三个选择中的任意一个，则在一个事件创建之后，它立即变为活动的。
COMMENT 'comment'：可选，用于定义事件的注释
DO event_body：必选，用于指定事件启动时所要执行的代码。可以是任何有效的SQL语句、存储过程或者一个计划执行的事件。如果包含多条语句，可以使用BEGIN...END复合结构

在event事件中：ON SCHEDULE 计划任务，有两种设定计划任务的方式：AT 时间戳（用来完成单次的计划任务）、EVERY 时间（单位）的数量时间单位[STARTS 时间戳] [ENDS时间戳]（用来完成重复的计划任务）。在两种计划任务中，时间戳可以是任意的TIMESTAMP 和DATETIME 数据类型，时间戳需要大于当前时间。在重复的计划任务中，时间（单位）的数量可以是任意非空（Not Null）的整数式，时间单位是关键词：YEAR，MONTH，DAY，HOUR，MINUTE 或者SECOND。

TIMESTAMP：表示一个具体的时间点，后面加上一个时间间隔，表示在这个时间间隔后事件发生。
EVERY：用于表示事件在指定时间区间内每隔多长时间发生一次，其中 STARTS用于指定开始时间；ENDS用于指定结束时间。
INTERVAL：表示一个从现在开始的时间，其值由一个数值和单位构成。例如，使用“4 WEEK”表示4周；使用“‘1:10’ HOUR_MINUTE”表示1小时10分钟。

修改MySql事件调度器（MySql定时任务、MySql事件）

ALTER EVENT event_name
    　　 [ONSCHEDULE schedule]
    　　 [RENAME TOnew_event_name]
    　　 [ON COMPLETION [NOT] PRESERVE]
    　　 [COMMENT 'comment']
    　　 [ENABLE | DISABLE]
    　　 [DO sql_statement]

删除MySql事件调度器（MySql定时任务、MySql事件）

DROP EVENT [IF EXISTS] event_name

MySql事件调度器操作实例

创建一个订单销售统计表：

CREATE TABLE `sale_statistic` (
  `sale_stat_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `sale_stat_total` decimal(19,2) unsigned DEFAULT '0.00' COMMENT '订单销售总额',
  `sale_stat_time` datetime DEFAULT NULL COMMENT '统计时间',
  PRIMARY KEY (`sale_stat_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='订单销售统计';

创建定时任务（MySql事件调度器）：每天统计销售订单的总额，然后插入到统计表中：首先创建一个定时任务，如果操作逻辑简单，可以创建存储过程。

DELIMITER //
DROP PROCEDURE IF EXISTS sale_statistic //
CREATE PROCEDURE `sale_statistic`()
BEGIN
DECLARE total decimal(12,0) default 0.0;
select sum(eorder_price_total) into total from ebay_order where eorder_payment_date >= date_format(now(), '%Y-%m-%d 00:00:00') and eorder_payment_date < DATE_ADD(date_format(now(), '%Y-%m-%d 00:00:00'),interval 1 day);
INSERT INTO `sale_statistic` (`sale_stat_total`, `sale_stat_time`) VALUES (total, NOW());
END

创建一个每天执行的 MySql事件调度器：sale_statistic

DROP EVENT IF EXISTS sale_statistic
CREATE EVENT sale_statistic
ON SCHEDULE EVERY 1 DAY
DO
BEGIN
CALL sale_statistic();
END

扩展知识

常用的定时参数：

每隔1秒钟执行事件：ON SCHEDULE EVERY 1 SECOND
5天后执行事件：ON SCHEDULE AT CURRENT_TIMESTAMP+INTERVAL 5 DAY
2020年7月20日12点整执行事件：ON SCHEDULE AT TIMESTAMP '2020-07-20 00:00:00'
5天后开始每天执行事件：ON SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP+INTERVAL 5 DAY
每天定时执行事件，5天后停止执行：ON SCHEDULE EVERY 1 DAY ENDS CURRENT_TIMESTAMP+INTERVAL 5 DAY
5天后开始每天定时执行事件，一个月后停止执行：ON SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP+INTERVAL 5 DAY ENDS CURRENT_TIMESTAMP+INTERVAL 1 MONTH
每天定时执行事件(只执行一次，任务完成后就终止该事件)：ON SCHEDULE EVERY 1 DAY ON COMPLETION NOT PRESERVE
每两分钟执行1次：on schedule every 2 minute
每3天执行1次：on schedule every 3 day