CODEWITHSUNDEEP
phpmysqlsqldatabase
Melvin Koopmans
Melvin Koopmans

Reputation: 3050

MySQL expiration

Has MySQL some kind of functionality to expire a certain row after x seconds?

For example, I have to following database:

users
    id - integer
    name - string

subscriptions
    id - integer
    name - string

user_subscriptions
    id - integer
    user_id - integer
    subscription_id - integer
    created_at - date
    updated_at - date

In this case we have a pivot table user_subscriptions and I want to automatically remove a row within user_subscriptions in x seconds

Is this possible within MySQL, if so how?

Upvotes: 0

Views: 82

Answers (3)

vogomatix
vogomatix

Reputation: 5041

Approximately:

CREATE EVENT event1
ON SCHEDULE EVERY '1' DAY
STARTS '2014-01-01 00:00:00'
DO DELETE FROM user_subscriptions us WHERE timediff(NOW(), us.timestamp) > INTERVAL 1 DAY;

Upvotes: 1

Stefan Winkler
Stefan Winkler

Reputation: 3956

See Ed Heal's answer for regular jobs.

If you fear that this uses too much resources when run too often, you could also temporarily simulate the desired behavior until the next run by adding a timestamp column (e.g. named subscription_date) to user_subscriptions and create a database view which hides the expired subscriptions:

-- shows only subscriptions of the last hour
create view user_subscriptions_view as
select id, user_id, subscription_id 
from user_subscriptions
where subscription_date > subtime(now(),'1:0:0')

Upvotes: 0

Ed Heal
Ed Heal

Reputation: 59997

You can create events that are run to a schedule to tidy up the database

This will run independent of PHP and CRON.

But be careful not to lock the table

Upvotes: 1

Related Questions