如何更新Mysql触发器中的LAST_INSERT_ID？

Question

在我的MySQL数据库中，我有一个带有复合主键的表，其中ID是而不是auto_increment模式。就像这样：

CREATE TABLE table_a (
    fk_table_b INT UNSIGNED NOT NULL,
    id INT UNSIGNED,
    label VARCHAR(80) NOT NULL,
    PRIMARY KEY (fk_table_b, id),
    FOREIGN KEY fk_table_b
        REFERENCES table_b(id)
);

为了在外键的函数中增加ID，我做了如下触发器：

DELIMITER $$
CREATE TRIGGER table_a_auto_increment
BEFORE INSERT ON table_a
FOR EACH ROW BEGIN
    SET NEW.id = (
        SELECT IFNULL(MAX(id), 0) + 1
        FROM table_a
        WHERE table_a.fk_table_b  = NEW.fk_table_b
    ); 
END $$
DELIMITER ;

但是当我做SELECT LAST_INSERT_ID()时，我得到0作为新的id .通常，您可以通过给LAST_INSERT_ID()这样的数字来覆盖它：

INSERT table_a ( fk_table_b, id)
VALUES (1, LAST_INSERT_ID(5));
SELECT LAST_INSERT_ID(); -- -> it gives me 5

因此，我尝试将两者结合起来执行此触发器：

DELIMITER $$
CREATE TRIGGER table_a_auto_increment
BEFORE INSERT ON table_a
FOR EACH ROW BEGIN
    SET NEW.id = (
        SELECT LAST_INSERT_ID(IFNULL(MAX(id), 0) + 1)
        FROM table_a
        WHERE table_a.fk_table_b  = NEW.fk_table_b
    ); 
END $$
DELIMITER ;

但当我在基地里插入一些东西的时候，它还是给了我0.你知道有什么办法能让它发挥作用吗？

非常感谢。

-编辑2020-08-14

最后，似乎不可能覆盖触发器内的LAST_INSERT_ID函数，因此我通过删除触发器并在插入函数中这样做来更改解决方案：

INSERT table_a ( fk_table_b, id, label)
VALUES (1, LAST_INSERT_ID((
    SELECT IFNULL(MAX(old_one.id), 0) + 1
    FROM table_a AS old_one
    WHERE old_one.fk_table_b  = table_a.fk_table_b
)), "something");

然后，这给了我一个好的结果，我可以用在我的后端:)

Answer 1

您可以使用其他服务表：

CREATE TABLE service_table (id BIGINT AUTO_INCREMENT PRIMARY KEY);

和

DELIMITER $$
CREATE TRIGGER table_a_auto_increment
BEFORE INSERT ON table_a
FOR EACH ROW 
BEGIN
    SET NEW.id = (
        SELECT IFNULL(MAX(id), 0) + 1
        FROM table_a
        WHERE table_a.fk_table_b  = NEW.fk_table_b
    ); 
    DELETE FROM service_table WHERE id IS NOT NULL;
    INSERT INTO service_table VALUES (NEW.id - 1);
    INSERT INTO service_table VALUES (NULL);
    SET NEW.id = LAST_INSERT_ID() - 1;
END $$
DELIMITER ;

小提琴 (移除外键)。

1. 也许代码可以简化一点-自己做吧。
2. 服务表可以定义为Engine = MEMORY (如果可用的话)。
3. 对于并发插入，代码不安全。