Suppose you have an existing MySQL event that has a comment. But the comment isn’t as helpful as you’d like it to be, and so you’d like to change it to something else.

This can be done easily with the ALTER EVENT statement along with the COMMENT clause. When we include the COMMENT clause in the ALTER EVENT statement, it replaces whatever comment was in the event with the new comment. If the event didn’t have a comment, then a new comment appears.

Example

Suppose we create an event with a comment like this:

CREATE EVENT insert_timestamp
    ON SCHEDULE
        EVERY 1 MONTH
    COMMENT 'Insert data'
    DO
        INSERT INTO EventTest ( EventTimestamp, InsertedBy ) 
            VALUES (NOW(), 'insert_timestamp');

The event was created along with a comment.

The above comment isn’t very helpful, so let’s change the comment:

ALTER EVENT insert_timestamp
    COMMENT 'Insert timestamp into table every month';

We’ve updated the comment.

When we update just the comment like this, the rest of the event remains unchanged. In other words, we can change an event’s comment without having to redefine the whole event.

We can use the SHOW CREATE EVENT statement to check the comment:

SHOW CREATE EVENT insert_timestamp;

Result:

+------------------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Event            | sql_mode                                                                                                                                  | time_zone | Create Event                                                                                                                                                                                                                                                                                                  | character_set_client | collation_connection | Database Collation |
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| insert_timestamp | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | SYSTEM    | CREATE DEFINER=`root`@`localhost` EVENT `insert_timestamp` ON SCHEDULE EVERY 1 MONTH STARTS '2024-04-10 16:37:46' ON COMPLETION NOT PRESERVE ENABLE COMMENT 'Insert timestamp into table every month' DO INSERT INTO EventTest ( EventTimestamp, InsertedBy ) 
            VALUES (NOW(), 'insert_timestamp') | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

We can see the updated COMMENT clause in the event’s definition.

We can also query the information schema:

SELECT 
    event_comment
FROM information_schema.events
WHERE event_schema = 'krankykranes'
AND event_name = 'insert_timestamp';

Result:

+-----------------------------------------+
| EVENT_COMMENT                           |
+-----------------------------------------+
| Insert timestamp into table every month |
+-----------------------------------------+

Remove the Comment

We can provide an empty string to remove the comment altogether:

ALTER EVENT insert_timestamp
    COMMENT '';

The above code removed the comment.

Let’s verify that:

SHOW CREATE EVENT insert_timestamp;

Result:

+------------------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Event            | sql_mode                                                                                                                                  | time_zone | Create Event                                                                                                                                                                                                                                                | character_set_client | collation_connection | Database Collation |
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| insert_timestamp | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | SYSTEM    | CREATE DEFINER=`root`@`localhost` EVENT `insert_timestamp` ON SCHEDULE EVERY 1 MONTH STARTS '2024-04-10 16:37:46' ON COMPLETION NOT PRESERVE ENABLE DO INSERT INTO EventTest ( EventTimestamp, InsertedBy ) 
            VALUES (NOW(), 'insert_timestamp') | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

We can see that the COMMENT clause has been removed from the event altogether.