Shulou(Shulou.com)06/01 Report--

MySQL Database Advanced (8)-- event 1, event introduction 1, event introduction

An event is a set of SQL sets used to perform scheduled tasks that are triggered when the time expires.

An event can be called once or started periodically, managed by a specific event scheduler thread.

Events replace work that can only be performed by the scheduled tasks of the operating system, and MySQL's event Scheduler can be accurate to execute one task per second, while the scheduled tasks of the operating system (such as CRON under Linux or task scheduling under Windows) can only be executed once per minute.

2. Advantages of events

A. the timed operation of the data no longer depends on external programs, but directly uses the functions provided by the database itself.

B. it can execute one task per second, which is very practical in some environments with high real-time requirements.

3. Shortcomings of the event

It is triggered regularly and cannot be called.

4. Applicable scenarios for events

Event can be used to handle operations that have fixed requirements at regular intervals, such as creating tables, deleting data, and so on.

2. Action of the event 1. Creation of the event 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, which cannot exceed 64 characters. The name must be unique in the current Dateabase. The same database cannot have an event with the same name. When you create an event, you can specify Schema at the same time, with the syntax structure: schema_name.eventname. The common tasks of using event are to create tables, insert data, delete data, clear tables, and delete tables. In order to standardize the event naming, the event name needs to have the ability to describe the whole event. The recommended naming rules are as follows: action name (INTO/FROM_) table name _ TIME, such as event naming, insert_into_tablename_day, which is recorded in the table every day.

ON SCHEDULE schedule: scheduling tasks

Schedule: scheduling rules that specify the execution time and execution rules of events. There are two forms of AT and EVERY to determine the execution time and frequency of event (the time must be the future time, and the past time can go wrong).

The grammatical 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_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

AT timestamp, used to complete a single scheduled task.

EVERY n quantity [STARTS timestamp] [ENDS timestamp], used to accomplish repetitive scheduled tasks.

The timestamp can be any TIMESTAMP and DATETIME data type, and the timestamp needs to be greater than the current time.

In repetitive scheduled tasks, the number of time (units) can be any integer that is not empty (Not Null), and the time unit is the key word: YEAR,MONTH,DAY,HOUR,MINUTE or SECOND.

AT timestamp is generally used for only one execution, and you can generally use the current time plus a delayed period of time. For example, AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR; can also define a time constant, for example: AT '2006-02-10 23 INTERVAL 5900 displacement interval is generally used for periodic execution, and the start time and end time can be set.

ON COMPLETION [NOT] PRESERVE: the action after the event expires. The default event will be deleted automatically when it expires. If you want to keep events using ON COMPLETION PRESERVE;, if you don't want to keep events, you can set ON COMPLETION NOT PRESERVE.

[ENABLE | DISABLE]: parameters Enable and Disable indicate the status of the set event. Enable indicates that the system will execute the event, and Disable indicates that the system does not execute the event.

[COMMENT 'comment']: add comments, which appear in the metadata and are stored in the COMMENT column of the information_schema table, with a maximum length of 64 bytes. Comment' means to put the comment content between single quotation marks, and it is recommended that you use comments to express more comprehensive information.

Event_body: event body, which can be a single-line SQL syntax, or BEGIN. Blocks of END statements, or stored procedures.

2. Opening of event Scheduler

MySQL turns off event Scheduler by default.

View event Scheduler information

Show global variables like'% event_scheduler%';SHOW PROCESSLIST

Open event Scheduler

Set global event_scheduler=ON

Modify it in the my.cnf configuration file of MySQL.

Event_scheduler = 1 # or ON

3. View SELECT * FROM mysql.event; SHOW EVENTS; SELECT * FROM information_schema.events;4 and delete events

DROP EVENT [IF EXISTS] eventname

5. Disable events

ALTER EVENT eventname DSIABLE

6. Open the 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] 3. Event instance 1, insert the record at regular intervals

Create a table

CREATE TABLE event_table (timeline TIMESTAMP)

Create an event and insert a record every 1 second

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

Check the insertion record after a certain period of 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 the inserted record after a specific time, and the record has been inserted

Select * from event_table

3. Clear the table at a specific time

Create an event, clear the table at a specific time, and retain the event after the event is executed

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

The contents of the query table have been cleared after the time has passed.

Select * from event_table

4. Regularly clear the table within an event segment

Create an event, open it after 5 days, empty the table regularly every day, and stop execution after a month.

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.