Events

A MySQL Event is like a scheduled task (cron job) that runs SQL statements at specific times or intervals.

It allows you to automate recurring database tasks without relying on external scripts or applications.

Key Points about Events

• Events are managed by the MySQL Event Scheduler (must be enabled).
• Syntax is similar to CREATE PROCEDURE, but with scheduling options.
• Can be one-time (run once at a specific date/time) or recurring (run periodically).
• Common uses:
  • Automating backups
  • Cleaning up old records
  • Summarizing data (daily/weekly reports)
  • Sending notifications (via tables/flags)

Enabling the Event Scheduler

By default, the event scheduler may be OFF. To enable:

SET GLOBAL event_scheduler = ON;

Check status:

SHOW VARIABLES LIKE 'event_scheduler';

Syntax of CREATE EVENT

CREATE EVENT event_name
ON SCHEDULE
    AT timestamp_value
    | EVERY interval [STARTS timestamp] [ENDS timestamp]
DO
   sql_statement;

• event_name → Unique name for the event.
• ON SCHEDULE → Defines when the event runs:
  • AT 'YYYY-MM-DD HH:MM:SS' → Run once at specific time.
  • EVERY interval → Repeat (e.g., EVERY 1 DAY).
• STARTS / ENDS → Define time range for recurring events.
• DO → The SQL statement (or block) to execute.

One-Time Event

Suppose we want to delete old logs tomorrow at midnight:

CREATE EVENT delete_old_logs
ON SCHEDULE AT '2025-08-26 00:00:00'
DO
   DELETE FROM logs WHERE log_date < NOW() - INTERVAL 30 DAY;

This event will run once at midnight and remove logs older than 30 days.

Recurring Event (Daily)

Suppose we want to archive old orders every day at midnight:

CREATE EVENT archive_orders
ON SCHEDULE EVERY 1 DAY
STARTS '2025-08-26 00:00:00'
DO
   INSERT INTO archived_orders
   SELECT * FROM orders WHERE order_date < NOW() - INTERVAL 90 DAY;

This will run daily at midnight, moving old orders into archived_orders.

Event with Multiple Statements (BEGIN...END)

We can use multiple SQL statements with BEGIN ... END:

DELIMITER //

CREATE EVENT daily_summary
ON SCHEDULE EVERY 1 DAY
DO
BEGIN
   DELETE FROM daily_summary_table;
   INSERT INTO daily_summary_table (dept_id, total_salary)
   SELECT department_id, SUM(salary)
   FROM employees
   GROUP BY department_id;
END //

DELIMITER ;

Every day, it clears and rebuilds a summary table of total salaries per department.

Managing Events

• View all events: SHOW EVENTS;
• View event definition: SHOW CREATE EVENT archive_orders
• Drop event: DROP EVENT archive_orders;
• Disable event (without deleting): ALTER EVENT archive_orders DISABLE;
• Enable event again: ALTER EVENT archive_orders ENABLE;