How to Create Recurring Event in MySQL: Syntax and Example

To create a recurring event in MySQL, use the CREATE EVENT statement with the ON SCHEDULE clause specifying EVERY interval. Enable the event scheduler with SET GLOBAL event_scheduler = ON; to run events automatically at defined intervals.

Syntax

The basic syntax to create a recurring event in MySQL includes:

• CREATE EVENT event_name: Names the event.
• ON SCHEDULE EVERY interval: Sets how often the event runs (e.g., every 1 day, every 5 minutes).
• STARTS timestamp (optional): When the event starts.
• DO statement: The SQL statement to execute each time the event runs.

sql

CREATE EVENT event_name
ON SCHEDULE EVERY interval
[STARTS timestamp]
DO
  sql_statement;

Example

This example creates a recurring event that runs every day at midnight to delete old records from a table named logs older than 30 days.

sql

SET GLOBAL event_scheduler = ON;

CREATE EVENT delete_old_logs
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_DATE + INTERVAL 1 DAY
DO
  DELETE FROM logs WHERE log_date < NOW() - INTERVAL 30 DAY;

Output

Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)

Common Pitfalls

Common mistakes when creating recurring events include:

• Not enabling the event scheduler (event_scheduler must be ON).
• Forgetting to specify the EVERY interval, which makes the event run only once.
• Incorrect time intervals or start times causing unexpected schedules.
• Not having proper privileges to create events.

sql

/* Wrong: Event scheduler is off, event won't run */
CREATE EVENT my_event
ON SCHEDULE EVERY 1 DAY
DO
  UPDATE my_table SET processed = 1;

/* Right: Enable scheduler first */
SET GLOBAL event_scheduler = ON;

CREATE EVENT my_event
ON SCHEDULE EVERY 1 DAY
DO
  UPDATE my_table SET processed = 1;

Quick Reference

Clause	Description	Example
CREATE EVENT event_name	Defines the event name	CREATE EVENT cleanup_logs
ON SCHEDULE EVERY interval	Sets how often the event runs	ON SCHEDULE EVERY 1 HOUR
STARTS timestamp	Optional start time for the event	STARTS '2024-07-01 00:00:00'
DO sql_statement	SQL to execute when event runs	DO DELETE FROM logs WHERE date < NOW() - INTERVAL 30 DAY
SET GLOBAL event_scheduler = ON	Enables event scheduler globally	SET GLOBAL event_scheduler = ON;

Key Takeaways

Enable the event scheduler with SET GLOBAL event_scheduler = ON before creating events.

Use CREATE EVENT with ON SCHEDULE EVERY to define recurring intervals.

Specify STARTS to control when the event begins running.

Ensure you have the necessary privileges to create and run events.

Test your event logic carefully to avoid unintended data changes.