MySQL Events

January 27th, 2006

A new feature of MySQL 5.1 is Events. Similar to a crontab or the 'Task Schedule', it allows you to execute an SQL statement at certain time intervals. The syntax is exceptionally easy to use:

CREATE EVENT [ IF NOT EXISTS ] event_name
ON SCHEDULE schedule
[ ON COMPLETION [ NOT ] PRESERVE ]
[ ENABLED | DISABLED ]
[ COMMENT 'comment' ]
DO sql_statement;

The first thought might be why you would want to run such a query from within MySQL. Running queries at regular intervals is sometimes necessary in large projects, e.g. for general cleanup etc. You can do this from within your chosen scripting language (PHP, Ruby etc), however, this creates another layer of dependancy. If your PHP script can't connect to the MySQL server, then it won't run - storing the event within MySQL overcomes this dependancy.

A simple example of an event is below:

CREATE EVENT optimizate
ON SCHEDULE EVERY 1 MONTH
ON COMPLETION PRESERVE
DO OPTIMIZE TABLE table1, table2;

This would cause MySQL to OPTIMIZE table1 and table2 every month, and continue to do so every month until the event is stopped.

The SQL statement in question can be an SQL query or you can build it up into a series of queries using the BEGIN and END syntax similar to stored routines and triggers.

The only upsetting part of the EVENTS syntax, is that is available only in MySQL 5.1.6 which has yet to be released, even into an alpha release. You can keep an eye out for it here.

More information on the EVENTS syntax can be found online at MySQL.com.

Add to del.icio.us Digg this Technorati