mysql定时器是系统给提供了event,而oracle里面的定时器是系统给提供的job。
定时器一般完成定期执行的时间的方法,在很多业务场景中都可能会用到,使用MySQL的定时器会比较方便编程,架构更加清晰,减轻webApp的压力。
一、 如何使用event 前提
需要开启全局event定时器,启用事件。
1. 查看event是否开启
SHOW VARIABLES LIKE 'event_scheduler';
2. 开启/关闭全局 event 定时器
SET GLOBAL event_scheduler = 1; -- 开启 = ON
SET GLOBAL event_scheduler = 0; -- 关闭 = OFF
3. 查看所有的 event
select * from mysql.event;
4. 删除 event
drop event event_name;
启用/禁用事件
可以在创建的同时设置到期状态:ON COMPLETION PRESERVE
— 当event到期了,event会被disable,但是该event还是会存在ON COMPLETION NOT PRESERVE
— 当event到期的时候,该event会被自动删除掉
-- 启用 stored_procedure_name 事件
ALTER EVENT stored_procedure_name ON COMPLETION PRESERVE ENABLE;
-- 禁用 stored_procedure_name 事件
ALTER EVENT stored_procedure_name DISABLE;
二、 定义 event 事件
创建事件
CREATE EVENT 的语法如下:
CREATE EVENT
[IF NOT EXISTS] ——————————————————————-标注1
event_name ——————————————————————————-标注2
ON SCHEDULE schedule ——————————————————标注3
[ON COMPLETION [NOT] PRESERVE] ————————-标注4
[ENABLE | DISABLE] ————————————————————标注5
[COMMENT ‘comment’] ———————————————————标注6
DO sql_statement ———————————————————————-*标注7
标注1:使用IF NOT EXISTS,只有在同名event不存在时才创建,否则忽略。建议不使用以保证event创建成功。
标注5:参数Enable和Disable表示设定事件的状态。Enable表示系统将执行这个事件。Disable表示系统不执行该事件。
标注6:注释会出现在元数据中,它存储在information_schema表的COMMENT列,最大长度为64个字节。
‘comment’表示将注释内容放在单引号之间,建议使用注释以表达更全面的信息。
标注7:sql_statement字段表示该event需要执行的SQL语句或存储过程。
命名规则
名称最大长度可以是64个字节。名字必须是当前Dateabase中唯一的,同一个数据库不能有同名的event。
使用event常见的工作是创建表、插入数据、删除数据、清空表、删除表。
为了避免命名规范带来的不便,最好让事件名称具有描述整个事件的能力。建议命名规则如下为:动作名称(INTO/FROM)表名_TIME,例如:
-- 1.每月创建(清空/删除)fans表
create(truncate/drop) _table_fans_month;
-- 2.每天从fans表插入(删除)数据:
insert(delete) _into (from) _fans_day;
每天执行一次
这里引用我之前写的一篇关于存储过程的文章里的一个存储过程,代码如下:
-- 定义名为 stored_procedure_name 的存储过程
CREATE PROCEDURE `db_name`.`stored_procedure_name`()
NOT DETERMINISTIC
COMMENT '定时任务存储过程'
BEGIN
insert `out_table`(`date`,`id`,`amount`) (
select date,id,sum(amount) as amount from original_table
where
-- 获取每天的情况
to_days(date) = to_days(now())
group by id
);
END
定义事件,调用 stored_procedure_name ()
过程:
CREATE DEFINER=`root`@`localhost` EVENT `event_timing`
-- 每1天的00:00:00调用,从2018-12-19开始
ON SCHEDULE EVERY 1 DAY STARTS '2018-12-19 00:00:00'
ON COMPLETION NOT PRESERVE ENABLE
DO CALL `stored_procedure_name`();
这样你的数据库就会每天统计一次数据插入到新的表中。
每个月的一号凌晨1 点执行mount_1_point()存储过程:
CREATE EVENT NOT EXISTS mount_1_point
ON SCHEDULE EVERY 1 MONTH
STARTS DATE_ADD(DATE_ADD(
DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 1 MONTH),
INTERVAL 1 HOUR)
ON COMPLETION PRESERVE ENABLE
DO
BEGIN
CALL mount_1_point();
END
定时到某个时间之后事件终止
-- 每天定时统计表,5天后开始执行,一个月后停止:
CREATE EVENT event_timing_5_day
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY
ENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH
DO CALL `stored_procedure_name`();
三、 定时任务 SCHEDULE 详解schedule
1. AT 时间戳
2. EVERY 时间
EVERY 时间(单位)的数量时间单位[STARTS 时间戳] [ENDS时间戳],用来完成重复的计划任务。
在两种计划任务中,时间戳可以是任意的TIMESTAMP 和DATETIME 数据类型,时间戳需要大于当前时间。
在重复的计划任务中,时间(单位)的数量可以是任意非空(Not Null)的整数式,时间单位是关键词:YEAR,MONTH,DAY,HOUR,MINUTE 或者SECOND。
提示: 其他的时间单位也是合法的如:QUARTER、 WEEK、 YEAR_MONTH、DAY_HOUR、DAY_MINUTE、DAY_SECOND、HOUR_MINUTE、HOUR_SECOND、 MINUTE_SECOND,不建议使用这些不标准的时间单位。
(季度、周、)
四、 含命令行的存储过程
注意,创建存储过程的时候,如果采用命令行的方式,需要先修改命令结束符,将分号改成其他的符号
-- 设置分隔符为 '$$' ,mysql默认的语句分隔符为 ';' ,
-- 这样在后续的 create 到 end 这段代码都会看成是一条语句来执行
DELIMITER $$
//创建存储过程或者事件语句
//结束
$$
将语句分割符设置回 ';'
DELIMITER ;
五、 懒人的做法 使用Navicat图形化界面
好久没去写sql,语法都快忘光了,然而借助工具还是很容易做出定时器的。这里采用Navicat for mysql: