mysql - 更新日期作为触发器的一部分？

Question

我试图在我的 PHP Internet 应用程序中保留当前内容和存档内容的列表。我希望能够将已存档的内容识别为具有结束日期，而未存档的内容则没有结束日期。如果我可以创建更多路径，那将是理想的，但我希望从这里开始。

我注意到的第一件事是我在第 1 行的 "处遇到语法错误，但在我创建表的地方没有双引号。我遇到的第二个问题是使用 TIMESTAMP 作为数据类型。我尝试使用 CURRENT_TIMESTAMP 作为开始日期，但它返回了语法错误。我遇到的最后一个问题是触发器构造。我无法深入了解以解决它。一旦我通过引用，我也会尝试解决它。​​

CREATE TABLE plan(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(32),
  startdate TIMESTAMP NOT NULL,
  enddate TIMESTAMP);
CREATE TABLE level(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(32),
  description VARCHAR(500),
  startdate TIMESTAMP NOT NULL,
  enddate TIMESTAMP);
CREATE TABLE planIDxlevelID(
  planID INT NOT NULL REFERENCES plan(id),
  levelID INT NOT NULL REFERENCES level(id),
  arXORcur ENUM('archive', 'current');
);

delimiter |

CREATE TRIGGER testref BEFORE INSERT ON plan
  FOR EACH ROW BEGIN
    INSERT INTO plan(id, plan, startdate, enddate)
      SET id = LAST_INSERT_ID( id + 1), name = NEW.name, startdate = NEW.UTC_TIMESTAMP, enddate = NULL;
    UPDATE plan(enddate) WHERE plan.id = OLD.id
      SET enddate = UTC_TIMESTAMP;
    INSERT INTO planIDxlevelID(planID, levelID, arXORcur)
      SET planID = NEW.planID, levelID = OLD.levelID, arXORcur = current;
    UPDATE planIDxlevelID(planID, levelID, arXORcur)  WHERE planID = OLD.planID
      SET planID = OLD.planID, levelID = OLD.levelID, arXORcur = archive;
  END;
|

delimiter ;

INSERT INTO plan (name) VALUES
  "Frogs", "Toys", "Trucks", "Nature", "Seasons",
  "Construction", "Candy", "Rainbows", "Telephone", "Breakfasts";

Answer 1

TIMESTAMP 与 DATETIME

这是一个经典的陷阱:TIMESTAMP 是一种特殊数据类型，它每次触摸该行时都会更新为“现在”，无论您是将其更新为另一个值还是保留它。你想要的是 DATETIME。

接下来，您有许多语法错误和其他错误。以下执行没有错误，但它现在可能没有您想要的逻辑，但您可以对其进行编辑以修复该问题:

CREATE TABLE plan (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(32),
  startdate DATETIME NOT NULL, -- changed TIMESTAMP to DATETIME
  enddate DATETIME -- changed TIMESTAMP to DATETIME
);

CREATE TABLE level (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(32),
  description VARCHAR(500),
  startdate DATETIME NOT NULL, -- changed TIMESTAMP to DATETIME
  enddate DATETIME -- changed TIMESTAMP to DATETIME
);

CREATE TABLE planIDxlevelID (
  planID INT NOT NULL REFERENCES plan(id),
  levelID INT NOT NULL REFERENCES level(id),
  arXORcur ENUM('archive', 'current')
);

DROP TRIGGER IF EXISTS test_insert;
delimiter |
CREATE TRIGGER test_insert BEFORE INSERT ON plan
  FOR EACH ROW BEGIN
    INSERT INTO plan (id, plan, startdate)
    values (LAST_INSERT_ID() + 1, NEW.name, NEW.UTC_TIMESTAMP());

    INSERT INTO planIDxlevelID (planID, levelID, arXORcur)
    values (NEW.ID, null, arXORcur = current);    
  END;|
delimiter ;

DROP TRIGGER IF EXISTS test_update;
delimiter |
CREATE TRIGGER test_update BEFORE UPDATE ON plan
  FOR EACH ROW BEGIN
    UPDATE plan SET
    enddate = UTC_TIMESTAMP
    WHERE plan.id = OLD.id;

    UPDATE planIDxlevelID SET
    planID = NEW.ID,
    levelID = null,
    arXORcur = archive
    WHERE planID = OLD.ID;
  END;|
delimiter ;

INSERT INTO plan (name) VALUES
  ("Frogs"), ("Toys"), ("Trucks"), ("Nature"), ("Seasons"),
  ("Construction"), ("Candy"), ("Rainbows"), ("Telephone"), ("Breakfasts");