mysql简述事件的用途_mysql事件的应用举例

############### 首先设置环境变量 ##################### mysql> show variables like 'event_%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | OFF | +-----------------+-------+ 1 row in set (0.03 sec) mysql> set global event_scheduler=ON; Query OK, 0 rows affected (0.02 sec) mysql> ############### 创建测试用表 ##################### create table event_invoke (event_name varchar(20) not null, event_started timestamp not null); @@@@@@@@@@@@@@@@@@@@@@@@@@@ mysql> use test; Database changed mysql> desc event_invoke; +---------------+-------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +---------------+-------------+------+-----+-------------------+-----------------------------+ | event_name | varchar(20) | NO | | NULL | | | event_started | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +---------------+-------------+------+-----+-------------------+-----------------------------+ 2 rows in set (0.02 sec) mysql> ########### 单个调度 ################## create event test on schedule at now() do insert into event_invoke values ('test',now()); mysql> select * from event_invoke; +------------+---------------------+ | event_name | event_started | +------------+---------------------+ | test | 2009-11-04 00:45:55 | +------------+---------------------+ 1 row in set (0.00 sec) mysql> @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ drop event if exists test; create event test on schedule at '2009-11-17 10:12:00' do insert into event_invoke values ('test',now()); mysql> select now(); +---------------------+ | now() | +---------------------+ | 2009-11-17 10:12:19 | +---------------------+ 1 row in set (0.00 sec) mysql> select * from event_invoke; +------------+---------------------+ | event_name | event_started | +------------+---------------------+ | test | 2009-11-04 00:45:55 | | test | 2009-11-17 10:12:00 | +------------+---------------------+ 2 rows in set (0.00 sec) ############### 间隔固定时间调度 ################ drop event if exists test; create event test on schedule at now() + interval 3 minute do insert into event_invoke values ('fenzhong',now()); @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ drop event if exists test; create event test on schedule at now() + interval 3 day do insert into event_invoke values ('tian',now()); @@@@@@@@@@@@@@@@@@@@@@@@ drop event if exists test; create event test on schedule at now() + interval 3 hour do insert into event_invoke values ('xiaoshi',now()); @@@@@@@@@@@@@@@@@@@@@@@@@ mysql> select now(); +---------------------+ | now() | +---------------------+ | 2009-11-17 10:32:01 | +---------------------+ 1 row in set (0.00 sec) mysql> select now(); +---------------------+ | now() | +---------------------+ | 2009-11-17 10:35:07 | +---------------------+ 1 row in set (0.00 sec) mysql> select * from event_invoke; +------------+---------------------+ | event_name | event_started | +------------+---------------------+ | test | 2009-11-04 00:45:55 | | test | 2009-11-17 10:12:00 | | test | 2009-11-17 10:34:42 | +------------+---------------------+ 3 rows in set (0.00 sec) mysql> ########################################### 下周日启动的事件 ########################### drop event if exists test; create event test on schedule at case dayname(now()) when 'Sunday' then now() + interval 7 day when 'Monday' then now() + interval 6 day when 'Tuesday' then now() + interval 5 day when 'Wednesday' then now() + interval 4 day when 'Thursday' then now() + interval 3 day when 'Friday' then now() + interval 2 day when 'Saturday' then now() + interval 1 day end do insert into event_invoke values ('next_sunday',now()); @@@@@@@@@@@@@@@@@@@@@@@@@@@ drop event if exists test; create event test on schedule at now() + interval (8-dayofweek(now())) day do insert into event_invoke values ('next_sunday',now()); ################# 明天11点启动的事件 ################### drop event if exists test; create event test on schedule at timestamp(curdate()+interval 1 day,'11:00:00') do insert into event_invoke values ('next_sunday',now()); ############################################################## @@@@@@@ starts------ends 用法 @@@@@@@ ############################################################### ################ 每2小时调用一次直到晚上11点 ################# drop event if exists test; create event test on schedule every 2 hour starts now()+interval 3 hour ends curdate()+interval 23 hour do insert into event_invoke values ('every_2_hour',now()); ########## 每天中午12点调用，并且每分钟调用一次，一共调用6次 ####### drop event if exists test; create event test on schedule every 1 minute starts timestamp(curdate()+interval 1 day,'12:00:00') ends timestamp (curdate()+interval 1 day,'12:00:00') +interval 5 mnute do insert into event_invoke values ('every_12',now()); ################### 在周日调用，并且循环以后的四个周日 ########### drop event if exists test; create event test on schedule every 1 week starts now() + interval (8-dayofweek(now())) day ends now() + interval (8-dayofweek(now())) day +interval 4 week do insert into event_invoke values ('every_sunday',now()); 在每个周日下午3点调用，在下一个周日启动并且在当年的最后一个周日终止 drop event if exists test; create event test on schedule every 1 week starts timestamp(case dayname(now()) when 'Sunday' then now() when 'Monday' then now() + interval 6 day when 'Tuesday' then now() + interval 5 day when 'Wednesday' then now() + interval 4 day when 'Thursday' then now() + interval 3 day when 'Friday' then now() + interval 2 day when 'Saturday' then now() + interval 1 day end,'15:00:00') ends timestamp(case dayname(concat(year(curdate()),'-12-31')) when 'Sunday' then concat(year(curdate()),'-12-31') when 'Monday' then concat(year(curdate()),'-12-31') - interval 1 day when 'Tuesday' then concat(year(curdate()),'-12-31') - interval 2 day when 'Wednesday' then concat(year(curdate()),'-12-31') - interval 3 day when 'Thursday' then concat(year(curdate()),'-12-31') - interval 4 day when 'Friday' then concat(year(curdate()),'-12-31') - interval 5 day when 'Saturday' then concat(year(curdate()),'-12-31') - interval 6 day end,'15:00:00') do insert into event_invoke values ('next_sunday',now()); 在每个月第一天启动，开始于下个月的第一天并且在当年的最后一天结束 drop event if exists test; create event test on schedule every 1 month starts curdate()+interval 1 month - interval (dayofmonth (curdate())-1)day ends timestamp(concat(year(curdate()),'-12-31')) do insert into event_invoke values ('fisrtofmonth',now());