mysqlæä¹è®©ä¸ä¸ªåå¨è¿ç¨å®æ¶æ§è¡
æ¥çeventæ¯å¦å¼å¯: show variables like '%sche%';
å°äºä»¶è®¡åå¼å¯: set global event_scheduler=1;
å
³éäºä»¶ä»»å¡: alter event e_test ON COMPLETION PRESERVE DISABLE;
å¼æ·äºä»¶ä»»å¡: alter event e_test ON COMPLETION PRESERVE ENABLE;
ç®åå®ä¾.
å建表 CREATE TABLE test(endtime DATETIME);
å建åå¨è¿ç¨test
CREATE PROCEDURE test ()
BEGIN
update examinfo SET endtime = now() WHERE id = 14;
END;
å建event e_test
CREATE EVENT if not exists e_test
on schedule every 30 second
on completion preserve
do call test();
æ¯é30ç§å°æ§è¡åå¨è¿ç¨test,å°å½åæ¶é´æ´æ°å°examinfo表ä¸id=14çè®°å½çendtimeå段ä¸å»
1) é¦å
æ¥çä¸ä¸ªç®åçä¾åæ¥æ¼ç¤ºæ¯ç§æå
¥ä¸æ¡è®°å½å°æ°æ®è¡¨
USE test;
CREATE TABLE aaa (timeline TIMESTAMP);
CREATE EVENT e_test_insert
ON SCHEDULE EVERY 1 SECOND
DO INSERT INTO test.aaa VALUES (CURRENT_TIMESTAMP);
çå¾
3ç§éåï¼åæ§è¡æ¥è¯¢ççï¼
mysql> SELECT * FROM aaa;
+---------------------+
| timeline |
+---------------------+
| 2007-07-18 20:44:26 |
| 2007-07-18 20:44:27 |
| 2007-07-18 20:44:28 |
+---------------------+
2) 5天åæ¸
空test表ï¼
CREATE EVENT e_test
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO TRUNCATE TABLE test.aaa;
3) 2007å¹´7æ20æ¥12ç¹æ´æ¸
空test表ï¼
CREATE EVENT e_test
ON SCHEDULE AT TIMESTAMP '2007-07-20 12:00:00'
DO TRUNCATE TABLE test.aaa;
4) æ¯å¤©å®æ¶æ¸
空test表ï¼
CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
DO TRUNCATE TABLE test.aaa;
5) 5天åå¼å¯æ¯å¤©å®æ¶æ¸
空test表ï¼
CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO TRUNCATE TABLE test.aaa;
6) æ¯å¤©å®æ¶æ¸
空test表ï¼5天ååæ¢æ§è¡ï¼
CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
ENDS CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO TRUNCATE TABLE test.aaa;
7) 5天åå¼å¯æ¯å¤©å®æ¶æ¸
空test表ï¼ä¸ä¸ªæååæ¢æ§è¡ï¼
CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY
ENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH
DO TRUNCATE TABLE test.aaa;
[ON COMPLETION [NOT] PRESERVE]å¯ä»¥è®¾ç½®è¿ä¸ªäºä»¶æ¯æ§è¡ä¸æ¬¡è¿æ¯æä¹
æ§è¡ï¼é»è®¤ä¸ºNOT PRESERVEã
8) æ¯å¤©å®æ¶æ¸
空test表(åªæ§è¡ä¸æ¬¡ï¼ä»»å¡å®æåå°±ç»æ¢è¯¥äºä»¶)ï¼
CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
ON COMPLETION NOT PRESERVE
DO TRUNCATE TABLE test.aaa;
[ENABLE | DISABLE]å¯æ¯è®¾ç½®è¯¥äºä»¶å建åç¶ææ¯å¦å¼å¯æå
³éï¼é»è®¤ä¸ºENABLEã
[COMMENT âcommentâ]å¯ä»¥ç»è¯¥äºä»¶å ä¸æ³¨éã
ä¸ãä¿®æ¹äºä»¶(ALTER EVENT)
ALTER EVENT event_name
[ON SCHEDULE schedule]
[RENAME TO new_event_name]
[ON COMPLETION [NOT] PRESERVE]
[COMMENT 'comment']
[ENABLE | DISABLE]
[DO sql_statement]
1) 临æ¶å
³éäºä»¶
ALTER EVENT e_test DISABLE;
2) å¼å¯äºä»¶
ALTER EVENT e_test ENABLE;
3) å°æ¯å¤©æ¸
空test表æ¹ä¸º5天æ¸
空ä¸æ¬¡ï¼
ALTER EVENT e_test
ON SCHEDULE EVERY 5 DAY;
åãå é¤äºä»¶(DROP EVENT)
è¯ æ³å¾ç®åï¼å¦ä¸æ示ï¼
DROP EVENT [IF EXISTS] event_name
ä¾å¦å é¤åé¢å建çe_testäºä»¶
DROP EVENT e_test;
å½ç¶åææ¯è¿ä¸ªäºä»¶åå¨ï¼å¦åä¼äº§çERROR 1513 (HY000): Unknown eventé误ï¼å æ¤æ好å ä¸IF EXISTS
DROP EVENT IF EXISTS e_test;
create event test
ON SCHEDULE AT '2007-09-01 12:00:00' + INTERVAL 1 DAY
on completion not preserve
do insert into yyy values('hhh','uuu');
解éï¼ä»2007-09-01å¼å§ï¼æ¯å¤©å¯¹è¡¨yyyå¨12ï¼00ï¼00è¿è¡ä¸ä¸ªæå
¥æä½ãèä¸åªæ§è¡ä¸æ¬¡ï¼on completion not preserve ï¼
æç计åä»»å¡ä¸ºï¼
create event sysplan
ON SCHEDULE AT '2010-05-22 23:00:00' + INTERVAL 1 DAY
on completion not preserve
do truncate table bjproj.ae_tmp;
ä¸ãéè¿è®¾å®å
¨å±åéevent_scheduler çå¼å³å¯å¨æçæ§å¶äºä»¶è°åº¦å¨æ¯å¦å¯ç¨ã
æ¥çæ¯å¦event_schedulerå¼å¯mysql> SHOW VARIABLES LIKE '%event%';
设置å¼å¯mysql> SET GLOBAL event_scheduler=ON;
åãä¾å:
æ¯
åéæå
¥ä¸æ¡æ¥å¿:DELIMITER //CREATE EVENT `user_log_event` ON SCHEDULE EVERY 1
MINUTE STARTS '2010-12-27 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO
BEGIN INSERT INTO log SET addtime=NOW();END//
è°ç¨åå¨è¿ç¨:DELIMITER
//CREATE EVENT `user_log_event` ON SCHEDULE EVERY 1 DAY STARTS
'2010-00-00 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
CALL user_log_prov();END//
温馨提示:答案为网友推荐,仅供参考