Using Stored Routines, Triggers, and Scheduled Events - MySQL - page 325

With the strategy just outlined, the auction table remains relatively small, and you can
always find information about auction histories as necessary by looking in the auc
tion_log table.
9.8. Using Events to Schedule Database Actions
Problem
You want to set up a database operation that runs periodically without user intervention.
Solution
Create an event that executes according to a schedule.
Discussion
MySQL provides an event scheduler that enables you to set up database operations that
run at times that you define. This section describes what you must do to use events,
beginning with a simple event that writes a row to a table at regular intervals. Why bother
creating such an event? One reason is that the rows serve as a log of continuous server
operation, similar to the MARK line that some Unix syslogd servers write to the system
log periodically so that you know they're alive.
Begin with a table to hold the mark rows. It contains a TIMESTAMP column (which MySQL
will initialize automatically) and a column to store a message:
CREATE TABLE mark_log
(
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
message VARCHAR ( 100 )
);
Our logging event will write a string to a new row. To set it up, use a CREATE EVENT
statement:
CREATE EVENT mark_insert
ON SCHEDULE EVERY 5 MINUTE
DO INSERT INTO mark_log ( message ) VALUES ( '-- MARK --' );
The mark_insert event causes the message '-- MARK --' to be logged to the mark_log
table every five minutes. Use a different interval for more or less frequent logging.
This event is simple and its body contains only a single SQL statement. For an event
body that executes multiple statements, use BEGIN … END compound-statement syntax.
In that case, if you use mysql to create the event, change the statement delimiter while
you define the event, as discussed in Recipe 9.1 .