MySQL事件(1)

来源：互联网发布：简明python教程 chm 编辑：程序博客网时间：2024/05/14 03:28

1.事件是MySQL在相应的时刻调用的过程式数据库对象。
2.一个事件只能调用一次，一个事件也可以周期性的调用，可以在特定的时间调用。
3.MySQL调度器负责调度事件，要创建事件必须打开调度器：

set global event_scheduler=true;

关闭调度器：

set global event_scheduler=false;

4.创建一个事件记录表来记录事件的执行：

create table event_invoke(event_name varchar(20),event_start timestmap)

5.创建一个立即启动的事件：

create event direct on schedule at now()do insert into event_invoke values('direct',now());

创建一个在2016年3月1号上午11点执行的事件：

create event direct2 on schedule at '2016-03-01  11:00:00'do insert into event_invoke values('direct_20160301',now());

创建一个每三天执行一次的事件：

create event direct3 on schedule at now()+interval 3 daydo insert into event_invoke values('threedays',now());

创建一个在下周日执行的事件：

create event_sunday on schedule at case dayname(now())when 'Sunday' then now() + interval 7 daywhen 'Monday'then now() + interval 6 daywhen 'Tuesday' then now() + interval 5 daywhen 'Wednesday'then now() + interval 4 daywhen 'Thursday'then now() + interval 3 daywhen 'Friday'then now() + interval 2 daywhen 'Saturday'then now() + interval 1 daydo insert into event_invoke values('next_sunday',now());

以上可以简化为：

create event next_sunday on schedule at now()+interval (8-dayofweek(now())) daydo insert into event_invoke values('next_sunday',now());

创建一个明天上午10点执行的事件：

create event tomorrow10 on schedule at timestamp(curdate() + interval 1 day,'10:00:00') do insert into event_invoke values('tomorrow',now());

创建一个事件在3小时后启动并且每2小时运行一次直到晚上11点：

create event every2hour on schedule every 2 hour starts now()+3 hour  ends curdate()+interval 23 hourdo insert into event_invoke values('every2hour',now());

创建一个事件，明天中午12点运行，并且每分钟运行一次，一共运行6次：

create event sixtime on schedule every 1 minutestarts timestamp(curdate()+interval 1 day ,'12:00:00')ends timestamp(curdate + interval 1 day,'12:00:00')+interval 5 minute do insert into event_invoke values('_sixtimes',now());

创建一个事件他在Sunday启动并且持续后面的四个周:

create event fivesundays starts case dayname(now())when 'Sunday' then now() when 'Monday'then now() + interval 6 daywhen 'Tuesday' then now() + interval 5 daywhen 'Wednesday'then now() + interval 4 daywhen 'Thursday'then now() + interval 3 daywhen 'Friday'then now() + interval 2 daywhen 'Saturday'then now() + interval 1 dayend ends case dayname(now()) when 'Sunday' then now() when 'Monday'then now() + interval 6 daywhen 'Tuesday' then now() + interval 5 daywhen 'Wednesday'then now() + interval 4 daywhen 'Thursday'then now() + interval 3 daywhen 'Friday'then now() + interval 2 daywhen 'Saturday'then now() + interval 1 dayend + interval 4 week do insert into event_invoke values('next_4_sunday',now());

代码来源：MySQL开发者SQL权威指南

0 0