mysql - 创建事件，但表显示空值

Question

我编写了一个应该向表中添加值的 sql 事件，但它在不应该的时候返回了空值。它应该将时间添加到完成表中

USE project;

CREATE TABLE IF NOT EXISTS Finish (
    id INT PRIMARY KEY AUTO_INCREMENT,
    message VARCHAR(255) NOT NULL,
    created_at DATETIME NOT NULL
);

SET GLOBAL event_scheduler = ON;
SET GLOBAL event_scheduler = OFF;

SHOW PROCESSLIST;



CREATE EVENT IF NOT EXISTS event_1
ON SCHEDULE AT CURRENT_TIMESTAMP
ON COMPLETION PRESERVE
DO
  INSERT INTO Finish(message,created_at)
  VALUES('Delivery Successed',NOW());

SHOW PROCESSLIST;

CREATE EVENT IF NOT EXISTS event_2
ON SCHEDULE EVERY 5 second
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
-- ON COMPLETION PRESERVE
DO
  INSERT INTO Finish(message,created_at)
  VALUES('Delivery Filed',NOW());
 -- DROP;
 DROP EVENT event_2;

Answer 1

请试一试:(我删除了不必要的代码；)

SET GLOBAL event_scheduler = ON;

DROP TABLE IF EXISTS Finish;

CREATE TABLE IF NOT EXISTS Finish (
    id INT PRIMARY KEY AUTO_INCREMENT,
    message VARCHAR(255) NOT NULL,
    created_at DATETIME NOT NULL
);



DROP EVENT IF  EXISTS event_1;

CREATE EVENT IF NOT EXISTS event_1
ON SCHEDULE AT CURRENT_TIMESTAMP
ON COMPLETION PRESERVE
DO
  INSERT INTO Finish(message,created_at)
  VALUES('Delivery Successed',NOW());


DROP EVENT IF EXISTS event_2;

CREATE EVENT IF NOT EXISTS event_2
ON SCHEDULE EVERY 5 second
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
-- ON COMPLETION PRESERVE
DO
  INSERT INTO Finish(message,created_at)
  VALUES('Delivery Filed',NOW());

最后检查 Finish 表是否正确更新。

SELECT * FROM Finish;

注意:您在将 event_scheduler OFF 设置为 ON 之后立即将其设置为 OFF。所以你新创建的事件没有机会运行。

更多:event_1 是一次性事件。 event_2 从开始时间起每 5 秒执行一次，最多一分钟。