I am using a MySQL database on a LAMP stack. I have a table in which I got some simple entries. These entries are supposed to automatically be deleted after ten minutes not being used. "being used" is defined as being inserted, updated or simply requested through a SELECT (obviously delete is unimportant). I would like to use pure MySQL for this, but could not find a solution yet. The fields can have a table.time_used property if required, but do not have it yet. It would be no problem to create one though. I was thinking of some TRIGGER and PROCEDURE combination, but could not figure one out yet.
I know I could use some third-party application like cron, but I would prefer a MySQL solution because the project may be copied to other environments where cron is not an option.
I also know it is possible to do this by changing the SELECT statements using the table and a table.expiration_time field which would likely be more exact too, but I do not need it to work on the second, I just need roughly ten minutes and would like to keep it clean, as this may run on very limited machines.
Thanks in advance! :)
I would recommend all access through stored procedures that would then be in control of updating the lastUsed datetime.
Adhoc access outside of stored procedures would not enforce the lastUsed aspect being updated. In other words that would be frowned upon. It naturally could be used but as it would not have tight control, it would fall on the outside of expectations.
Any other mechanism to perform this would rely on a fullstack service that is outside of MySQL.
Automatic deletion would occur through the built-in Events functionality in MySQL. I have 3 Events related links off my profile page. One to start looking at is Here.