Events in MySQL

Events in MySQL are tasks that run according to a defined Schedule. events are executed based on schedule so it is also called a Scheduled event. in this article, we will be learning about events with MySQL. 

Events can be created for one-time execution or for certain intervals. It consists of some MySQL statements which get executed on the happening of the event.

Application of Event:

1.  The event can be used for various optimization over collected data on a frequent basis like weekly, or monthly.
2. It can be used to track various details on available data.
3. This is used to maintain a large eCommerce-based database where there is a need of monitoring the stock units of available products. 
4. It can be used to clean up log records of the visiting users on-site weekly or after a certain time,

Scheduled events are executed by a special thread called an event scheduler. we can check all running events’ names by typing Command. 

SHOW EVENTS;

 The above statement will list all the events with their name. In order to execute any event firstly we need to ensure that the event scheduler is set to ON 

Event Scheduler:

To check the status of the event scheduler we can execute the following Command,

 SHOW VARIABLES
 WHERE VARIABLE_NAME = 
 'event_scheduler';

Output:

if the status of the event scheduler_id Off then we can set it as ON by executing Following Command.

SET GLOBAL event_scheduler = ON:

Creating an event:

Syntax:

CREATE EVENT  event_name
ON SCHEDULE schedule
DO
event_body

The event name must be unique. To execute the event in a repetitive manner EVERY keyword can be used. 

let’s create a simple event that will check after every 10 minutes how many stocks are there in the table. We will use the following schema for Product_mastr:

product_id	product_name	Qty
001	headphones	20
002	Mobile Phone	10

We will have another empty table we named as order_stock. This table will have the data which need to be ordered by shop keeper these things never get out of stock.

product_id

qty

time

create an event that checks after every 10 minutes on the Product_mastr table, to track the product quantity and insert quantity with a timestamp.

mysql> DELIMITER //
mysql> CREATE EVENT LOG_QTY
   -> ON SCHEDULE EVERY 10 MINUTE
   -> DO BEGIN
   -> insert into order_stock(product_id,qty_which_needToBeOrdered,time)
   select product_id,qty as qty_which_needToBeOrde                             
   red,CURRENT_TIMESTAMP() from product_mastr;
   -> END;
   -> //

Now lets Check the ORDER_STOCK table:

select * from order_stock;

Output:

log record would be added in a repetitive manner to the order_stock table after every 10 minutes. So this is How events can be useful for various purposes Like tracking of various data, Table optimization or clearing this kind of records after certain time, all this kind off things can be achieved with the help of MySQL Event.