Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to achieve scheduled tasks in the MySQL database. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

I. Overview of events

A new feature, event Scheduler (Event Scheduler), is added in MySQL 5.1.According to this feature, the event Scheduler is called event Scheduler.

Events (event) are procedural database objects that MySQL calls at the appropriate time. An event can be called once or started periodically, and it is managed by a specific thread, which is called the event Scheduler.

Events, like triggers, are started when something happens. When a statement is started on the database, the trigger starts, and the event starts based on the scheduled event. Because they are similar to each other, events are also called temporary triggers. 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.

Second, the advantages and disadvantages of the event

Advantages: some data timed operations no longer 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: timing trigger, can not be called.

Event Scheduler parameters

Events are managed by a specific thread. With the event Scheduler enabled, the thread can be seen by an account with SUPER permission to execute SHOW PROCESSLIST.

-- check whether the event is enabled. SHOW VARIABLES LIKE 'event_scheduler';SELECT @ @ event_scheduler;-- Global enable event Scheduler SET GLOBAL event_scheduler = ON;-- Global close event Scheduler SET GLOBAL event_scheduler = OFF;-- permanently enable event Scheduler (added in my.cnf configuration file) # event Scheduler startup status event_scheduler = on

Fourth, create events

A create event statement creates an event. Each event consists of two main parts. the first part is event scheduling (eventschedule), which indicates when and how often the event starts.

The second part is the event action (event action), which is the code executed when the event starts. The action of the event contains a SQL statement, which may be a simple insert or update statement, or it can make a stored procedure or

Benin...end statement block, both of which allow us to execute multiple SQL.

An event can be active (open) or stopped (closed), which means that the event scheduler checks whether the event action must be called, and stopping means that the declaration of the event is stored in the directory, but the scheduler does not check whether it should be called. Immediately after an event is created, it becomes active, and an active event can be executed one or more times.

Related syntax:

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_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

Description:

5. Event (scheduled task) management

1. Query event

In MySQL, you can view events that have been created by querying the information_schema.events table.

-- View the event show events;-- of the current library to view all events SELECT * FROM information_schema.events

two。 Modify event

After the event is created, you can also use the ALTER EVENT statement to modify its definition and related properties

ALTER [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

3. Startup and shutdown events

The ALTER EVENT statement can make an event turn off or be active again.

-- start the event. ALTER EVENT event_name ENABLE;-- shutdown event. ALTER EVENT event_name DISABLE

4. Delete event

Deleting events that have been created can be done using the DROP EVENT statement.

DROP EVENT IF EXISTS event_name

VI. Demonstration of examples

1. Event + sql

Create an event named event_t1 to insert a piece of data into the data table T1 (user information table) every 5 seconds.

-- create user information table CREATE TABLE IF NOT EXISTS T1 (id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'user number', name VARCHAR (30) NOT NULL COMMENT 'user name', create_time TIMESTAMP COMMENT 'creation time') COMMENT = 'user information table';-- create event CREATE EVENT IF NOT EXISTS event_t1ON SCHEDULE EVERY 5 SECONDON COMPLETION PRESERVECOMMENT 'add user information timing task' DO INSERT INTO T1 (name,create_time) VALUES ('hwb',NOW ())

two。 Event + stored procedure

-- create a total table CREATE TABLE IF NOT EXISTS t_total (userNumber INT COMMENT 'number of users', createtime TIMESTAMP COMMENT 'creation time') COMMENT = 'total table';-- create a stored procedure named t_total to count the number of previous table inserts CREATE PROCEDURE t_total () BEGIN DECLARE n_total INT default 0; SELECT COUNT (*) INTO n_total FROM T1; INSERT INTO t_total (userNumber,createtime) VALUES (nasty totalint now ()); END Create an event named event_total to call the stored procedure at 12:00 every day. CREATE EVENT IF NOT EXISTS event_totalON SCHEDULE EVERY 1 DAY STARTS DATE_ADD (DATE_ADD (CURDATE (), INTERVAL 1 DAY), INTERVAL 12 HOUR) ON COMPLETION PRESERVE ENABLEDO CALL t_total ()

After reading the above, do you have any further understanding of how to implement scheduled tasks in the MySQL database? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.