概念
mysql事件是根据时间表运行的任务。因此,我们有时将它们称为预定事件。创建事件时,将创建一个命名的数据库对象,该对象包含一个或多个要以一个或多个定期间隔执行的SQL语句,从特定的日期和时间开始到结束。从概念上讲,这类似于UNIX crontab(也称为“cron job”)或Windows任务调度程序的概念。
语法
-
关键字create event加上一个事件名称,该名称在数据库模式中唯一标识事件;
-
一个按调度的子句,它决定事件执行的时间和频率;
-
Do子句,其中包含要由事件执行的SQL语句。
CREATE [DEFINER = user] EVENT [IF NOT EXISTS] event_name ON SCHEDULE schedule [ON COMPLETION [NOT] PRESERVE] [ENABLE | DISABLE | DISABLE ON SLAVE] [COMMENT 'string'] DO event_body; schedule: AT timestamp [+ INTERVAL interval] ... | EVERY interval [STARTS timestamp [+ INTERVAL interval] ...] [ENDS timestamp [+ INTERVAL interval] ...] interval: quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
说明
- event_name 事件名称(不区分大小写)
- ON SCHEDULE 事件执行方式(AT单次事件任务,EVERY重复事件任务)
- AT timestamp用于一次性事件。它指定事件仅在时间戳给定的日期和时间执行一次,时间戳必须同时包含日期和时间,或者必须是解析为日期时间值的表达式。为此,可以使用日期时间或时间戳类型的值。如果日期是过去的,则会出现警告;
- EVERY interval [STARTS timestamp][ENDS timestamp]。interval表示允许重复的时间单位(语法中已给出);EVERY子句可以包含可选的STARTS 子句,STARTS后面跟着一个时间戳值,该值指示操作何时开始重复,还可以使用+Interval Interval来指定“从现在开始”的时间量;EVERY子句可以包含可选的ENDS子句。ENDS关键字后跟一个时间戳值,该值告诉MySQL事件何时应停止重复;也可以使用带结束的+间隔间隔。
- [ON COMPLETION [NOT] PRESERVE] 默认一旦事件过期,它会立即被删除;可以设置ON COMPLETION PRESERVE来让事件保持显示。
- [ENABLE | DISABLE | DISABLE ON SLAVE] 设置事件启用(ENABLE )、禁用(DISABLE )
准备
- 创建测试数据库和表
CREATE DATABASE test; USE test; DROP TABLE IF EXISTS `tb_user`; CREATE TABLE `member` ( `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, `name` varchar(30) NOT NULL, `age` tinyint(1) UNSIGNED NOT NULL DEFAULT '0', `create_at` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE = InnoDB CHARSET = utf8; INSERT INTO `member` (`name`, `age`, `create_at`) VALUES ('帝君', '40', NOW()); INSERT INTO `member` (`name`, `age`, `create_at`) VALUES ('墨渊', '30', NOW()); INSERT INTO `member` (`name`, `age`, `create_at`) VALUES ('白浅', '26', NOW()); INSERT INTO `member` (`name`, `age`, `create_at`) VALUES ('夜华', '24', NOW()); INSERT INTO `member` (`name`, `age`, `create_at`) VALUES ('凤九', '18', NOW());
-
事件调度器状态
MySQL > show variables like '%event_scheduler%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | OFF | +-----------------+-------+ 1 row in set (0.00 sec)
-
开启关闭事件调度器(MySQL服务重启,事件配置就会还原默认设置,通过修改MySQL的my.cnf配置文件mysqld 下面增加GLOBAL event_scheduler = ON,做到永久生效,这里我们使用开启状态)
# 开启 SET GLOBAL event_scheduler = ON; SET @@global.event_scheduler = ON; SET GLOBAL event_scheduler = 1; SET @@global.event_scheduler = 1; # 关闭 SET GLOBAL event_scheduler = OFF; SET @@global.event_scheduler = OFF; SET GLOBAL event_scheduler = 0; SET @@global.event_scheduler = 0;
-
查看选中数据库下的事件
MySQL [(none)]> USE test; Database changed MySQL [test]> show events\G; Empty set (0.01 sec)
-
查看调度器线程
MySQL [(none)]> show processlist; +----+-----------------+-----------+------+---------+------+------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------+------+---------+------+------------------------+------------------+ | 5 | event_scheduler | localhost | NULL | Daemon | 1176 | Waiting on empty queue | NULL | | 7 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+-----------------+-----------+------+---------+------+------------------------+------------------+ 2 rows in set (0.00 sec)
例子
- 当前时间一分钟后将test.member中age字段加1
# 创建事件 MySQL [test]> CREATE EVENT IF NOT EXISTS at_update ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE COMMENT '当前时间一分钟之后更新记录' DO UPDATE test.member SET age = age + 1; Query OK, 0 rows affected (0.00 sec) # 查看事件(事件一旦执行完,事件会被立即删除,可以通过设置ON COMPLETION PRESERVE保留事件,使其Status: DISABLED) MySQL [test]> show events\G; *************************** 1. row *************************** Db: test Name: at_update Definer: root@localhost Time zone: SYSTEM Type: ONE TIME Execute at: 2019-04-15 11:59:47 Interval value: NULL Interval field: NULL Starts: NULL Ends: NULL Status: ENABLED Originator: 1 character_set_client: utf8mb4 collation_connection: utf8mb4_general_ci Database Collation: utf8mb4_general_ci 1 row in set (0.01 sec) MySQL [test]> SELECT * FROM member; +----+--------+-----+---------------------+ | id | name | age | create_at | +----+--------+-----+---------------------+ | 1 | 帝君 | 41 | 2019-04-15 15:27:09 | | 2 | 墨渊 | 31 | 2019-04-15 15:27:09 | | 3 | 白浅 | 27 | 2019-04-15 15:27:09 | | 4 | 夜华 | 25 | 2019-04-15 15:27:09 | | 5 | 凤九 | 19 | 2019-04-15 15:27:12 | +----+--------+-----+---------------------+ 5 rows in set (0.00 sec)
-
在特定时间执行事件
MySQL [test]> SELECT NOW(); +---------------------+ | NOW() | +---------------------+ | 2019-04-15 15:36:14 | +---------------------+ 1 row in set (0.00 sec) MySQL [test]> CREATE EVENT IF NOT EXISTS e_insert ON SCHEDULE AT '2019-04-15 15:37:00' COMMENT '2019-04-15 14:19:00插入一条记录' DO INSERT INTO `member` (`name`, `age`, `create_at`) VALUES ('翼君', '25', NOW()); Query OK, 0 rows affected (0.02 sec) MySQL [test]> show events\G; *************************** 1. row *************************** Db: test Name: e_insert Definer: root@localhost Time zone: SYSTEM Type: ONE TIME Execute at: 2019-04-15 14:19:00 Interval value: NULL Interval field: NULL Starts: NULL Ends: NULL Status: ENABLED Originator: 1 character_set_client: utf8mb4 collation_connection: utf8mb4_general_ci Database Collation: utf8mb4_general_ci 1 row in set (0.00 sec) MySQL [test]> SELECT * FROM member; +----+--------+-----+---------------------+ | id | name | age | create_at | +----+--------+-----+---------------------+ | 1 | 帝君 | 41 | 2019-04-15 15:27:09 | | 2 | 墨渊 | 31 | 2019-04-15 15:27:09 | | 3 | 白浅 | 27 | 2019-04-15 15:27:09 | | 4 | 夜华 | 25 | 2019-04-15 15:27:09 | | 5 | 凤九 | 19 | 2019-04-15 15:27:12 | | 6 | 翼君 | 25 | 2019-04-15 15:37:00 | +----+--------+-----+---------------------+ 6 rows in set (0.00 sec)
-
每秒钟插入两条记录
MySQL [test]> delimiter | MySQL [test]> MySQL [test]> CREATE EVENT e ON SCHEDULE EVERY 5 SECOND COMMENT '每五分钟插入两条记录' DO BEGIN DECLARE v INTEGER; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END; SET v = 0; WHILE v < 2 DO INSERT INTO `member` (`name`, `age`, `create_at`) VALUES (SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*100), NOW()); SET v = v + 1; END WHILE; END | Query OK, 0 rows affected (0.01 sec) MySQL [test]> MySQL [test]> delimiter ; MySQL [test]> SELECT * FROM member; +----+----------------------+-----+---------------------+ | id | name | age | create_at | +----+----------------------+-----+---------------------+ | 1 | 帝君 | 41 | 2019-04-15 15:27:09 | | 2 | 墨渊 | 31 | 2019-04-15 15:27:09 | | 3 | 白浅 | 27 | 2019-04-15 15:27:09 | | 4 | 夜华 | 25 | 2019-04-15 15:27:09 | | 5 | 凤九 | 19 | 2019-04-15 15:27:12 | | 6 | 翼君 | 25 | 2019-04-15 15:37:00 | | 7 | e58a468c801df4cabdb1 | 94 | 2019-04-15 15:41:08 | | 8 | 318de84585c27ab8aa39 | 19 | 2019-04-15 15:41:08 | | 9 | 84af669366936371f4fb | 7 | 2019-04-15 15:41:13 | | 10 | 6b22941b1accd7b075e2 | 84 | 2019-04-15 15:41:13 | +----+----------------------+-----+---------------------+ 10 rows in set (0.00 sec)
附加
- 如果事件不需要,可以删除
drop event event_name;