1

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.

e.klara.k
  • 369
  • 1
  • 6
  • 17
  • 3
    Possible duplicate of [Delete all rows with timestamp older than x days](http://stackoverflow.com/questions/21206361/delete-all-rows-with-timestamp-older-than-x-days) – Tewdyn Jan 18 '16 at 13:47
  • sidenote: if `Key` is your actual column name, that is an MySQL `(R)` reserved word, so you'll need to give it special treatment. https://dev.mysql.com/doc/refman/5.5/en/keywords.html – Funk Forty Niner Jan 18 '16 at 13:48
  • 1
    *"Forgot to mention, I would like the deletion of old rows to be automatic and run at least once a week."* - Use a cron job. – Funk Forty Niner Jan 18 '16 at 13:50
  • Triggers are to perform an offsetting operation triggered by a data modification event. Keeping your table clean of old entries do not really qualify for that, plus triggers cannot modify the data in other records of the table. I would create a maintenance script (a simple delete, if you wish to remove the records completely) and schedule its execution using OS level scheduler or mysql's own scheduler. – Shadow Jan 18 '16 at 13:51
  • @Fred-ii- My mistake, I named it theKey to avoid confusion – e.klara.k Jan 18 '16 at 13:51
  • @Fred-ii- Hm, that might be an idea... I have like no idea about what a cron job is but I will look in to it! – e.klara.k Jan 18 '16 at 13:55
  • @Shadow How do I acess mysql's own scheduler? – e.klara.k Jan 18 '16 at 13:55
  • Abhik's answer below details it. – Shadow Jan 18 '16 at 14:06

1 Answers1

7

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.

Abhik Chakraborty
  • 44,654
  • 6
  • 52
  • 63