CODEWITHSUNDEEP
mysqlsql
The GamerX
The GamerX

Reputation: 13

MYSQL: Create row in table with expiration date

Hello is possible INSERT row to table with expiration date and when the expiration date is reached so row are automatically deleted? Or i only must create one column with expiration date and when sorting checking this value for ignore?

Upvotes: 1

Views: 1858

Answers (2)

vishal
vishal

Reputation: 352

Your need looks more like an Event IF you want not want to add expiration date column than

CREATE EVENT delete_event ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
ON COMPLETION PRESERVE
DO BEGIN
      DELETE FROM Your_table  WHERE date>= logic for expiration_date   ;
END;

otherwise 

DELIMITER $$
CREATE EVENT Delete ON SCHEDULE EVERY 1 DAY
DO
BEGIN
  DELETE   FROM TABLE  WHERE `date_expires` < CURRENT_TIMESTAMP();
END$$
DELIMITER

;

NOTE that MySQL Event Scheduler need to be enabled on your server:

SET GLOBAL event_scheduler = ON;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You cannot do this directly in the database. You can do this by scheduling an event or job that does the delete. You can schedule an event for each deletion.

I think a better approach, though, is to use a view:

create view v_t as
    select t.*
    from t
    where expiration_date > now();

This ensures that the row is not available at the instant the expiration date is no longer in the future. You can then remove the rows at your leisure. Deleting rows can put a load on the database, so you might want to wait until the load is lighter.

Upvotes: 2

Related Questions