Detailed explanation of MySQL event modification events (ALTER EVENT), disabling events (DISABLE), enabling events (ENABLE), event renaming and database event migration operations

Detailed explanation of MySQL event modification events (ALTER EVENT), disabling events (DISABLE), enabling events (ENABLE), event renaming and database event migration operations

This article uses examples to describe the operations of modifying MySQL events (ALTER EVENT), disabling events (DISABLE), enabling events (ENABLE), renaming events, and migrating database events. Share with you for your reference, the details are as follows:

We need to know that MySQL allows us to change various properties of existing events. If we want to change an existing event, we can use the ALTER EVENT statement as follows:

ALTER EVENT event_name
ON SCHEDULE schedule
ON COMPLETION [NOT] PRESERVE
RENAME TO new_event_name
ENABLE | DISABLE
DO
 event_body

The ALTER EVENT statement only works on existing events. If we try to modify an event that does not exist, MySQL will issue an error message, so before changing an event, we should first check the existence of the event using the SHOW EVENTS statement:

mysql> SHOW EVENTS FROM testdb;
+--------+---------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+
| Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation |
+--------+---------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+
| testdb | test_event_02 | root@localhost | SYSTEM | ONE TIME | 2017-08-03 04:24:48 | NULL | NULL | NULL | NULL | DISABLED | 0 | utf8 | utf8_general_ci | utf8_general_ci |
+--------+---------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+
1 row in set

Create a sample event that inserts a new record into the messages table every minute to demonstrate how to use the various features of the ALTER EVENT statement:

USE testdb;
CREATE EVENT test_event_04
ON SCHEDULE EVERY 1 MINUTE
DO
  INSERT INTO messages(message,created_at)
  VALUES('Test ALTER EVENT statement',NOW());

Let's modify the event to run every 2 minutes:

ALTER EVENT test_event_04
ON SCHEDULE EVERY 2 MINUTE;

We can also change the body of the event by specifying new logic:

ALTER EVENT test_event_04
DO
  INSERT INTO messages(message,created_at)
  VALUES('Message from event',NOW());
-- Clear the data in the table truncate messages;

After the modification is completed, you can wait for 2 minutes and view the messages table again:

mysql> SELECT * FROM messages;
+----+--------------------+---------------------+
| id | message | created_at |
+----+--------------------+---------------------+
| 1 | Message from event | 2017-08-03 04:46:47 |
| 2 | Message from event | 2017-08-03 04:48:47 |
+----+--------------------+---------------------+
2 rows in set

We can disable an event by using the DISABLE keyword after the ALTER EVENT statement:

ALTER EVENT test_event_04
DISABLE;

We can also view the status of events by using the SHOW EVENTS statement:

mysql> SHOW EVENTS FROM testdb;
+--------+---------------+----------------+-----------+-----------+---------------------+----------------+-----------------+------+---------+------------+----------------------+---------------------+
| Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation |
+--------+---------------+----------------+-----------+-----------+---------------------+----------------+-----------------+------+---------+------------+----------------------+---------------------+
| testdb | test_event_02 | root@localhost | SYSTEM | ONE TIME | 2017-08-03 04:24:48 | NULL | NULL | NULL | NULL | DISABLED | 0 | utf8 | utf8_general_ci | utf8_general_ci |
| testdb | test_event_04 | root@localhost | SYSTEM | RECURRING | NULL | 2 | MINUTE | 2017-08-03 04:44:47 | NULL | DISABLED | 0 | utf8 | utf8_general_ci | utf8_general_ci |
+--------+---------------+----------------+-----------+-----------+---------------------+----------------+-----------------+------+---------+------------+----------------------+---------------------+
2 rows in set

We can enable an event by using the ENABLE keyword after the ALTER EVENT statement:

ALTER EVENT test_event_04
ENABLE;

Check the event status:

mysql> SHOW EVENTS FROM testdb;
+--------+---------------+----------------+-----------+-----------+---------------------+----------------+-----------------+------+---------+------------+----------------------+---------------------+
| Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation |
+--------+---------------+----------------+-----------+-----------+---------------------+----------------+-----------------+------+---------+------------+----------------------+---------------------+
| testdb | test_event_02 | root@localhost | SYSTEM | ONE TIME | 2017-08-03 04:24:48 | NULL | NULL | NULL | NULL | DISABLED | 0 | utf8 | utf8_general_ci | utf8_general_ci |
| testdb | test_event_04 | root@localhost | SYSTEM | RECURRING | NULL | 2 | MINUTE | 2017-08-03 04:44:47 | NULL | ENABLED | 0 | utf8 | utf8_general_ci | utf8_general_ci |
+--------+---------------+----------------+-----------+-----------+---------------------+----------------+-----------------+------+---------+------------+----------------------+---------------------+
2 rows in set

Let's try to rename an existing event using ALTER EVENT:

