4.10. 事件调度器(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)
			
		

			
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 ;
			
		

			ALTER EVENT captcha DISABLE;
		

			ALTER EVENT captcha ENABLE;
		

			
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 |
+--------+-------------+---------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| netkiller | captcha     | neo@%   | SYSTEM    | RECURRING | NULL       | 5              | MINUTE         | 2013-07-08 16:27:03 | NULL | ENABLED |          1 | utf8                 | utf8_general_ci      | utf8_general_ci    |
| netkiller | 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: netkiller
                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: netkiller
                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
			
		

每月创建一张新表，适用于分表的场景

			
CREATE DEFINER=`neo`@`netkiller` EVENT `logging`
	ON SCHEDULE
		EVERY 1 MONTH STARTS '2017-12-11 15:51:00'
	ON COMPLETION PRESERVE
	ENABLE
	COMMENT '每月自动创建表'
DO BEGIN
	declare _table_date varchar(10);
	select date_format(date_add(curdate(),interval 1 month),'%Y%m') into _table_date;
	call logging(_table_date);
END
			
		

			
CREATE DEFINER=`neo`@`netkiller` PROCEDURE `logging`(
	IN `table_date` VARCHAR(10)
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
	set @_table_name = CONCAT('log_',table_date);
	set @_create = "CREATE TABLE If Not Exists ";
	set @_param = "(
			`id` INT(11) NOT NULL AUTO_INCREMENT,
			`type` VARCHAR(255) NULL DEFAULT NULL COMMENT '日志类型 1：网站 2：IOS 3:Android',
			`url` VARCHAR(640) NULL DEFAULT NULL COMMENT '用户访问url',
			`serverIp` VARCHAR(255) NULL DEFAULT NULL COMMENT '服务器ip',
			`bodyBytesSent` VARCHAR(255) NULL DEFAULT NULL,
			`bytesSent` VARCHAR(255) NULL DEFAULT NULL COMMENT '参数字节数',
			`browser` VARCHAR(255) NULL DEFAULT NULL COMMENT '浏览器信息',
			`ctime` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
			`mtime` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (`id`),
			INDEX `ctime` (`ctime`, `deviceType`,`isFirst`),
			INDEX `userIp` (`userIp`),
			INDEX `deviceId` (`deviceId`),
			INDEX `account` (`account`)
		)
		COMMENT='APP 访问记录'
		COLLATE='utf8_general_ci'
		ENGINE=InnoDB
		;";
	
	SET @sql = CONCAT(@_create,@_table_name,@_param);
	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	Deallocate prepare stmt;
END