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

mysql determines whether the current time is between the start and end time and the start and end time are allowed to be empty

Blog

Pure js to achieve the effect of carousel

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

mysql determines whether the current time is between the start and end time and the start and end time are allowed to be empty

Pure js to achieve the effect of carousel

A Preliminary Study on JSBridge in Javascript

Optimized record of using IN data volume in Mysql

Implementation of Docker cross-host network (manual)

Summary of MySQL 8.0 memory-related parameters

How to deploy the crownblog project to Alibaba Cloud using docker

Practical record of optimizing MySQL tables with tens of millions of data

How to configure Nginx virtual host in CentOS 7.3

A brief analysis of the use of zero copy technology in Linux

Recommend

MySQL 5.7.20 installation and configuration method graphic tutorial (win10)

Vue.js implements calendar function

JavaScript Basics: Error Capture Mechanism

Detailed explanation of Nginx configuration file

Summary of various postures of MySQL privilege escalation

Specific usage of Vue's new toy VueUse

Detailed tutorial on installing phpMyAdmin on Ubuntu 18.04

How to use Flex layout to achieve scrolling of fixed content area in the head

A brief explanation of the reasonable application of table and div in page design

How to dynamically modify the replication filter in mysql

The difference and usage of LocalStorage and SessionStorage in vue

How to use the flash plug-in to call the PC's camera and embed it into the TML page

Ideas and codes for implementing iframe to intercept part of the website content

Drop-down menu implemented by HTML+CSS3+JS

React implements multi-component value transfer function through conetxt