ALTER EVENT test_event_04
RENAME TO test_event_05;

To view the event status:

mysql> SHOW EVENTS FROM testdb;
+--------+---------------+----------------+-----------+-----------+---------------------+----------------+-----------------+------+---------+------------+----------------------+---------------------+
| Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation |
+--------+---------------+----------------+-----------+-----------+---------------------+----------------+-----------------+------+---------+------------+----------------------+---------------------+
| testdb | test_event_02 | root@localhost | SYSTEM | ONE TIME | 2017-08-03 04:24:48 | NULL | NULL | NULL | NULL | DISABLED | 0 | utf8 | utf8_general_ci | utf8_general_ci |
| testdb | test_event_05 | root@localhost | SYSTEM | RECURRING | NULL | 2 | MINUTE | 2017-08-03 04:44:47 | NULL | ENABLED | 0 | utf8 | utf8_general_ci | utf8_general_ci |
+--------+---------------+----------------+-----------+-----------+---------------------+----------------+-----------------+------+---------+------------+----------------------+---------------------+
2 rows in set

You can then move events from one database to another by using the RENAME TO clause:

ALTER EVENT testdb.test_event_05
RENAME TO newdb.test_event_05;

Let’s check the event status:

mysql> SHOW EVENTS FROM newdb;
+-------+---------------+----------------+-----------+-----------+------------+----------------+-----------------+---------------------+------+---------+------------+----------------------+--------------------+
| Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation |
+-------+---------------+----------------+-----------+-----------+------------+----------------+-----------------+---------------------+------+---------+------------+----------------------+--------------------+
| newdb | test_event_05 | root@localhost | SYSTEM | RECURRING | NULL | 2 | MINUTE | 2017-08-03 04:44:47 | NULL | ENABLED | 0 | utf8 | utf8_general_ci | utf8_general_ci |
+-------+---------------+----------------+-----------+-----------+------------+----------------+-----------------+---------------------+------+---------+------------+----------------------+--------------------+
1 row in set

Okay, that’s all for this record.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "Summary of MySQL Index Operation Skills", "Summary of MySQL Common Functions", "Summary of MySQL Log Operation Skills", "Summary of MySQL Transaction Operation Skills", "Summary of MySQL Stored Procedure Skills" and "Summary of MySQL Database Lock-Related Skills".

I hope this article will be helpful to everyone's MySQL database design.

You may also be interested in:
  • How to rename the table in MySQL and what to pay attention to
  • MySQL database rename fast and safe method (3 kinds)
  • Detailed explanation of a method to rename procedure in MYSQL
  • Rename fields using SQL statements in MySQL
  • MySQL database rename statement sharing
  • Teach you how to use Python to operate MySql database
  • Develop a small tool to operate MySQL using Python
  • Python Basics: Operating MySQL Database
  • Python chat room with interface implementation example code (tkinter, Mysql, Treading, socket)
  • Sharing simple steps of Python operating MySQL database
  • Detailed explanation of the idea of ​​using Python sql statements to perform multi-condition fuzzy queries on mysql database
  • Implementing Add, Delete, Modify and Check in Python with tkinter+MySQL
  • Use Python to quickly rename the MySQL database

<<:  JavaScript canvas to load pictures

>>:  How to configure Bash environment variables in Linux

Recommend

JS implements sliding up and down on the mobile terminal one screen at a time

This article shares with you the specific code of...

Differences between Windows Server win2003, win2008R2, win2012, win2016, and win2019 system versions

Recently, Microsoft released the 2019 server syst...

Detailed tutorial for installing the unzipped version of mysql5.7.28 winx64 on windows

Table of contents 1. Unzip 2. Create a data folde...

Detailed explanation of the solution to Ubuntu dual system stuck when starting

Solution to Ubuntu dual system stuck when startin...

MySQL time types and modes details

Table of contents 1. MySQL time type 2. Check the...

Detailed explanation of the 14 common HTTP status codes returned by the server

HTTP Status Codes The status code is composed of ...

MySQL json format data query operation

The default table name is base_data and the json ...

The implementation principle of Tomcat correcting the JDK native thread pool bug

To improve processing power and concurrency, Web ...

Docker Detailed Illustrations

1. Introduction to Docker 1.1 Virtualization 1.1....

How to change the system language of centos7 to simplified Chinese

illustrate When you install the system yourself, ...

Disabled values ​​that cannot be entered cannot be passed to the action layer

If I want to make the form non-input-capable, I se...

MySql sharing of null function usage

Functions about null in MySql IFNULL ISNULL NULLI...

CSS naming conventions (rules) worth collecting Commonly used CSS naming rules

CSS naming conventions (rules) Commonly used CSS ...

Example of configuring multiple SSL certificates for a single Nginx IP address

By default, Nginx supports only one SSL certifica...

Docker uses the mysqldump command to back up and export mysql data in the project

mysqldump command Introduction: A database backup...