MySQL + Event Scheduler: How to delete data from MYSQL table using Event Scheduler

Deleting or removing data in MySQL can be complicated, especially if you do it frequently or according to a schedule, such as regularly cleaning up data. Fortunately, we can automate and schedule this task using MySQL Events Scheduler. This article aims to show you how to use MySQL Event Scheduler to schedule a DELETE query to remove data automatically from a MySQL table at a specific time and date or at regular intervals.

To use MySQL event scheduler for data deletion, you must follow these steps:

Grant the EVENT privilege by using the GRANT statement.
Set up the “event_scheduler” system variable to ON.
Create an event to delete data using the CREATE EVENT statement.
Specify the event scheduler parameters and clauses (schedule, do, AT, EVERY, ON COMPLETION…).
Write the SQL DELETE statements.

Related Articles:

This article will cover:

Step1: Grant EVENT privilege using the GRANT statement
Step2: Set up the “event_scheduler” system variable
Step3: Create an event scheduler to delete data using the CREATE EVENT statement.
Step4: Specify the event scheduler parameters and clauses
Step5: Write the SQL DELETE statements.
Example:
Conclusion:
You might also like:

Step1: Grant EVENT privilege using the GRANT statement

To perform any actions such as SELECT, INSERT, DELETE … in a SQL statement, the event user or definer must have the requisite privilege. To know more about Event privilege, check HERE.

You can grant this privilege to a specific user by using the GRANT statement as follows:

 GRANT EVENT ON myschemaName.* TO userAccount;

Step2: Set up the “event_scheduler” system variable

The next step is to activate the event scheduler by setting the “event scheduler” system variable to “ON” before you can use it to delete data. You may accomplish this by running the following command:

 SET GLOBAL event_scheduler = ON;

Step3: Create an event scheduler to delete data using the CREATE EVENT statement.

Once the event scheduler is activated, you can use the CREATE EVENT statement to create an event to remove data from your database table.

To create a New Event scheduler in MySQL using the CREATE EVENT statement, you need to set up the minimum requirements such as name, an ON SCHEDULE clause to specify when and how often the event will execute, and a DO clause which contains an SQL statement to be executed by the event. For more details about the parameters and clauses, check this post: MySQL + Event Scheduler: How to use MySQL Event Scheduler in Detail.

The syntax for this statement is as follows:

 CREATE EVENT event_name

 ON SCHEDULE schedule

 DO event_body;

Step4: Specify the event scheduler parameters and clauses

Inside the CREATE EVENT clauses, you can set up several parameters that can be used to specify the scheduling information and conditions. For more details about the parameters and clauses, check this post: MySQL + Event Scheduler: How to use MySQL Event Scheduler in Detail.

 CREATE
   [DEFINER = user]
   EVENT
   [IF NOT EXISTS]
   event_name
   ON SCHEDULE schedule
   [ON COMPLETION [NOT] PRESERVE]
   [ENABLE | DISABLE | DISABLE ON SLAVE]
   DO event_body;

DEFINER: We set the DEFINER to the user account that created the event in ‘user_name‘@’host_name‘ format. This clause specifies the MySQL user account to be used when checking access privileges at event execution time.

IF NOT EXISTS: has the same meaning for CREATE EVENT as for CREATE TABLE: If an event named eventName already exists in the same schema, no action is taken, and no error results. (However, a warning is generated in such cases.)

schedule parameter: In the ON SCHEDULE clause, you may use expressions involving built-in MySQL functions and user variables to obtain any of the timestamp or interval values it contains. Still, you can not use stored or loadable functions in such expressions and using them will fail with an error message.

{
AT timestamp [+ INTERVAL interval] …
| EVERY interval
[STARTS timestamp [+ INTERVAL interval] …]
[ENDS timestamp [+ INTERVAL interval] …]
}

AT specifies the time the event should start and run only once.
EVERY specifies the interval at which the event should repeat. For example, you can schedule an event to run every day at a particular time or every week on a specific day and time.

ON COMPLETION: Takes one of the two values, PRESERVE or NOT PRESERVE. And we use this parameter to define whether the event should be dropped after it runs.

ENABLE or DISABLE: This parameter activates or deactivates the event.

event_body: Contains the SQL statements of the DO clause that specifies an action executed by the event. In our case is the MySQL DELETE statement (see the following section).

Step5: Write the SQL DELETE statements.

The SQL DELETE statement is the SQL statement that will delete all data from the specified table where the condition is met. So it is crucial to be careful when writing and using the DELETE statement and always make that you have a backup of your data before proceeding. You can use the LIMIT clause to limit the number of rows deleted per event and avoid performance issues.

 
 CREATE EVENT event_name

 ON SCHEDULE schedule

 DO DELETE FROM table_name WHERE condition;

Example:

The following event scheduler example (cleanEvent ) performs a regular cleaning up of data on the table named MyCleanTable every 24 hours for all the data older than three months.

 CREATE EVENT cleanEvent ON SCHEDULE EVERY 24 HOUR ENABLE
  DO 
  DELETE FROM MyCleanTable
  WHERE `timestamp_column` < CURRENT_TIMESTAMP - INTERVAL 6 MONTH;

Conclusion:

Using MySQL’s event scheduler to remove data is a powerful tool for automating tasks and maintaining tidy and organized databases. Keep in mind to always test the conditions before using the DELETE statement, and create a backup of your data before starting. I hope this article was helpful!