MySQL DROP EVENT

Summary: in this tutorial, you will learn how to use the MySQL DROP EVENT statement to remove one or more events from the database.

Introduction to the MySQL DROP EVENT statement

The DROP EVENT statement allows you to remove one or more scheduled events from the MySQL event scheduler.

Here’s the basic syntax of the DROP EVENT statement:

DROP EVENT [IF EXISTS] event_name [, event_name] ...;Code language: SQL (Structured Query Language) (sql)

In this syntax:

• IF EXISTS: An optional clause that prevents an error from occurring if the specified event does not exist.
• event_name: The name of the event you want to drop. You can specify multiple events, separated by commas.

Note that you can use the wildcard % in the event name to remove all the events that match a specific pattern.

To perform the DROP EVENT statement, you need to have EVENT privilege for the database to which the event belongs.

MySQL DROP EVENT statement example

We’ll show you how to create an event and remove it using the DROP EVENT statement.

First, create a new database mydb and a new table event_logs:

CREATE DATABASE IF NOT EXISTS mydb;

USE mydb;

CREATE TABLE IF NOT EXISTS event_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    message VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Code language: SQL (Structured Query Language) (sql)

Second, create an event called log_writter that writes a message to the event_logs table every second from the current timestamp within an hour:

CREATE EVENT log_writter
ON SCHEDULE EVERY 1 SECOND
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
   INSERT INTO event_logs(message) 
   VALUES (CONCAT('Event executed at ',NOW()));Code language: SQL (Structured Query Language) (sql)

In this example:

• log_writer is the name of the event.
• The event is scheduled to run every second within one hour from the current timestamp
• The event inserts a message into the event_logs table.

Third, show the event to confirm that the event has been created successfully:

SHOW EVENTS\GCode language: SQL (Structured Query Language) (sql)

Output:

*************************** 1. row ***************************
                  Db: mydb
                Name: log_writter
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 1
      Interval field: SECOND
              Starts: 2024-01-05 13:55:55
                Ends: 2024-01-05 14:55:55
              Status: ENABLED
          Originator: 1
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)Code language: CSS (css)

Third, remove the log_writter event using the DROP EVENT statement:

DROP EVENT IF EXISTS log_writter;Code language: SQL (Structured Query Language) (sql)

Finally, verify the event removal by displaying the event list:

SHOW EVENTS;

Output:

Empty set (0.00 sec)Code language: JavaScript (javascript)

The output indicates that the event has been removed successfully.

Summary

• Use MySQL DROP EVENT statement to remove an event from the database.