Home | 简体中文 | 繁体中文 | 杂文 | Search | ITEYE 博客 | OSChina 博客 | Facebook | Linkedin | 作品与服务 | Email

23.9. 事件调度器(EVENT)

23.9.1. 启用 EVENT

set GLOBAL event_scheduler=ON;
			

my.cnf 配置

event_scheduler=on
			

查看状态

			
mysql> select @@GLOBAL.event_scheduler;
+--------------------------+
| @@GLOBAL.event_scheduler |
+--------------------------+
| ON                       |
+--------------------------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set (0.01 sec)
			
			

23.9.2. 创建 EVENT

			
DROP EVENT IF EXISTS `captcha`;
DELIMITER //
CREATE DEFINER=`neo`@`%` EVENT `captcha` ON SCHEDULE EVERY 5 MINUTE STARTS '2013-07-08 16:27:03' ON COMPLETION PRESERVE ENABLE DO BEGIN
	delete from captcha where ctime < DATE_ADD(now(), INTERVAL -5 MINUTE);
END//
DELIMITER ;
			
			

23.9.3. 禁用/启用

ALTER EVENT captcha DISABLE;
			
ALTER EVENT captcha ENABLE;
			

23.9.4. show events

			
mysql> show events;
+--------+-------------+---------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| Db     | Name        | Definer | Time zone | Type      | Execute at | Interval value | Interval field | Starts              | Ends | Status  | Originator | character_set_client | collation_connection | Database Collation |
+--------+-------------+---------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| hx9999 | captcha     | neo@%   | SYSTEM    | RECURRING | NULL       | 5              | MINUTE         | 2013-07-08 16:27:03 | NULL | ENABLED |          1 | utf8                 | utf8_general_ci      | utf8_general_ci    |
| hx9999 | sms_ips_log | neo@%   | SYSTEM    | RECURRING | NULL       | '0 5'          | DAY_HOUR       | 2013-07-09 14:39:51 | NULL | ENABLED |          1 | utf8                 | utf8_general_ci      | utf8_general_ci    |
+--------+-------------+---------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
2 rows in set (0.00 sec)

mysql> show events \G;
*************************** 1. row ***************************
                  Db: hx9999
                Name: captcha
             Definer: neo@%
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 5
      Interval field: MINUTE
              Starts: 2013-07-08 16:27:03
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
*************************** 2. row ***************************
                  Db: hx9999
                Name: sms_ips_log
             Definer: neo@%
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: '0 5'
      Interval field: DAY_HOUR
              Starts: 2013-07-09 14:39:51
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
2 rows in set (0.00 sec)

ERROR:
No query specified
			
			
comments powered by Disqus