Mysql – Delete old records based on its identifier

automationdeleteMySQL

For example, I have a table similar to this:

deviceid | servertime
1          2019-05-13 12:00:00
1          2019-05-14 12:00:00
2          2019-05-13 13:00:00
2          2019-05-12 13:00:00
2          2019-05-11 13:00:00
2          2019-05-10 13:00:00
3          2019-05-13 10:00:00
4          2019-04-16 15:00:00
...

I need a script that would run every day and delete all records belonging to deviceid "2" that is older than a year (from current date). In addition, it needs to delete records with other deviceids that are older than a month.

I would prefer to avoid cron jobs, because I'm concerned about security (I don't think storing MySQL password in a cron file is safe)

I'm a complete noob at SQL, I myself wouldn't know where to begin. I have phpMyAdmin if it's of relevance.

Best Answer

To delete records which have deviceid = 2 and are older than a year from the current date use

DELETE 
FROM table 
WHERE deviceid = 2 
  AND servertime < CURRENT_DATE - INTERVAL 1 YEAR; -- or, maybe, <=.

To delete records which are older than a year from the current date except those which have deviceid = 2 use

DELETE 
FROM table 
WHERE deviceid != 2 -- or deviceid <> 2
  AND servertime < CURRENT_DATE - INTERVAL 1 YEAR;

To perform this action daily use MySQL's Event Scheduler. Create a schedule which is executed every day

CREATE EVENT delete_old_records
ON SCHEDULE EVERY 1 DAY          -- execute daily
            STARTS CURRENT_DATE  -- at 00:00
DO
    DELETE 
    FROM table 
    WHERE deviceid != 2
      AND servertime < CURRENT_DATE - INTERVAL 1 YEAR;

And do not forget to enable event scheduler!!! Once (until disable or server restart) - by the query

SET GLOBAL event_scheduler = ON;

Permanently - by adding --event-scheduler=ENABLED to the service command-line or event-scheduler=ENABLED to the server configuration file (preferred).

Related Question