MySQL give rows a lifetime

Question

How can I create a lifetime of a row so after a specific time say 2 weeks the row will automatically erase? Any info would be great.

Answer 1

RDBMS don't generally allow rows to automatically self destruct. It's bad for business.

More seriously, some ideas, depending on your exact needs

• run a scheduled job to run a DELETE to remove rows based on some date/time column
• (more complex idea) use a partitioned table with a sliding window to move older rows to another partition
• use a view to only show rows less than 2 weeks old

Answer 2

Add a timestamp column to the table that defaults to CURRENT_TIMESTAMP, and install a cron job on the server that frequently runs and prunes old records.

DELETE FROM MyTable WHERE datediff(now(), myTimestamp) >= 14;

Answer 3

Or you can add timestamp column and always select like this:

SELECT * FROM myTable WHERE timetampColumn>=date_sub(now(), interval 2 week);

It is better if you don't need to erase the data and you want to show only data from last 2 weeks.