Php

Simple Example of EVENT Scheduler In MySQL

in this post, I’m going to let you know about MySQL event scheduler and how to create events to automate repetitive database tasks. These tasks are similar to Linux cron jobs.

MySQL Events are named objects that have one or more SQL statements in them. They’re saved in the database and run at predetermined intervals.

MySQL Event Scheduler

MySQL Events are tasks that run on a schedule set by the user. The Event Scheduler is a thread that runs Events at a specified time.

If you need to delete all data from a table at a particular time, you can construct a cron job at the script level that will run at the specified time.

However, at the database level, you can define an EVENT that will RUN at a predetermined time.

How to check Event Scheduler is enabled

All scheduled events are executed by a specific thread called the event scheduler thread in MySQL. The below command can be used to see the status of the event scheduler thread:

SHOW PROCESSLIST

If the event scheduler is enabled, You will get the below lists:

How To Enable Event Scheduler

You can set the event_scheduler system variable to enable and start it using below command:

SET GLOBAL event_scheduler = ON;

MySQL Create Event

The CREATE EVENT statement creates a new event. Here is the basic syntax of the CREATE EVENT statement:

CREATE EVENT `event_name`
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
DO BEGIN
 -- event body
END;

in the above code, We’ll create and schedule a new event in MySQL.

The event will not run unless the Event Scheduler is enabled. The MySQL scheduled jobs can be assigned various settings. You can get more details from MySQL create event documentation.

MySQL CREATE EVENT Configuration

Let’s create some examples of creating new events and defined parameters.

Creating a one-time MySQL Event

AT 'YYYY-MM-DD HH:MM.SS'

Example: AT ‘2013-30-01 05:00.00’

Run Event Scheduler once after a specific period has elapsed

AT CURRENT_TIMESTAMP + INTERVAL n [HOUR|MONTH|WEEK|DAY|MINUTE]

Related Post

Example: AT CURRENT_TIMESTAMP + INTERVAL 1 DAY

Recurring Event Scheduler at Specific Intervals

EVERY n [HOUR|MONTH|WEEK|DAY|MINUTE]

Example: EVERY 1 DAY

Event Scheduler at specific intervals during a specific period

Using the Event Scheduler, you can schedule events at certain times throughout the day or week.

EVERY n [HOUR|MONTH|WEEK|DAY|MINUTE] STARTS date ENDS date

Example: EVERY 1 DAY STARTS CURRENT_TIMESTAMP + INTERVAL 1 WEEK ENDS ‘2012-01-01 00:00.00’

Schedule to Drop an Event

You can also be dropped once its schedule has expired with help of (ON COMPLETION NOT PRESERVE).

IF you will set (ON COMPLETION PRESERVE) then your event will preserve after the schedule.

Create Events in MySQL

The following statement creates a recurring event that executes every minute:

Create DEFINER=`root`@`localhost` EVENT `insights_app1`.`failedservices_truncate` ON 
 SCHEDULE EVERY 1 MINUTE
DO 
Delete From failedservices

The above EVENT run every 1 min.

How To Drop an Event

To remove an existing event, you use the DROP EVENT statement as follows:

DROP EVENT [IF EXIST] event_name;

How to Create View in MySQL

MySql View is very important factor of Database based on performance. View is virtual table that’s contains result set of your SQL statement. In SQL statement yo can use SQL functions, WHERE, and JOIN statements to get result set.
The View table can contain rows and column like your original table the column name may be different and unique in VIEW table. By default, the names of the columns retrieved by the SELECT statement are used for the view column names. The Columns name in view may be one or more real tables in the database.

Syntax to Create View in MySQL

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

A view can belong to a database. By default, a new view is created in the default database. To create the view explicitly in a given database, specify the name as db_name.view_name:

Create view in mysql with Example

CREATE VIEW test.view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Where: test is the database name

Recent Posts

Configure and Retrieve S3 Information Using Laravel PHP-AWS-SDK

This tutorial helps integrate a PHP SDK with Laravel. We'll install aws-php-sdk into laravel application and access all aws services… Read More

2 months ago

What is the Purpose of php_eol in PHP?

in this quick PHP tutorial, We'll discuss php_eol with examples. PHP_EOL is a predefined constant in PHP and represents an… Read More

8 months ago

Laravel Table Relationship Methods With Example

This Laravel tutorial helps to understand table Relationships using Elequonte ORM. We'll explore laravel table Relationships usage and best practices… Read More

8 months ago

Exploring the Power of Laravel Eloquent Join?

We'll explore different join methods of Laravel eloquent with examples. The join helps to fetch the data from multiple database… Read More

9 months ago

Quick and Easy Installation of Laravel Valet

in this Laravel tutorial, We'll explore valet, which is a development environment for macOS minimalists. It's a lightweight Laravel development… Read More

9 months ago

What is Laravel Soft Delete and How Does it Work?

I'll go through how to use soft delete in Laravel 10 in this post. The soft deletes are a method… Read More

9 months ago

Categories