事件

创建事件

CREATE  [DEFINER = { user | CURRENT_USER }]   
EVENT   [IF NOT EXISTS]   event_name   
ON SCHEDULE schedule   
[ON COMPLETION [NOT] PRESERVE]   
[ENABLE | DISABLE | DISABLE ON SLAVE]   
[COMMENT 'comment']   
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} 

• definer 可选，定义事件的用户
• if not exists 可选，判断创建的事件是否存在
• event event_name 必选，事件名，默认用户名
• on schedule 必选， 用于定义指定的事件间隔
• on completion 可选，是否循环执行，默认一次执行，not preserve
• enable 可选 是否开启
• comment 可选 ，注释
• do 必选，执行的操作

• schedule 事件的间隔：
  • AT '2021-4-29 11:11:11' 定时执行
  • every 1 [day|week|month|hour|minute|...] 每个一段时间执行
  • starts '2021-04-29' 指定开始时间
  • ends '2021-4-30' 指定结束时间

delimiter $$

create definer = current_user event `spring_boot_building`.`insert_currenttime_in_table1`

on schedule
	 -- 每隔5秒中就向 `table1` 插入当前时间	
	 every 5 second
on completion preserve
enable
comment '每隔5秒中就向 `table1` 插入当前时间的事件'
do
	begin
	    insert into `table1` values(null, now());
	end$$
delimiter ;

查看事件

show events;

select * from information_schema.events;

启用和禁用

alter event event_name disable;
alter event event_name enable;

删除事件

drop event [if exists] event_name;

修改事件

ALTER[DEFINER={user | CURRENT_USER}]
EVENT [IF NOT EXISTS] event_name 
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
DO 
	event_body;