Reoccurring Events in MySQL

12 September, 2019

What are Events in MySQL?

Events in MySQL are something that can happen every x amount of time. If you have something that you want to reoccur ever now and then, you should consider using events.

Example

CREATE EVENT IF NOT EXISTS `remove_login_attempts` # Create & Name Event
ON
  SCHEDULE EVERY 1 MINUTE # When to redo task
  ON COMPLETION PRESERVE
  ENABLE
DO
  DELETE FROM login_attempts WHERE time < (NOW() - INTERVAL 15 MINUTE); # The code to run

What Is The Code Doing

  1. Create event and name it.

    CREATE EVENT IF NOT EXISTS `remove_login_attempts`

  2. How long it should wait until it runs the code again

    ON SCHEDULE EVERY 1 MINUTE

  3. Keep the event after it has ran

    This option isn’t really necessary on an event that never stops running.

    ON COMPLETION PRESERVE
    
    # Alternatively you can remove the event once it is done:
    # ON COMPLETION NOT PRESERVE
  4. Enable the event

    ENABLE

  5. What code to run (This code should be after ‘DO’)

    DO

  6. The command/code to run (this is an example, you can tell it to do anything)

    DELETE FROM login_attempts WHERE time < (NOW() - INTERVAL 15 MINUTE);