CODEWITHSUNDEEP
mysqldatabasetriggers
e.klara.k
e.klara.k

Reputation: 379

Delete old rows MySQL Trigger - beginner

I am looking for a way to delete old rows from my MySQL table and I have not really understood the questions and answears I have found so I was hoping that someone could clarify. I have added a Timestamp column to my table where I store the date the row was created. If the row is older than four months, I wan't to delete it. I have looked into triggers but if I have understood correctly, an insert trigger only alows you to work with the row that you insert? Maybe I'm wrong but I'm very new to triggers and don't even understand when/how to run them? Is it enough to just run them once, will the action automatically be repeated every time someone insert something (if it is an insert trigger)?

I run my insertQuery very often, everytime someone requests an URL to their painting. I am using php and my table is structured like this:

DatabaseID || theKey  || Timestamp
1             abcd       2016-01-02
2             a1bc       2016-01-03
3             a1sb       2016-01-03
4             a12b       2016-01-05

EDIT: Forgot to mention, I would like the deletion of old rows to be automatic and run at least once a week.

Upvotes: 1

Views: 6337

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

It would be ideal to have a mysql event scheduler http://dev.mysql.com/doc/refman/5.7/en/create-event.html

As a first step you need to activate the scheduler as

SET GLOBAL event_scheduler = ON;

Note that if the server is restarted then it will need to reset the above so better to set event_scheduler=on somewhere under the [mysqld] section in the default mysql config file, usually /etc/my.cnf, make sure to restart the mysql server

Once the scheduler is set run following to see if its working and you should see something as 

show variables like '%event_scheduler%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+

Finally on the terminal write the scheduler as

delimiter //
create event if not exists clean_old_records
on schedule every 1 week
do
begin
 delete from your_table_name 
 where 
 Timestamp < date_sub(curdate(),interval 1 month) ;
end; //
delimiter ;

In the example above it will delete records older than one month and you can have your own value.

Upvotes: 7

Related Questions