MySQL database Advanced (eight)--Events

MySQL database Advanced (eight)--event One, Introduction to Events 1, Introduction to Events

An event is a set of SQL sets that are used to perform timed tasks that are triggered when time is up.
An event can be invoked once or periodically, and managed by a particular event scheduler thread.
Events replace the work that was originally performed only by the operating system's scheduled tasks, and the MySQL event Scheduler can perform a task exactly once per second, while the operating system's scheduled tasks (such as Cron under Linux or Scheduled tasks under Windows) can only be performed once per minute.

2. Advantages of the event

A, the data timing operation no longer relies on external programs, and directly use the functionality provided by the database itself.
B, can achieve a task every second, in some of the real-time requirements of the high-performance environment is very practical.

3. Disadvantages of the event

Timed trigger, cannot be called.

4. Application Scenarios for events

For operations that have fixed requirements at regular intervals, such as creating tables, deleting data, and so on, you can use event to handle it.

Ii. operation of the event 1, creation of events

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

Event_Name: Event name, cannot exceed 64 characters, name must be unique in current dateabase, the same database cannot have an event with the same name. When creating an event, you can specify the schema at the same time, with the syntax structure: schema_name.eventname. Common work with event is to create tables, insert data, delete data, empty tables, and delete tables. In order to standardize event naming, event names need to have the ability to describe the entire event. The recommended naming rules are as follows: Action name(into/from_) Table name _time, such as the daily Insert Table records event naming, Insert_into_tablename_day.
On SCHEDULE SCHEDULE: Scheduled Tasks
Schedule: A scheduling rule that specifies the execution time and execution rules of an event. Determine the execution time and frequency of the event (the time must be in the future, the past time will be wrong), there are two forms of at and every.
The syntax structure is as follows:
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}
At timestamp, used to complete a single scheduled task.
Every n quantity [starts timestamp] [ENDS timestamp], which is used to complete repetitive scheduled tasks.
The timestamp can be any of the timestamp and datetime data types, and the timestamp needs to be greater than the current time.
In a recurring scheduled task, the number of times (units) can be an integer of any non-null (NOT NULL), the time unit is the keyword: year,month,day,hour,minute or second.
At TIMESTAMP is typically used only once, typically with the current time plus a delay of a period of time, for example: at Current_timestamp + INTERVAL 1 HOUR; You can also define a time constant, for example: at ' 2006-02-10 23:59:00 '; Every interval is typically used for periodic execution and can set the start time and end time.
On completion [NOT] PRESERVE: The action after the event expires, the default event is automatically deleted after it expires. If you want to keep the event using on completion PRESERVE, you can set on completion not PRESERVE if you do not want to preserve the event.
[ENABLE | DISABLE]: The parameters enable and DISABLE indicate the state of the set event. Enable means that the system will execute the event, disable indicates that the system does not execute the event.
[COMMENT ' COMMENT ']: Add comments, comments appear in the metadata, stored in the COMMENT column of the Information_schema table, with a maximum length of 64 bytes. ' Comment ' means placing comments between single quotes, and it is recommended to use annotations to express more comprehensive information.
Event_body: Event body, which can be a single-line SQL syntax, or begin ... End Statement block, or a stored procedure.

2, the event Scheduler to open

MySQL default Shutdown event scheduler.
Viewing Event Scheduler Information

show global variables like ‘%event_scheduler%‘;SHOW PROCESSLIST;

Turn on Event Scheduler
set global event_scheduler=ON;
Modified in MySQL's my.cnf configuration file.
event_scheduler?=?1?#或者ON??

3. Viewing of events

 SELECT * FROM mysql.event;    SHOW EVENTS;    SELECT * FROM information_schema.events;

4. Delete Events

DROP EVENT [IF?EXISTS] eventname;

5. Disabling events

ALTER EVENT eventname DSIABLE;

6. Open Event

ALTER EVENT eventname ENABLE;

7. Modification of the event

ALTER??? [DEFINER = { user | CURRENT_USER }]??? EVENT event_name??? [ON SCHEDULE schedule]??? [ON COMPLETION [NOT] PRESERVE]??? [RENAME TO new_event_name]??? [ENABLE | DISABLE | DISABLE ON SLAVE]??? [COMMENT ‘comment‘]??? [DO event_body]

Iii. Event Example 1, inserting records at a certain time every

Create a table
CREATE TABLE event_table(timeline TIMESTAMP);
Create an event that inserts one record every 1 seconds

CREATE EVENT insert_into_event_table_secondON SCHEDULE EVERY 1 SECOND DO INSERT INTO schoolDB.event_table VALUES(CURRENT_TIMESTAMP);

Check the insert record for a certain time
select * from event_table;
Stop Event
alter event insert_into_event_table_second disable;
Delete Event
drop event insert_into_event_table_second;

2. Insert a record at a specific time

Create an event to insert a record at a specific time

CREATE EVENT insert_into_event_table_timeON SCHEDULE AT TIMESTAMP ‘2018-04-11 21:27:30‘DO INSERT INTO schoolDB.event_table values(CURRENT_TIMESTAMP);

View insert record after a certain time, record inserted
select * from event_table;

3. Clear the table at a specific time

Create an event that clears the table at a specific time, preserving the event after the event has finished executing

CREATE EVENT truncate_event_table_timeON SCHEDULE AT TIMESTAMP ‘2018-04-11 21:35:00‘ON COMPLETION PRESERVEDO TRUNCATE TABLE schoolDB.event_table;

Query the contents of the table after the time has been emptied
select * from event_table;

4, an event period of time to clear the table

Create an event, open 5 days later, clear the table regularly every day, and stop execution after one months

CREATE EVENT truncate_event_table_dayON SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAYENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTHON COMPLETION PRESERVEDO TRUNCATE TABLE schoolDB.event_table;

MySQL database Advanced (eight)--Events