MySQL ALTER EVENT

Summary: in this tutorial, you will learn how to use the MySQL ALTER EVENT to modify an existing event.

Introduction to MySQL ALTER EVENT statement

In MySQL, an event is a task that runs according to a schedule. Because of this, an event is also known as a scheduled event.

MySQL allows you to change various attributes of an existing event using the ALTER EVENT statement. Here’s the basic syntax of the ALTER EVENT statement:

ALTER EVENT [IF EXISTS] event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[COMMENT 'comment']
[ENABLE | DISABLE]
DO event_bodyCode language: SQL (Structured Query Language) (sql)

In this syntax:

• IF EXISTS: This optional clause prevents an error from occurring if the specified event does not exist.
• event_name: The name of the event you want to change.
• ON SCHEDULE: Specifies the new schedule for the event, including frequency, start time, and end time.
• ON COMPLETION: Indicates whether you want to preserve or drop the event after it is completed. Use PRESERVE to keep the event, and NOT PRESERVE to drop it.
• COMMENT 'comment': An optional comment describing the event.
• ENABLE | DISABLE: Allows you to enable or disable the event. Note that a disabled event will not run until you re-enable it.
• DO event_body: Specifies the new SQL statement(s) or procedure to be executed by the event.

MySQL ALTER EVENT examples

We’ll create the mydb database that contains a messages table:

CREATE DATABASE IF NOT EXISTS mydb;

USE mydb;

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

The following statement creates an event that inserts a new row into the messages table every minute.

CREATE EVENT test_event
ON SCHEDULE EVERY 1 MINUTE
DO
   INSERT INTO messages(message)
   VALUES('Test ALTER EVENT statement');Code language: SQL (Structured Query Language) (sql)

1) Changing the schedule

The following example uses the ALTER EVENT statement to change the schedule of the test_event to make the event run every 2 minutes:

ALTER EVENT test_event
ON SCHEDULE EVERY 2 MINUTE;Code language: SQL (Structured Query Language) (sql)

2) Changing the event body

The following example uses the ALTER EVENT statement to change the body of the event by specifying the new query after the DO keyword:

ALTER EVENT test_event
DO
   INSERT INTO messages(message)
   VALUES('New message');Code language: SQL (Structured Query Language) (sql)

You can wait for 2 minutes and verify the changes by retrieving the data from the messages table:

SELECT * FROM messages;Code language: SQL (Structured Query Language) (sql)

3) Disabling an event

The following example uses the ALTER EVENT statement to disable the test_event:

ALTER EVENT test_event
DISABLE;Code language: SQL (Structured Query Language) (sql)

You can check the status of the event using the SHOW EVENTS statement as follows:

SHOW EVENTS FROM mydb;

4) Enabling an event

The following example uses the ALTER EVENT statement to enable an event using the ENABLE keyword:

ALTER EVENT test_event
ENABLE;Code language: SQL (Structured Query Language) (sql)

5) Renaming an event

To change the name of an event from one to another, you can use the ALTER EVENT ... RENAME TO statement:

ALTER EVENT test_event
RENAME TO sample_event;Code language: SQL (Structured Query Language) (sql)

6) Moving an event to another database

The ALTER EVENT ... RENAME TO statement also allows you to move an event from a database to another database:

ALTER EVENT mydb.sample_event
RENAME TO newdb.test_event;Code language: SQL (Structured Query Language) (sql)

It is assumed that the newdb database is available in your MySQL database server.

Summary

• Use MySQL ALTER EVENT to change the attributes of an event.