php - PHP 的 SQL 触发事件

Question

我正在使用 PHP + MySQL 进行开发。

我需要在到期日期时删除表中的一行(元组)。

可能最好的方法是使用触发器，但我不知道该怎么做。

谁有例子？

Answer 1

您可以使用 MySQL event scheduler . CREATE EVENT 手册页的最小示例将是您可能需要的良好基础:

 CREATE EVENT myevent
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
    DO
      UPDATE myschema.mytable SET mycol = mycol + 1;

如果你想每 10 分钟清理一次，你会使用

CREATE EVENT myevent
    ON SCHEDULE EVERY 10 MINUTE

您的清算声明将是删除:

    DO
      DELETE FROM yourTable WHERE expiration_date < NOW();

就是这样，MySQL 将每 10 分钟自动执行一次。四行SQL完成:

CREATE EVENT clear_expirations             -- a better name for the event
    ON SCHEDULE EVERY 10 MINUTE         -- as you need it
    DO
      DELETE FROM yourTable WHERE expiration_date < NOW();

编辑

默认情况下，事件调度程序将停止:

The global event_scheduler system variable determines whether the Event Scheduler is enabled and running on the server. It has one of these 3 values, which affect event scheduling as described here:

OFF: The Event Scheduler is stopped. The event scheduler thread does not run, is not shown in the output of SHOW PROCESSLIST, and no scheduled events are executed. OFF is the default value for event_scheduler.

When the Event Scheduler is stopped (event_scheduler is OFF), it can be started by setting the value of event_scheduler to ON. (See next item.)

ON: The Event Scheduler is started; the event scheduler thread runs and executes all scheduled events.

...

DISABLED: This value renders the Event Scheduler nonoperational. When the Event Scheduler is DISABLED, the event scheduler thread does not run (and so does not appear in the output of SHOW PROCESSLIST). In addition, the Event Scheduler state cannot be changed at runtime.

您可以通过以下方式获取此系统变量的值

SELECT @@event_scheduler;

如果结果为 OFF，您可以使用以下命令启动事件调度程序

SET GLOBAL event_scheduler = 'ON';

您应该修改 MySQL 服务器的配置，以便事件调度程序将在 mysqld 进程启动时运行。

应该清楚，您必须是 MySQL 服务器的管理员才能修改此配置。