Mysql event Scheduler

Hello guys, It’s being a long time when i havn’t written any blog. Now i will like to introduce a simple but very powerful concept of the mysql. Suppose you want to store a data according to the daily basis but after some days that data is not required to you. So you definitely want to delete that particular data but how you do that? This is the biggest question? what sort of coding should i do that it run after some event. So i will like to introduce a concept of mysql i.e. event schedule.

Event Scheduler: Events are executed by a special event scheduler thread; when we refer to the Event Scheduler, we actually refer to this thread.

DATABASE

// first of all create a table

create table counter(
id int(11) auto_increment,
username varchar(100),
created_at timestamp default current_timestamp,
primary key(id)
)

How to enable the event Scheduler?
Go to the MySql Event Scheduler and execute the following command.

//to enable the event scheduler
SET GLOBAL event_scheduler = ON;

Create a Event

create event insertUsername
on schedule every 1 second
Do
insert into counter(username)values('pradeep');

Output

 

still the process going on…. every second u get another value…

If you want to insert a value every 10 days later

create event insertUsername
on schedule every 10 day
Do
insert into counter(username)values('pradeep');

and so on.
Thus this small event scheduler is basically used where you have to fire a query at a particular time. For example if you want to send a push notification to a user at a particular time (i.e. around 12.0.0 clock) then you can create the schedule for that query to fire on particular time.

Alter an Event
If you want to make the changes in the event then you can make changes in the event by alter command

alter insertUsername
ON SCHEDULE EVERY 1 HOUR
STARTS TIMESTAMP + 1 HOUR

Show Event
If you want to show all the events running on particular database then use the below command

Show Events;

 

Drop an Event
If you want to drop the events running on particular database then use the below command

drop event insertUsername;