MySQL ALTER EVENT Statement



MySQL ALTER EVENT Statement

A MySQL Event is nothing but a task that execute at a particular schedule. An event can contain one or more MySQL statements these statements are stored in the databases and gets executed at the specified schedule.

The ALTER EVENT statement is used to change the characteristics of a MYSQL event.

Syntax

Following is the syntax of the MySQL ALTER EVENT statement −

ALTER EVENT old_event_name
RENAME TO new_event_name

Where, old_event_name is the name of the event you need to alter, new_event_name is the new name to which you need to rename the event.

Example (Renaming an event)

In the following query, we are creating a table named Data −

CREATE TABLE Data (Name VARCHAR(255), age INT);

Now, we create an event which inserts records into the above created table −

CREATE EVENT sample_event ON SCHEDULE EVERY 1 MONTH DO TRUNCATE TABLE data;

After creating an event if you verify the list of events using the SHOW EVENTS statements you can observe the created event in the list −

SHOW EVENTS;

The above show statement produces the following output −

***************** 1. row *****************
                  Db: test
                Name: sample_event
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 1
      Interval field: MONTH
              Starts: 2023-12-04 14:08:03
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

Following query renames the above created event sample_event −

ALTER EVENT sample_event RENAME TO new_event_name;

Verification

If you get the list of events using the SHOW EVENTS Statement again you can observe the changed name −

SHOW EVENTS;

Following is the output of the above query −

***************** 1. row *****************
                  Db: test
                Name: new_event_name
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 1
      Interval field: MONTH
              Starts: 2023-12-04 14:08:03
                Ends: NULL
              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)

Altering the schedule of an event

You can alter the schedule of an existing event by using the ON SCHEDULE clause of the ALTER statement.

Syntax

Following is the syntax of the MySQL ALTER EVENT statement to change the schedule of an existing event −

ALTER EVENT event_name
ON SCHEDULE new_time_stamp

Where, event_name is the name of the event you need to alter, new_time_stamp is the new time stamp value.

Example

Assume we have created an event which inserts a record into a data table after 1 minute of the current time stamp as shown below −

CREATE EVENT example_event ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 
1 Minute DO INSERT INTO new.Data VALUES('Rahman', 25);

Following query modifies the schedule of the above created event to every month −

ALTER EVENT example_event ON SCHEDULE EVERY 1 MONTH DO TRUNCATE TABLE data;

Altering the body of an event

You can alter the body of an existing event by using the DO clause of the ALTER statement.

Syntax

Following is the syntax of the MySQL ALTER EVENT statement −

ALTER EVENT event_name
DO event_body

Where, event_name is the name of the event you need to alter, event_body is the new body of the event.

Example

Assume we have created an event that inserts 3 records into a data table as shown below −

CREATE EVENT test_event ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 
Minute DO INSERT INTO Data VALUES('Rahman', 25), ('Ram', 35), ('Raj', 30);

If you verify the contents of the data table you can observe the inserted records −

select * from data;

Output

The above query produces the following output −

Name age
Rahman 25
Ram 35
Raj 30

Following query changes the body of the above event such that it truncates the contents of the data table −

ALTER EVENT test_event ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 
Minute DO TRUNCATE TABLE data;

If you verify the contents of the data table (again) you can observe the empty table as shown below −

select * from data;
Empty set (0.08 sec)

The COMMENT clause

You can add a comment while creating an event using the COMMENT clause as shown below −

Syntax

Following is the syntax to alter the comment if an existing event −

ALTER EVENT event_name COMMENT 'string';

Example

Assume we have created an event as shown below −

CREATE EVENT
event_hourly
ON SCHEDULE
EVERY 1 MONTH
COMMENT 'This truncates the data table each month'
DO
TRUNCATE TABLE data;

Following query alters the comment added above −

ALTER EVENT event_hourly COMMENT 'This is a new string';
Advertisements