Rambling Thoughts

MySQL Events

Posted on 27. May, 2009 by Nikhil Sheth in MySql

What are MySQL Events?

Events are just like cronjobs in Linux or or task scheduler in windows.

They are similar to triggers but it fires at particular time or periodically. You have flexibility of setting start date and end date too. MySQL supports Event from version 5.1.6 onwards.

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

This is very useful and saves lots of programming work. Lets say I need to optimize my tables very month, either I need to manually run optimize query every month or I have to write a cronjob. Now with Events, I can do tasks with ease.

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

Normally, once an event has expired, it is immediately dropped. You can override this behavior by specifying ON COMPLETION PRESERVE. Using ON COMPLETION NOT PRESERVE merely makes the default non-persistent behavior explicit.

To enable events in MySQL:

SET GLOBAL event_scheduler = 'ON';

Related posts:

1. MySQL Librarian
2. MySQL Admin and Development Tools
3. Oracle-Sun: What will be the fate of MySQL?
4. Automated MySQL Backups: phpMyBackupPro

Tags: mysql events