MYSQL - 创建事件以删除过期记录

Question

我有这个 MYSQL 事件的代码:

CREATE EVENT DeleteToken
ON SCHEDULE EVERY 1 MINUTE
DO
BEGIN
DELETE FROM 
`password_recovery` 
WHERE `date_expires` < 
CURRENT_TIMESTAMP();
END

当我运行它时，出现以下错误:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 8

date_exires 是 datetime 格式为 YYYY-MM-DD HH:MM:SSCURRENT_TIMESTAMP() 有什么问题。

我也试过 CURRENT_TIMESTAMP 但它给了我同样的错误。

Answer 1

我建议您使用此查询 -

CREATE EVENT DeleteToken
ON SCHEDULE EVERY 1 MINUTE
DO
DELETE FROM `password_recovery` WHERE `date_expires` < CURRENT_TIMESTAMP();

否则，当你使用 BEGIN..END 子句时，你应该使用 DELIMITER命令-

DELIMITER $$
CREATE EVENT DeleteToken
ON SCHEDULE EVERY 1 MINUTE
DO
BEGIN
  DELETE
    FROM `password_recovery`
  WHERE `date_expires` < CURRENT_TIMESTAMP();
END$$
DELIMITER ;