MySQL Event 绝对是你看过最全的Event 笔记 create event alter event show event drop event

Events

• 根据事件自动执行一些任务或sql代码 , 如在特定的时间执行删除,复制,增加修改,或生成数据报告之类的操作

• 首先要开启 event , 数据库默认是开启的 on

• show variables like "event%"
  -- 可以使用 like 缩小查找范围,头铁的随意
  
  set global event_scheduler  = ON 
  -- 关闭设置为OFF
  

• 

Demo

• 举例

• 创建一个事件,每年执行一次,删除payments_audit 表中的过时的数据

• delimiter $$
  create event yearly_dalete_stale_audit_rows -- 这个命名规则,yearly 每一年,删除,过时,audit表 rows
  on schedule
  	-- at "2021-06-03"  
  	-- 如果只执行一天,就用at
      every 1 year starts "2021-06-03" ends "2031-06-03"
      -- every 1 year 每年执行一次 ,从2021开始 ends 结束
  do begin
  	delete from payments_audit
      where action_date < now()-interval 1 year;
      -- where action_date < date_add(now(),interval -1 year);
      -- date_add 增加负数也可以当减用
      -- where action_date < date_sub(now(),interval 1 year);
      -- 三句效果相同
         
  end $$
  delimiter ;
  

Create event statement

• CREATE
      [DEFINER = user]
      EVENT
      [IF NOT EXISTS]
      event_name
      ON SCHEDULE schedule
      [ON COMPLETION [NOT] PRESERVE]
      -- Normally, once an event has expired, it is immediately dropped. You can override this behavior by specifying ON COMPLETION PRESERVE. Using ON COMPLETION NOT PRESERVE merely makes the default nonpersistent behavior explicit.
      -- 通常，一旦一个事件过期，它就会立即被删除。您可以通过指定ON COMPLETION PRESERVE来重写此行为。使用ON COMPLETION NOT PRESERVE仅仅使默认的非持久性行为显式。
      [ENABLE | DISABLE | DISABLE ON SLAVE]
      -- 用于设置复制从机上创建并复制到从机上的,但不是在从机上执行的
      [COMMENT 'string']
      -- 注释 ,必须用引号括起来
      DO event_body;  -- 这后面可以直接接语句,如果要是多条语句的话就加begin..end
  
  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}
  

Show and Drop Event Statement

• 查看事件,删除事件

• show events
  -- 可以加 like
  drop events if exists event_name
  

• 

Alter Event Statement

• 修改事件

• ALTER
      [DEFINER = user] -- 修改定义者
      EVENT event_name
      [ON SCHEDULE schedule] -- 修改触发事件的时间设置
      [ON COMPLETION [NOT] PRESERVE] 
      -- Normally, once an event has expired, it is immediately dropped. You can override this behavior by specifying ON COMPLETION PRESERVE. Using ON COMPLETION NOT PRESERVE merely makes the default nonpersistent behavior explicit.
      -- 通常，一旦一个事件过期，它就会立即被删除。您可以通过指定ON COMPLETION PRESERVE来重写此行为。使用ON COMPLETION NOT PRESERVE仅仅使默认的非持久性行为显式。
      [RENAME TO new_event_name]
      [ENABLE | DISABLE | DISABLE ON SLAVE]
      -- 这几个参数涉及到主从复制
      [COMMENT 'string']
      -- 注释
      [DO event_body]
      
      
  -- 举例
  CREATE EVENT e_hourly
      ON SCHEDULE
        EVERY 1 HOUR
      COMMENT 'Clears out sessions table each hour.'
      DO
        DELETE FROM site_activity.sessions;