使用MySQL触发器自动递增值

Question

我一直在尝试在一个表中设置一个基于向我的表中插入数据的自动递增，

在这被标记为重复之前，我已经看过其他问题了，我认为我的主要问题是对触发器的语法的误解，特别是这一行：

WHERE ID = NEW.ID;

MySQL块：

CREATE TABLE Members(
    ID  INT  UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
    FIRST_NAME  TEXT(16),
    LAST_NAME  TEXT(16),
    TITLE  TEXT(7), /** 7 CHARS for  'Student'*/
    INSTITUTION  VARCHAR(2048),
    No_Publications INT NOT NULL,
    PRIMARY KEY(ID)
  );
CREATE TABLE Papers(
    ISBN  INT UNSIGNED NOT NULL UNIQUE,
    Title  TEXT(4),
    Publish_Date  DATE NOT NULL,
    Topic  TEXT(128),
    PRIMARY KEY(ISBN)
  );
  CREATE TABLE Publications(
    Researcher_ID  INT  UNSIGNED NOT NULL UNIQUE,
    ISBN  INT UNSIGNED NOT NULL UNIQUE,
    PRIMARY KEY (Researcher_ID, ISBN),
    FOREIGN KEY(Researcher_ID) REFERENCES Members(ID) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY(ISBN) REFERENCES Papers(ISBN) ON DELETE RESTRICT ON UPDATE CASCADE /**Used in
    many to many relations*/
  );

  CREATE TRIGGER New_Publication AFTER INSERT ON Publications
  FOR EACH ROW
  BEGIN
  UPDATE Members SET No_Publications = No_Publications + 1 WHERE ID = NEW.ID;
  END;

编辑:在插入到Publications表中时，我希望Members表中的No_Publication列递增1

Answer 1

publications表中没有id字段，因此不能有任何NEW.ID。您一定在mysql中收到了未知字段错误。publications的create table语句包含有关在更新中使用哪个字段的线索：

FOREIGN KEY(Researcher_ID) REFERENCES Members(ID)

因此，更新应如下所示：

UPDATE Members SET No_Publications = No_Publications + 1 WHERE ID = NEW.Researcher_ID

此外，不需要使用begin ... end，因为触发器主体由一条sql语句组成：

CREATE TRIGGER New_Publication AFTER INSERT ON Publications
  FOR EACH ROW
  UPDATE Members SET No_Publications = No_Publications + 1 WHERE ID = NEW.Researcher_ID;