MySQL triggers and stored procedures combined to implement timed trigger actions

MySQL can implement timed triggering functions, such as what to do with a MySQL database at a certain time, or how often to do what work.

The second case is widely used, for example, I would like to check my data information every day, more than one months of useless information to clear up to make room for other storage data to use, or a period of time to update the data and so on.

The following discussion of this situation, give an example for everyone to refer to:

1. First define a stored procedure named E_test, note the vertical bar ("|" ) must not be lost

DELIMITER |

DROP PROCEDURE IF EXISTS e_test |
CREATE PROCEDURE e_test ()

BEGIN
Update order set Status=1 where To_days (now ())-to_days (date) >=1 and status=0;

END

|

Suppose there is an order table, and the table has a status field and a Date field, and now the time in date is now longer than 1 days, and the state of the Status=0 state status is changed to 1.

2. Create a timer named Event_test

SET GLOBAL event_scheduler = 1;
CREATE EVENT IF not EXISTS event_test

On SCHEDULE every 1 SECOND

On completion PRESERVE

Do call E_test ();

Create a timer that triggers an event every 1 seconds, which is a stored procedure that executes the e_test defined above once per second.

3. This is the simplest but also the most important, we have to manually start the timer, otherwise it can not work.

ALTER EVENT event_test on

Completion PRESERVE ENABLE;

Finally, to remind one point, create a stored procedure and create a timer code to separate execution, or will be error, temporarily do not know why, personally think this may be a database to create a good storage process needs a certain amount of buffer time to prepare, we should consider to set aside this time for him. Of course, this time for the database is a certain time, but for us is only a blink of an eye, as long as the separation of two execution time is enough.

MySQL triggers and stored procedures combined to implement timed trigger actions