使计时器在过期记录上运行代码

I am developing a web application (PHP/MySQL), in which I need to implement timers (record id expires at expiration_date), where expiration involves the record's state being updated and arbitrary code being executed as required.

I basically need a BPMN Timer event.

Options I have considered, none of which I am thrilled with:

  • Cronjob calling a function in the application that just queries for and updates expired records.
  • At a commonly called point in the code, call this function at a fixed interval.

How is this commonly done in PHP applications?

I would also choose the cronjob option.

I would also, however, have logic in my app that knows how to deal (or ignore) expired records. Services like cron can break, so it's nice to NOT have to rely upon it.

Unfortunately, DBMSs don't give us quite this sort of flexibility, and you won't want to go off and do database maintenance in the event you come across one of these "expired" records.

You're best excluding them through queries, the use of views, or simple application logic.

I would choose the cronjob option.

The following is an option which you have not considered:

You could use MySQL Event Scheduler to run a stored expiration routine within the database periodically.

Some good things about this approach:

  • Platform independent, works the same way on UNIX and Windows.
  • Easy to set-up. No need to explain to the user how to setup cron jobs. You just create the event together with your database schema.

Downsides:

  • Not enabled by default in MySQL. Need to put event_scheduler=on in my.cnf or otherwise enable it.
  • Requires MySQL 5.1 or newer if I remember correctly.

Example:

DROP EVENT IF EXISTS expire_event;
DELIMITER //
CREATE EVENT expire_event
ON SCHEDULE EVERY 1 MINUTE
DO BEGIN
    DELETE FROM data WHERE time < UNIX_TIMESTAMP(NOW() - INTERVAL 1 HOUR);
END //
DELIMITER ;

The above will every minute delete any rows from data table which have a UNIX time stamp older than 1 hour.