############### 首先设置环境变量 #####################
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());