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

This article mainly introduces MySQL how to create scheduled tasks, the article introduces in great detail, has a certain reference value, interested friends must read it!

Scheduled tasks are a clich é because we always need to modify specific data on a regular basis.

There must be more than one way to implement it, but I've been programming it for quite a long time, and today I suddenly thought, "Why do we have to call it?" Wouldn't it be better to use the capabilities of the database itself to achieve it?

It is understood that mysql's event mechanism can complete scheduled tasks, and its principle is to call a specified stored procedure at a specified time. It's easy now, isn't it? Let's do it.

First of all, we need a stored procedure, which is very simple, but considering the entry-level children's shoes, I still post an example:

Delimiter $$; create procedure del_car_viol () begin delete from car_viol where `create` < date_sub (curdate (), interval 1 day); end$$;delimiter

In this code, a stored procedure is created that can delete data whose time is less than yesterday (indicating that the field name should be ignored).

The next step is to create an event that calls the stored procedure according to certain rules, so that the function of timing operation can be realized.

The code is as follows:

Create event `eroomupdatekeeper username _ ticket` on schedule every 1 day starts' 2017-09-02 00001 on completion not preserve enable do call del_car_viol ()

The above code creates an event that automatically calls the previously written stored procedure every other day starting at 0: 00 on September 2, 2017.

The 1 day in the code stands for once a day, or you can replace it with 2 year (once every two years).

The event is executed immediately after it is created, and is usually enabled by default.

If you want to control the running state of an event, you can do this:

/ * enable event * / alter event event name on completion preserve enable; / * close event * / alter event event name on completion preserve disable

If you do not know whether your mysql has the support to enable the time feature, you can query it by using the following sentence:

/ * check whether the event function is enabled * / show variables like 'event_scheduler'

In the query table, value is off for off and on for on. If you want to enable the event feature, execute the following statement:

/ * enable event function * / set global event_scheduler = on

It is important to note that the event mechanism was introduced in the mysql5.1 version, which means that a version earlier than 5.1 may not be available.

The above is all the content of the article "how to create scheduled tasks in MySQL". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.