Database event

Event is a procedural database object called by MySql at the corresponding time. An event can be called once or started periodically. It is managed by a specific thread, which is the so-called "event scheduler".

Events are similar to triggers, which start when something happens. When a statement is started on the database, the trigger is started, and the event is started according to the scheduling event. Because they are similar to each other, events are also called temporary triggers.

Events replace the original work that can only be executed by the operating system's disease tasks, and the event scheduler of MySql can execute a task every second, while the scheduled tasks of the operating system (such as CRON under Linux or task planning under Windows) can only be executed every minute

1. Advantages and disadvantages of the event

advantage

Some data timing operations do not rely on external programs, but directly use the functions provided by the database itself. It can execute one task per second, which is very practical in some environments with high real-time requirements.

Disadvantages: it is triggered regularly and cannot be called.

2. Event operation

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}

Clause of CREATE EVENT statement:

clause	explain
definer	Optional, used to define the user who checks the permission when the event is executed
if not exists	Optional to determine whether the event to be created exists
event event_name	Required to specify the event name, event_ The maximum length of name is 64 characters. If event is specified_ Name, the default is the current MySQL user name (case insensitive)
ON SCHEDULE schedule	The interval used to define the execution time is required
ON COMPLETION [NOT] PRESERVE	Optional, used to define whether the event is executed circularly, that is, once or permanently. The default is once, that is, not preset
enable | disable|disable on slave	Optional to specify a property of the event. The keyword ENABLE indicates that the event is active, that is, the scheduler checks whether the event must be called; The keyword DISABLE indicates that the event is closed, that is, the declaration of the event is stored in the directory, but the scheduler will not check whether it should be called; The keyword DISABLE ON SLAVE indicates that the event is closed in the slave. If you do not specify any of these three choices, an event becomes active immediately after it is created.
COMMENT 'comment'	Optional, used to define the comment of the event
DO event_body	Required to specify the code to be executed when the event starts. It can be any valid SQL statement, stored procedure, or a planned event. If multiple statements are included, you can use the BEGIN... END compound structure

In the ON SCHEDULE clause, the value of the parameter schedule is an AS clause, which is used to specify that the event occurs at a certain time

Parameter Description:

(1) timestamp: indicates a specific time point, followed by a time interval, indicating that the event occurs after this time interval.

(2) EVERY clause: used to indicate how often an event occurs in a specified time interval. The SELECT clause is used to specify the start time; The ENDS clause is used to specify the end time.

(3) Interval: indicates a time from now on. Its value consists of a value and a unit. For example, use "4 WEEK" to represent 4 weeks; Use '1:10' HOUR_MINUTE to represent 1 hour and 10 minutes. The distance of the interval is in date_ The add() function.

Operation:

-- Check whether the event function is enabled
show variables like '%event_scheduler%';

-- Event off on
set global event_scheduler = off; -- close
set global event_scheduler = on ;-- open

-- Define events
-- View all event objects in the database, and the running events will be deleted automatically
select event_name from information_schema.events
 where event_schema = 'db'
order by event_name;

-- Two minutes after the event is established, the statement event will be executed automatically. When the conditions are met, the command event deletion will be executed.
create event e2 on schedule at current_timestamp + interval 2 minute 
do insert into db.et set id=uuid_short(),cn='aa',num=rand(),ct=now();

-- Execute after 5 seconds of the current time
create event e1 on schedule at current_timestamp + interval 5 second
 do 'sql sentence';

-- When the login time exceeds 10 seconds, it will exit the login. When logging in, save the user data and login time in the cache database
insert into et set id=uuid_short(),cn='aa',num=rand(),ct=now();
create event e1 on schedule at current_timestamp + interval 10 second
 do delete from et where ct=current_timestamp-interval 10 second;
 
-- 10 Starting in seconds, the insertion is performed every 5 seconds
create event e1 on schedule 
every 5 second
starts current_timestamp + interval 10 second
do insert into et value(uuid_short(),'aa',rand(),now());

-- Start now and empty the table every 10 seconds at satrts ends To what event stop
create event e2 on schedule 
every 10 second ends '2022-02-25 20:51:50' 
do truncate et;

-- Establish a memory cache mechanism. As long as the server is not closed, the data in this table will always exist, which is equivalent to establishing a database in the middle of the cache mechanism.

-- The created cache table exists in memory, and the data will disappear when the server closes the table
create table et(
	id bigint unsigned ,
	cn varchar(30) not null,
	num double unsigned,
	ct datetime,
	primary key(id)
)engine=memory;


-- View all events
show events;

-- Modify the name of the event
alter event ee1 rename to e01

-- Delete event
drop event if exists e01;

-- Event pause is disabled but not deleted
alter event e4 disable;

-- Event enable
alter event e4 enable;

There are mistakes, please give more advice!!