MySQL — Events

Basic Event

DELIMITER //
CREATE EVENT cleanup_old_logs
ON SCHEDULE EVERY 1 DAY
DO
BEGIN
    DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
END //
DELIMITER ;

One-Time Event

CREATE EVENT run_once
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
    UPDATE counters SET value = value + 1 WHERE name = 'hourly';

Schedule Options

Syntax	Description
EVERY 1 HOUR	Repeating interval
EVERY 7 DAY	Weekly
AT '2025-01-01 00:00:00'	Specific time
STARTS ... ENDS ...	Time window

Event with Start and End

DELIMITER //
CREATE EVENT nightly_report
ON SCHEDULE EVERY 1 DAY
STARTS '2025-01-01 02:00:00'
ENDS '2025-12-31 02:00:00'
DO
BEGIN
    INSERT INTO daily_reports (report_date, total_orders)
    SELECT CURDATE(), COUNT(*) FROM orders WHERE DATE(order_date) = CURDATE();
END //
DELIMITER ;

Enabling/Disabling Events

ALTER EVENT cleanup_old_logs DISABLE;
ALTER EVENT cleanup_old_logs ENABLE;

Viewing Events

SHOW EVENTS;
SHOW EVENTS FROM my_database;
SELECT * FROM information_schema.EVENTS;

Dropping Events

DROP EVENT IF EXISTS cleanup_old_logs;

Enable Event Scheduler

SET GLOBAL event_scheduler = ON;
SHOW VARIABLES LIKE 'event_scheduler';

Mini Practice

Write SQL code that:

1. Creates a repeating event
2. Creates a one-time event
3. Enables and disables an event
4. Views all events in the database

Up Next

Continue with Transactions — ensuring data consistency with BEGIN, COMMIT, and ROLLBACK.