Home | 简体中文 | 繁体中文 | 杂文 | Github | 知乎专栏 | 51CTO学院 | CSDN程序员研修院 | OSChina 博客 | 腾讯云社区 | 阿里云栖社区 | Facebook | Linkedin | Youtube | 打赏(Donations) | About
知乎专栏多维度架构

22.10. 事件调度器(EVENT)

查看当前系统的 event 状态 SHOW VARIABLES LIKE 'event_scheduler';

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

mysql> 	
	
	

22.10.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)
			
		

22.10.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 ;
			
		

22.10.3. 禁用/启用

		
ALTER EVENT captcha DISABLE;		
		
		
		
ALTER EVENT captcha ENABLE;		
		
		

22.10.4. 查看 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 |
+--------+-------------+---------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| 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
			
		

22.10.5. 删除 EVENT

		
DROP EVENT [IF EXISTS] event_name;
		
		
		
DROP EVENT event_delete;		
		
		

22.10.6. EVENT 应用案例

22.10.6.1. 实例·每月创建一个表

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

			
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
			
			

22.10.6.2. 案例·定时删除数据

需求:数据需要删除100万条数据,直接删除会对系统造成压力,导致主库阻塞。

解决方案:分批删除,使用 event 定时执行删除 SQL 直到删完位置。

开启 EVENT

		
set global event_scheduler = on;		
		
			

造数据

		
CREATE EVENT IF NOT EXISTS event_test
ON SCHEDULE EVERY 2 SECOND ON COMPLETION PRESERVE 
DO insert into mytable(message,ctime) value(uuid(), now());		
		
			

备份数据

			
create table new_table_2022_7_30 select * from old_table;			
			
			

定时删除

		
DROP EVENT event_delete;
DELIMITER $$
CREATE EVENT IF NOT EXISTS event_delete
ON SCHEDULE EVERY 1 SECOND ON COMPLETION PRESERVE 
DO BEGIN
	DECLARE num integer;

	SELECT  COUNT(*) INTO num FROM mytable;

	IF num > 0 THEN
		delete from mytable limit 1;
		insert into logs(ctime) values(now());
	END IF;

END$$
		
			

22.10.6.3. 指定日期执行

			
每个月的一号凌晨1 点执行 
CREATE EVENT EVENT2 
ON SCHEDULE EVERY 1 MONTH STARTS DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 1 MONTH),INTERVAL 1 HOUR) 
ON COMPLETION PRESERVE ENABLE 
DO 
BEGIN 
	CALL YOUR(); 
END 

每个季度一号的凌晨2点执行 
CREATE EVENT TOTAL_SEASON_EVENT 
ON SCHEDULE EVERY 1 QUARTER STARTS DATE_ADD(DATE_ADD(DATE( CONCAT(YEAR(CURDATE()),'-',ELT(QUARTER(CURDATE()),1,4,7,10),'-',1)),INTERVAL 1 QUARTER),INTERVAL 2 HOUR) 
ON COMPLETION PRESERVE ENABLE 
DO 
BEGIN 
CALL YOUR(); 
END 

每年1月1号凌晨2点执行 
CREATE EVENT TOTAL_YEAR_EVENT 
ON SCHEDULE EVERY 1 YEAR STARTS DATE_ADD(DATE(CONCAT(YEAR(CURDATE()) + 1,'-',1,'-',1)),INTERVAL 2 HOUR) 
ON COMPLETION PRESERVE ENABLE 
DO 
BEGIN 
CALL YOUR(); 
END