event_scheduler=on
重启数据库
service mysql restart
mysql> show variables like '%event_sch%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+
1 row in set (0.00 sec)
2、创建event
DELIMITER $$
CREATE event event_p2
ON SCHEDULE
EVERY 5 MINUTE
STARTS NOW()
DO
BEGIN
CALL p2(160105);
END$$
DELIMITER ;
3、查看event
mysql> show events\G;
*************************** 1. row ***************************
Db: report
Name: event_p2
Definer: root@localhost
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 1
Interval field: MINUTE
Starts: 2016-05-10 20:09:50
Ends: NULL
Status: ENABLED
Originator: 1
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
CREATEEVENT [IFNOTEXISTS] event_name
ONSCHEDULE schedule
[ONCOMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT 'comment']
DO sql_statement;
schedule:
ATtimestamp[+ INTERVAL interval]
| EVERY interval [STARTS timestamp] [ENDStimestamp]
interval:
quantity {YEAR| QUARTER |MONTH|DAY|HOUR|MINUTE|
WEEK | SECOND| YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
其中,event_name:定时器名,最大长度64个字符,若未指定,则默认为当前的MySQL用户名(不区分大小写);
schedule:限定执行时间;
ON COMPLETION [NOT] PRESERVE:表示是否需要循环复用这个Event;
sql_statement:要执行的SQL语句(也可以使用存储过程代替传统的SQL语句);
comment:对该时间调度器的一个注释,最大长度64个字符;
【关闭事件】
ALTEREVENT event_nameONCOMPLETION PRESERVE DISABLE;
【开启事件】
ALTEREVENT event_nameONCOMPLETION PRESERVE ENABLE;
【删除事件】
DROPEVENT [IF EXISTS] event_name
【注意】:要使用定时器,MySQL的常量GLOBAL event_scheduler必须为on或者是1.
【范例】
1. 每天凌晨1点开始执行数据更新:
CREATEEVENT [IFNOTEXISTS] E_testEvent_1
ONSCHEDULE EVERY 1DAYSTARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1DAY), INTERVAL 1HOUR)
ONCOMPLETION PRESERVE ENABLE
DO
BEGIN
call p2();
end ;
2. 每月第一天凌晨1点开始执行数据更新(使用存储过程):
CREATEEVENT E_testEvent_2
ONSCHEDULE EVERY 1MONTHSTARTS DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVALDAY(CURDATE())-1DAY), INTERVAL 1MONTH),INTERVAL 1HOUR)
ONCOMPLETION PRESERVE ENABLE
DO
BEGIN
CALL p2();
END
3. 每季度第一天凌晨1点开始执行数据更新:
CREATEEVENT E_testEvent_3
ONSCHEDULE EVERY 1 QUARTER STARTS DATE_ADD(DATE_ADD(DATE( CONCAT(YEAR(CURDATE()),'-',ELT(QUARTER(CURDATE()),1,4,7,10),'-',1)),INTERVAL 1 QUARTER),INTERVAL 2HOUR)
ONCOMPLETION PRESERVE ENABLE
DO
UPDATE_T_testSETcol= 2;