MySQL事件

MySQL事件

事件

事件是指根据计划在未来的指定时间执行一次或多次的任务。任务开始和结束于指定的日期和时间，期间执行一次或间隔执行多次。与触发器不同，事件由时间触发，且并不依赖于特定的触发动作。

事件的管理由事件调度器完成。事件由一个特殊的事件调度程序线程执行，当我们提到事件调度程序时，实际上指的是这个线程。在运行时，具有process权限的用户可以在show processlist的输出中看到事件调度程序线程及其当前状态。

事件调度器

全局event_scheduler系统变量决定是否启用事件调度程序并在服务器上运行。

# 查看事件调度器开启状态
show variables like 'event_scheduler';
# 开启事件调度器
set global event_scheduler=ON;
# 关闭事件调度器
set global event_scheduler=OFF；

disabled：此值表示事件不可操作，此时事件调度器线程不会运行。

使用skip-grant-tables选项启动MySQL服务器会导致event_scheduler被设置为DISABLED，覆盖命令行或my.cnf或my.ini文件中设置的任何其他值。

创建事件

create [definer=user] event [if not exists] event_name
  on schedule 时间与频率
  [on completion [not] preserve]
  [enable | disable | disable on slave]
  [comment '注释']
  do 事件体;

时间与频率：
  at timestamp [+ interval 时间间隔 时间单位] ... | 
  every 时间间隔 时间单位 [starts timestamp [+ interval 时间间隔 时间单位] ...]
                         [ends timestamp [+ interval 时间间隔 时间单位] ...]

时间间隔 时间单位：
  数量 {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
       WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
       DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

on completion表表示定义事件一旦过期是否立即被删除，默认为not preserve，表示删除；设置为preserve表示不删除。

enable表示当前事件可用，默认为enable；disable表示当前事件不可用。

comment表示注释，最多可有64个字符。

at表示事件只执行一次，every表示时间定期执行。starts和ends表示定期执行事件的时间范围。

every后设置事件的执行间隔时间，starts设置从何时开始，ends设置到何时结束。

start后的[+ interval 时间间隔 时间单位]表示延时到timestamp后指定时间开始执行；
ends后的[+ interval 时间间隔 时间单位]表示延时到timestamp后指定时间结束执行；
当starts和ends后指定多个[+ interval 时间间隔 时间单位]时，这多个指定时间间隔的时间将被合并。

使用show events语句可以方便的查看事件的开始时间和结束时间以及执行间隔等信息。

查看事件

# 查看事件
show events [{FROM | IN} schema_name] [LIKE 'pattern' | WHERE expr];
# 查看事件创建的语句
show create event event_name;

事件创建后存储再数据库中，其相关信息存在Information_schema库中的events表中。

修改事件

alter event event_name
  [on schedule 时间与频率]
  [on completion [not] preserve]
  [rename to 新的事件名]
  [enable | disable | disable on slave]
  [comment '注释']
  [do 事件体];

删除事件

drop event [if exists] 事件名;

事件权限

​在MySQL中，创建事件和修改删除事件，需要用户具有对应的权限。MySQL是使用模式(schema)来确定用户操作事件的权限的。

schema与database在不同的数据库管理系统中有不同的实现，但在MySQL中从5.0.2版本后schema是database的同义词。

​用户想要操作事件必须具备两种权限，一种是event的权限，另一种是table的权限。

• 用户对数据表有增删改查权限，但是没有event的权限，则不能操作事件
• 用户对event有权限，但是对数据表没有权限，则不能操作事件；若用户对数据表只有select权限，则可以创建事件成功，但是该事件不起作用，即包含修改操作时，事件将会不断失败。

event权限

# 授予event权限
grant event on schema_name.* to 用户名@IP;
# 移除event权限
revoke event on schema_name.* from 用户名@IP;

event的权限是授予在模式上的，因此不能具体到某个表。

仔细想想，event权限代表着对事件的增删改查操作，而table的权限代表着事件中sql语句对表的增删改查操作。

事件的操作可能涉及多个表，自然不能在授权时具体到表上。

举例

准备

DROP TABLE IF EXISTS `event_test`;
CREATE TABLE `event_test` (
    `id` int NOT NULL AUTO_INCREMENT COMMENT '事件测试表主键',
    `number` int NOT NULL COMMENT '事件测试列',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='事件测试表';
INSERT INTO `event_test` VALUES (1,0),(2,0),(3,0),(4,0),(5,0),(6,0),(7,0),(8,0),(9,0);

创建事件

DROP EVENT IF EXISTS `event_test_event`;
CREATE EVENT IF NOT EXISTS `event_test_event`
  ON SCHEDULE
      EVERY 1 minute
        STARTS '2021-11-16 11:30:00' + INTERVAL 1 minute
        ENDS '2021-11-16 11:30:00' + INTERVAL 1 hour
          ON COMPLETION PRESERVE ENABLE
           COMMENT '该事件作用是每分钟为所有列数值加1，持续时间为1小时'
             DO CALL update_event_procedure();

事件体

DROP PROCEDURE IF EXISTS `update_event_procedure`;
DELIMITER ##
CREATE PROCEDURE update_event_procedure()
BEGIN
    update `event_test` set `number` = `number` + 1;
END;
##
DELIMITER ;

事件执行前
| id | number |
| 1 | 0 |
| 2 | 0 |
| 3 | 0 |
| 4 | 0 |
| 5 | 0 |
| 6 | 0 |
| 7 | 0 |
| 8 | 0 |
| 9 | 0 |
事件执行后
| id | number |
| 1 | 60 |
| 2 | 60 |
| 3 | 60 |
| 4 | 60 |
| 5 | 60 |
| 6 | 60 |
| 7 | 60 |
| 8 | 60 |
| 9 | 60 |

END