MySQL Event is definitely the most complete Event note you have ever seen create event alter event show event drop event

Events

Automatically execute some tasks or sql codes according to events, such as deleting, copying, adding and modifying, or generating data reports at a specific time

First of all, you must turn on event, the database is turned on by default

show variables like "event%"
-- 可以使用 like 缩小查找范围,头铁的随意

set global event_scheduler  = ON 
-- 关闭设置为OFF
image-20210603204227639

Demo

For example

Create an event and execute it once a year to delete outdated data in the payments_audit table

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

View events, delete events

show events
-- 可以加 like
drop events if exists event_name
image-20210603214823345

Alter Event Statement

Modify event

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;