MySQL record that would subject to TTL

Question

Is it possible, to create a record in MySQL database, that would subject to TTL (Time to live) option.

I want to make a simple password recovery feature and I need to store an activation key, that would be stored in database for only 3600 seconds and then be deleted automatically after that time? I know there are bunch of other ways to achieve this but they are not as straight forward as an idea of TTL functionality.

I guess that MySQL doesn't have such functionality but I just thought that maybe I'm missing something and there is?

Answer 1

I just found out that MySQL 5.1+ has event scheduler. The MySQL Event Scheduler manages the scheduling and execution of events - tasks that run according to schedule.

Stored routines require the event table in the MySQL database. This table is created during the MySQL installation procedure.

Syntax for using it would be:

CREATE EVENT
  ClearUserActivationCodes
ON SCHEDULE EVERY 1 DAY
DO
BEGIN
DELETE FROM
  user_activation_code
WHERE code_time_stamp < NOW()
END

It's quite useful and fully satisfies my needs for automatically clearing tables without using cron jobs.

Answer 2

Personally I would store the key with a TIMESTAMP field, using the DEFAULT CURRENT_TIMESTAMP option.

Then, when fetching the key, check the timestamp. If it's less than an hour ago, then you're okay to go ahead. Otherwise, treat it as invalid and delete the key (you can also provide a specific error message saying that the key expired and the user needs a new one).

Additionally, use a cron task running once a day to delete keys that are older than a day. This ensures you don't get a growing pile of expired codes if people never actually enter them.