mysql - event事件/执行计划/自动执行

目录

一、创建event事件详解

二、事件调试器

三、事件调度案例

---

 

一、创建event事件详解

1.基本概念

mysql5.1版本开始引进event概念。event既“时间触发器”，与triggers的事件触发不同，event类似与linux crontab计划任务，用于时间触发。通过单独或调用存储过程使用，在某一特定的时间点，触发相关的SQL语句或存储过程。

 

2.适用范围

对于每隔一段时间就有固定需求的操作，如创建表，删除数据等操作，可以使用event来处理。

 

3.使用权限

单独使用event调用SQL语句时，查看和创建需要用户具有event权限，调用该SQL语句时，需要用户具有执行该SQL的权限。Event权限的设置保存在mysql.user表和mysql.db表的Event_priv字段中。

 

当event和procedure配合使用的时候，查看和创建存储过程需要用户具有create routine权限，调用存储过程执行时需要使用excute权限，存储过程调用具体的SQL语句时，需要用户具有执行该SQL的权限。

查看EVENT命令有如下几种：

（1）查询mysql.event表；

（2）通过SHOW EVENTS命令；

（3）通过SHOW FULL EVENTS命令；

（4）通过查询information_schema.events表

（5）SHOW CREATE EVENT。

总之，event的使用频率较低建议使用root用户进行创建和维护。

 

4.基本语法

4.1 开启定时器

要使event起作用，MySQL的常量GLOBAL event_scheduler必须为on或者是1。

(1)查看是否开启定时器：SHOW VARIABLES LIKE 'event_scheduler';

(2) 开启定时器 ：SET GLOBAL event_scheduler = 1;

 

4.2 创建事件

CREATE

CREATE EVENT

[IF NOT EXISTS]

event_name

ON SCHEDULE schedule --------------------------------*标注3

[ON COMPLETION [NOT] PRESERVE] ---- PRESERVE是使事件在执行完毕后不会被Drop掉

[ENABLE | DISABLE] -------------------------------------- 表示设定事件的状态，Disable表示不执行

[COMMENT 'comment'] ---------------------------------- 添加注释，注释会出现在元数据中

DO sql_statement ----------------------------------------- 需要执行的SQL语句或存储过程

 

ON SCHEDULE 计划任务，有两种设定计划任务的方式：

1. AT 时间戳，用来完成单次的计划任务。

2. 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，不建议使用这些不标准的时间单位。

 

4.3 修改事件

使用ALTER EVENT 来修改事件，具体的ALTER语法如下，与创建事件的语法类似：

ALTER EVENT

event_name

ON SCHEDULE schedule

[RENAME TO new_event_name]

[ON COMPLETION [NOT] PRESERVE]

[ENABLE | DISABLE]

[COMMENT 'comment']

DO sql_statement

 

4.4 删除事件

EVENT使用DROP EVENT语句来删除已经创建的事件，语法如下：

DROP EVENT [IF EXISTS] event_name

 

二、事件调试器

1.查看事件调度器状态

SHOW VARIABLES LIKE 'event_scheduler';

SELECT @@event_scheduler;

SHOW PROCESSLIST;

 

2.开启事件调试器

mysql event_scheduler

开启event_scheduler sql指令：

SET GLOBAL event_scheduler = ON;

SET @@global.event_scheduler = ON;

SET GLOBAL event_scheduler = 1;

SET @@global.event_scheduler = 1;

相反，关闭event_scheduler指令：

 

3.关闭事件调度器

SET GLOBAL event_scheduler = OFF;

SET @@global.event_scheduler = OFF;

SET GLOBAL event_scheduler = 0;

SET @@global.event_scheduler = 0;

 

4.查看自动调度事件有哪些

select * from mysql.event

 

三、事件调度案例

1.立即执行

create event event_now  

on schedule  

at now()  

do insert into events_list values('event_now', now()); 

 

2.每5秒执行

CREATE EVENT IF NOT EXISTS event_test1

on schedule  EVERY 5 SECOND 

starts now()   

ON COMPLETION PRESERVE

do call proc_test_02();

 

3.每分执行

create event test.event_minute  

on schedule  

every  1 minute    

do insert into events_list values('event_now', now());  

 

4.每天执行

CREATE EVENT IF NOT EXISTS update_*_event

ON SCHEDULE  every 1 DAY 

STARTS TIMESTAMP(CURRENT_DATE,'00:05:00')

ON COMPLETION PRESERVE ENABLE

DO CALL update_*_proc(); 

 

5.每月执行

每个月的一号凌晨1 点执行STAT()存储过程

CREATE  EVENT  NOT EXISTS  STAT

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 STAT();

END;

 

6.指定时间差

相差8小时，注意时区

drop EVENT if exists yida_sts.event_ebay_sold_day_trc;

CREATE EVENT IF NOT EXISTS yida_sts.event_ebay_sold_day_trc

ON SCHEDULE  date_add(date(curdate() + 1),interval 8 hour) 

STARTS  TIMESTAMP'2017-01-26 1:35:00'

ON COMPLETION PRESERVE ENABLE

DO CALL yida_sts.yd_ebay_sold_day_trc(0);

 

 

四、自动执行job计划

1.job计划时间设置

每天定时执行 

例如：每天的凌晨1点执行 

Interval => TRUNC(sysdate) + 1 +1/ (24)

每周定时执行 

例如：每周一凌晨1点执行 

Interval => TRUNC(next_day(sysdate,'星期一'))+1/24

每月定时执行 

例如：每月1日凌晨1点执行 

Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24

每季度定时执行 

例如每季度的第一天凌晨1点执行 

Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24

每半年定时执行 

例如：每年7月1日和1月1日凌晨1点 

Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+1/24

每年定时执行 

例如：每年1月1日凌晨1点执行 

Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+1/24

 

2.job计划设置步骤

(1)第一步：提交job计划

-- 每天零晨1点执行: TRUNC(sysdate) + 1 +1/ (24)    

dbms_job.submit(job1, 'proc_jobs_test;', sysdate, 'TRUNC(sysdate) + 1 +1/ (24)');

(2)查询job计划id

select * from user_jobs;

(3)第二步:执行job计划

-- 注:必须执行此命令,设置的job才会被激活执行,否则不会自动调用执行

begin

   dbms_job.run(1019);     -- job_id可以通过 查询得到

end; 

/

 

3.删除一个job

begin

   dbms_job.remove(1019);--和select * from user_jobs; 中的job值对应，看what对应的过程

end;  

/