mysql - 每次创建新行时更新值

Question

我有两个表:config(last_inserted_id) 和 element(id) 是否有机会随时获取最后插入的 id当在 element 表中创建新行并在 config 表的 last_inserted_id 列中执行更新时？

我写了这个:

CREATE TRIGGER UPDATE_CONFIG_VALUES AFTER INSERT ON element
    BEGIN
        UPDATE config SET last_inserted_id = last_insert_id();
    END;
END;

是吗？如果我删除 element 表中的一行会发生什么？该值是否应该在 config 表中更新“我如何避免这种情况？

配置表

CREATE TABLE IF NOT EXISTS `cmplatform`.`isrl_config` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `logo_address` VARCHAR(250) NOT NULL,
  `name` VARCHAR(250) NOT NULL,
  `rif` VARCHAR(20) NOT NULL,
  `address` TEXT NOT NULL,
  `phone` VARCHAR(14) NOT NULL,
  `last_retention_number` INT NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB

Answer 1

测试这个(未测试)

CREATE TRIGGER element_inserted_tg AFTER INSERT ON element
FOR EACH ROW
BEGIN
    DECLARE count INT;
    SELECT COUNT(1) INTO count FROM config;
    IF count == 1
        UPDATE config SET last_inserted_id = NEW.id;
    ELSE
        INSERT INTO config VALUES (NEW.id);
    END IF;
END;

CREATE TRIGGER element_deleted_tg AFTER DELETE ON element
FOR EACH ROW
BEGIN
    UPDATE config SET last_inserted_id = (SELECT MAX(id) FROM element);
END;