Online visitors

People learning around the world

Online

—

Visitors exploring W3Schools

Online visitors by country Circles show approximate country locations. The country list provides the visitor counts.

Circles represent countries, not exact visitor locations.

Country

Online

Loading country activity…

    MySQL Event Scheduler

    MySQL Event Scheduler allows you to automate repetitive database tasks effortlessly. This tutorial covers the essentials of MySQL Event Scheduler, including how to create, modify, and delete events with examples.

    What is MySQL Event Scheduler?

    The Event Scheduler is a built-in MySQL database management system feature that allows you to execute SQL statements at specified intervals. You can use it to automate database operations, such as purging records, clearing logs, or generating reports, without needing external cron jobs or scheduled tasks.

    How to Enable MySQL Event Scheduler?

    MySQL Event Scheduler is disabled by default for security reasons. To enable it, you must set the event_scheduler system variable to ON. You can do this by running the following command:

    SET GLOBAL event_scheduler = ON;

    To check its status:

    SHOW VARIABLES LIKE 'event_scheduler';

    How to Create an Event?

    To create an event, you can use the CREATE EVENT statement. Here is the syntax:

    CREATE EVENT [event_name]
    ON SCHEDULE [schedule]
    DO [sql_statement];

    Here, the event_name is the name of the event, the schedule is the schedule on which the event should run, and the sql_statement is the SQL statement that should be executed when the event runs.

    For scheduling, you have two main options: use the AT clause for a one-time event or employ the EVERY clause for recurring.

    Create a Basic Event

    Here’s how to create a simple event that deletes records older than 30 days from a table named logs.

    -- Run Once at a Specific Date and Time
    
    CREATE EVENT purge_logs
    ON SCHEDULE AT '2023-09-15 12:00:00'
    DO
    DELETE FROM logs WHERE timestamp_column < NOW();
    
    
    -- Run Every Day
    
    CREATE EVENT purge_logs
    ON SCHEDULE EVERY 1 DAY
    DO
    DELETE FROM logs WHERE timestamp_column < NOW() - INTERVAL 30 DAY;
    

    Create a Complex Event

    Sometimes, you may need to create events that contain multiple SQL statements, or you may also need to call stored procedures. For example, you can move old logs from the logs table to the logs_archive table and then delete them from the logs table using the following events.

    CREATE EVENT purge_logs
    ON SCHEDULE EVERY 1 DAY
    DO
    BEGIN
      DECLARE EXIT HANDLER FOR SQLEXCEPTION
      BEGIN
        -- Handle the error
        ROLLBACK;
      END;
    
      START TRANSACTION;
    
      -- Archive the old records first
      INSERT INTO logs_archive (id, message, timestamp_column)
      SELECT id, message, timestamp_column FROM logs WHERE timestamp_column < NOW() - INTERVAL 30 DAY;
    
      -- Delete old records from the logs table
      DELETE FROM logs WHERE timestamp_column < NOW() - INTERVAL 30 DAY;
    
      COMMIT;
    END;
    

    Alter an Event

    To modify an existing event:

    ALTER EVENT purge_logs
    ON SCHEDULE EVERY 2 DAYS;
    

    Show Active Events

    To view all active events, run:

    SHOW EVENTS;
    

    Disable a Specific Event

    To turn off an event without deleting it:

    ALTER EVENT purge_logs DISABLE;
    

    Delete an Event

    To permanently remove an event:

    DROP EVENT IF EXISTS purge_logs;
    

    Conclusion

    This tutorial has covered the fundamentals of MySQL Event Scheduler, including how to create events with real-world examples. Learning to use MySQL Event Scheduler can significantly enhance your database management skills and productivity. Automating repetitive tasks lets you focus on more critical aspects of your work and let MySQL take care of the rest.



    Found This Page Useful? Share It!
    Get the Latest Tutorials and Updates
    Join us on Telegram

    Notifications

    Updates are enabled for this browser.

    Receive new tutorials, articles and learning resources. You can turn updates off anytime.