知乎专栏 |
查看当前系统的 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>
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 ;
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
需求:数据需要删除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$$
每个月的一号凌晨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