How to Create Event in MySQL: Syntax and Examples

To create an event in MySQL, use the CREATE EVENT statement with a schedule and an action. Events run SQL code automatically at specified times or intervals.

Syntax

The CREATE EVENT statement defines a scheduled task in MySQL. You specify the event name, schedule (when and how often it runs), and the SQL statements it executes.

• event_name: Name of the event.
• ON SCHEDULE: Defines the timing, like AT a specific time or EVERY interval.
• DO: The SQL statements to run when the event triggers.
• ENABLE/DISABLE: Controls if the event is active.

sql

CREATE EVENT event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
DO
sql_statement;

Example

This example creates an event named update_counter that runs every day at midnight to increment a value in a table.

sql

CREATE EVENT update_counter
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_DATE + INTERVAL 1 DAY
DO
  UPDATE counters SET count = count + 1 WHERE id = 1;

Output

Query OK, 0 rows affected (0.01 sec)

Common Pitfalls

Common mistakes when creating events include:

• Not enabling the event scheduler with SET GLOBAL event_scheduler = ON;.
• Forgetting to specify the schedule correctly, causing the event not to run.
• Using complex SQL without proper permissions or error handling.
• Assuming events run immediately without checking the STARTS time.

sql

/* Wrong: Event scheduler off, event won't run */
CREATE EVENT my_event
ON SCHEDULE EVERY 1 HOUR
DO
  DELETE FROM logs WHERE created_at < NOW() - INTERVAL 7 DAY;

/* Correct: Enable scheduler first */
SET GLOBAL event_scheduler = ON;
CREATE EVENT my_event
ON SCHEDULE EVERY 1 HOUR
DO
  DELETE FROM logs WHERE created_at < NOW() - INTERVAL 7 DAY;

Quick Reference

Clause	Description
CREATE EVENT event_name	Defines the event name.
ON SCHEDULE	Sets when and how often the event runs (e.g., EVERY 1 DAY, AT timestamp).
STARTS	Optional start time for the event.
ENDS	Optional end time for the event.
DO	SQL statements to execute when the event runs.
ENABLE / DISABLE	Turns the event on or off.
ON COMPLETION PRESERVE	Keeps the event after it finishes (for one-time events).

Key Takeaways

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

Use CREATE EVENT with ON SCHEDULE and DO clauses to define when and what the event runs.

Specify start and end times to control event execution periods.

Events run automatically in the background without manual triggers.

Check permissions and syntax carefully to avoid silent failures.