MySQL event mechanism-timed tasks

Timed tasks are a cliché, as we always need to modify specific data at regular intervals.

there must be more than one way to implement it, but I For quite some time, it was coded to do it, and today it suddenly occurred to me, "Why do we have to call it?" "Wouldn't it be better to use the ability of the database itself to achieve it?"

By understanding that the MySQL event mechanism can complete a timed task, the principle is to invoke the specified stored procedure at the specified time. It's easy now, isn't it? Open up.

First of all, we need a stored procedure, although it is very simple, but given the introduction of the children's shoes, I would still post an example:

Delimiter $$;create procedure Del_car_viol () Begin    Delete from Car_viol where ' create ' < Date_sub (Curdate (), Interval 1 day); end$$;d Elimiter;

This section of code creates a stored procedure that can delete data that is less than yesterday (indicating that the field name is ignored).

The next step is to create an event that allows the event to invoke the stored procedure according to a certain rule, so that the function of the timed operation can be realized.

The code is as follows:

Create event ' E_update_user_ticket ' on   schedule every 1 day starts ' 2017-09-02 00:00:00 ' on   completion not Preserv E Enable do call Del_car_viol ();  

The above code creates an event that automatically invokes a stored procedure that was written every other day, starting September 2, 2017 0 o'clock.

The 1 days in the code represent once a day, and you can also replace it with 2 year (2 years at a time).

Once the event is created, it is executed immediately and is usually turned on by default.

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

/* Open Event */alter event name on completion preserve enable;  /* Shutdown Event */alter event name on completion preserve disable;  

If you do not know if your MySQL has the support of the Turn on Time feature, you can query by the following statement:

/* See if the event function is turned on */show variables like ' Event_scheduler ';  

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

It is important to note that the event mechanism was introduced in the mysql5.1 version, which means that versions below 5.1 may not be available. (Hope to be helpful to everyone ^_^)

MySQL event mechanism-timed tasks