mysql-常用组件之定时器

定时器主要用于定时的执行一次或者循环执行一条sql，在实际场景上，例如，定期清理数据表，定期导出日志文件等等场景。本次公司晚上维护系统，晚上需要定期挂维护页，用到了定时器，这里简单总结一下。

启用定时器

MySQL event 功能默认是关闭的，可以使用下面的语句来看 event 的状态，如果是 OFF 或者 0，表示是关闭的。

• 检查命令

SHOW VARIABLES LIKE 'event_scheduler';

• 定时器已经开启的执行结果：

如果未开启定时器，执行以下命令开启定时器：

SET GLOBAL event_scheduler = 1;

注意：虽然这里用 set global event_scheduler = on 语句开启了事件，但是每次重启服务器或重启 mysql 服务后，事件会自动关闭（event_scheduler=OFF），所以想让事件一直保持开启，最好修改配置文件，让 mysql 服务启动的时候开启时间，只需要在 my.cnf 配置文件的 [mysqld] 部分加上 event_scheduler=ON 即可:

关闭定时器命令：

SET GLOBAL event_scheduler = 0;

创建定时器

创建定时器 SQL 模板：

DELIMITER $$
 
-- SET GLOBAL event_scheduler = ON$$     -- required for event to execute but not create    
 
CREATE	/*[DEFINER = { user | CURRENT_USER }]*/	EVENT `dbName`.`eventName` -- 事件名
 
ON SCHEDULE
	 /* uncomment the example below you want to use */
 
	-- scheduleexample 1: run once 只执行一次
 
	   --  AT 'YYYY-MM-DD HH:MM.SS'/CURRENT_TIMESTAMP { + INTERVAL 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...] }
 
	-- scheduleexample 2: run at intervals forever after creation 创建完后周期性执行
 
	   -- EVERY 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...]
 
	-- scheduleexample 3: specified start time, end time and interval for execution 指定开始和结束时间，并在期间内周期性的执行
	   /*EVERY 1  [HOUR|MONTH|WEEK|DAY|MINUTE|...]
 
	   STARTS CURRENT_TIMESTAMP/'YYYY-MM-DD HH:MM.SS' { + INTERVAL 1[HOUR|MONTH|WEEK|DAY|MINUTE|...] }
 
	   ENDS CURRENT_TIMESTAMP/'YYYY-MM-DD HH:MM.SS' { + INTERVAL 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...] } */
 
/*[ON COMPLETION [NOT] PRESERVE] -- 时间完成后是否删除事件
[ENABLE | DISABLE] -- 是否立即生效
[COMMENT 'comment']*/
 
DO
	BEGIN
	    (sql_statements) -- 需要周期性执行的 sql 语句
	END$$
 
DELIMITER ;

• 注意：

1. DELIMITER $$ 这个是将mysql中以 ; 结尾的规定修改为以 $$ 为语句结束符，因为存储过程里可以有多条 sql 语句，里面的 sql 语句都以 ; 结尾，如果回车了那么系统会当做 sql 语句直接执行了，我们希望的是先定义这一系列 sql 语句而先不执行，所以要改下操作结束符。当然你在改后一定要改回来，大家可以看到最后一行有对应的修改回来的语句。

2. 如果需要周期性执行的语句也可用存储过程来代替，使用 DO CALL procedure() 语句来代替。

3. 设置定时器事件的生效与否语句：

ALTER EVENT event_minute DISABLE;  # 关闭事件
ALTER EVENT event_minute ENABLE;   # 启用事件

举例

1.从现在开始每隔九天定时执行

ON SCHEDULE EVERY 9 DAY STARTS NOW() ；

2.每个月的一号凌晨 1 点执行

on schedule every 1 month starts date_add(date_add(date_sub(curdate(),interval day(curdate())-1 day),interval 1 month),interval 1 hour);

3.每个季度一号的凌晨1点执行

on schedule every 1 quarter starts date_add(date_add(date(concat(year(curdate()),'-',elt(quarter(curdate()),1,4,7,10),'-',1)),interval 1 quarter),interval 1 hour);