How to delete a row of mysql database after a few hours?

Question

I created a script that register for news letter. but before accepting this processes the registered mail must be validate. so ... after the registration , the scrip insert data into row for the news_letter thable with this value: reg_mail,val_code,reg_time and ...

Until now , this process is done after registration. The script creates a random code and get NOW() time. It then saves in database.

After that if someone else go to reg page, the script sends a query to database and deletes rows that passed for example 5 hours.

I want to change this process and tell mysql to automatically delete rows that pass specific time without send any query to database.

Is it possible? If so, how can I do that?

And store a timestamp for the time that the code was sent so you have something to check against. A simple procedure called by a scheduled event would prune them. — Simon at The Access Group, Jun 12 '13 at 08:13
i dont have any information about that. can u show me a example ...? (I'm a beginner) :) — Mohsen Rasouli, Jun 12 '13 at 08:19
To be precise, you'd have to create a scheduled event per entry. Is an approximate precision (like between 5 hours and 5hours 10 min) enough ? — C4stor, Jun 12 '13 at 08:20
possible duplicate of [How to delete mysql row after time passes?](http://stackoverflow.com/questions/9865393/how-to-delete-mysql-row-after-time-passes) — warfish, Dec 27 '13 at 23:02

score 2 · Answer 1 · answered Jun 12 '13 at 08:18

It's not possible. The best you can do with pure mysql is periodically trigger a stored procedure to clean the database.

If you want this kind of feature, you'll have to use other kind of storage (memcached or cassandra for example manage ttl pretty well) or layer of application to keep in memory id to expires (a simple process reading a file in which you write a list of events to expire with a timestamp could be enough).

How to delete a row of mysql database after a few hours?

1 Answers1