php - 为什么计划的事件没有从 mysql 数据库中删除数据

Question

我正在使用 php 和 mysql。使用 codeigniter 中的 date(Y-m-d H:i:s) 将数据插入日期时间字段。

CREATE EVENT AutoDeleteRows
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 30 SECOND 
DO 
DELETE LOW_PRIORITY FROM a_database.event_temp_users 
WHERE registration_date< DATE_SUB(NOW(), INTERVAL 1 MINUTE)

1 分钟后，我刷新表格，但数据仍然存在。该事件每 30 秒运行一次，它会删除 1 分钟或更长时间的所有行，不是吗？

使用 phpmyadmin 和 InnoDB 引擎。

编辑即使我这样做

CREATE EVENT AutoDeleteRows
ON SCHEDULE EVERY 30 SECOND 
DO 
DELETE LOW_PRIORITY FROM a_database.event_temp_users 
WHERE registration_date< DATE_SUB(NOW(), INTERVAL 1 MINUTE)

还是不行

Answer 1

如果您的事件应该每 30 秒运行一次，您必须使用:

CREATE EVENT AutoDeleteRows
ON SCHEDULE EVERY 30 SECOND 
DO 
DELETE LOW_PRIORITY FROM a_database.event_temp_users 
WHERE registration_date< DATE_SUB(NOW(), INTERVAL 1 MINUTE)

相反。

To repeat actions at a regular interval, use an EVERY clause.

使用 AT，您的事件只会在这个特定时间点执行一次:

AT timestamp is used for a one-time event. It specifies that the event executes one time only at the date and time given by timestamp, which must include both the date and time, or must be an expression that resolves to a datetime value.

均来自章节 CREATE EVENT Syntax

您必须检查事件调度程序是否正在运行。这可以通过

来完成

SELECT @@event_scheduler;

如果结果应为关闭并且您有权启动事件调度程序，那么您可以使用以下命令激活事件调度程序

SET GLOBAL event_scheduler = 'ON';

参见Event Scheduler Configuration

The global event_scheduler system variable determines whether theEvent Scheduler is enabled and running on the server. It has one ofthese 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.