Đăng nhập

Tin tức sự kiện

Kỹ thuật lập trình

Phần mềm nguồn mở

Web Interface

Cấu trúc máy tính

Góc nhìn lập trình viên

Trung tâm tập Yoga cho mọi người

Tự động chạy câu lệnh my sql bằng cách thực thi MySQL Event Scheduler



If you are running a large public web applications like shopping and emails portals, you have handle lots of unwanted data rows for example spam emails and unused shopping cart data. Sure it will created problem in database overload. So that I want to explain a simple tip called how to use MySQL event scheduler for deleting unwanted data rows from database.

Database
Sample database cart table contains four columns cart_id, user_id, product_id and created_at.

CREATE TABLE cart
(
cart_id INT  AUTO_INCREMENT,
user_id INT,
product_id INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (cart_id)
) ;

Enable MySQL Event Scheduler
Start MySQL event scheduler by executing following query in PhpMyAdmin or MySQL command prompt.

SET GLOBAL event_scheduler = ON;
Or
SET GLOBAL event_scheduler = 1;

MySQL event create

Create a Event
Here the following event will run everyday and clear/delete 10 days old data from cart table based on time stamp


CREATE EVENT newEvent
ON SCHEDULE EVERY 1 DAY
DO
DELETE FROM cart WHERE created_at <= DATE_SUB(NOW(), INTERVAL 10 DAY) ;

MySQL event create

Alter Event
If you want to modify the event run time simple you can execute a query in below syntax.

ALTER newEvent
ON SCHEDULE EVERY 5 HOUR
STARTS TIMESTAMP + 3 HOUR 

View Event
Show all the running events.

SHOW EVENTS;

MySQL event create

Delete Event
You can drop the event for executing below query.


DROP EVENT newEvent;

Check event schedule status under process tab in PhpMyAdmin root (it will show only when scheduled event is running).


MySQL event create