MySQL:自引用不为空，auto_increment，外键？

Question

是否可以在 MySQL(任何版本)中声明和使用一个表，该表具有引用同一表的 AUTO_INCREMENT 主键的自引用非空外键列？

基本上，我想创建下表，其中的行代表一片树林。我希望层次结构中的根节点由 parent_id 指示该行等于 id (而不是允许 parent_id 中的 NULL 指示根)。

例如

CREATE TABLE forest (
    id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    parent_id BIGINT NOT NULL,
        CONSTRAINT fk_forest_parent_id FOREIGN KEY(parent_id) REFERENCES forest(id),
    name VARCHAR(20) NOT NULL UNIQUE
);

创建此表有效 (5.0.44sp1-enterprise-gpl-nt-log MySQL Enterprise Server (GPL))。但是，似乎无法使用 LAST_INSERT_ID() 插入 parent_id根节点(这并不奇怪)。例如，以下内容不起作用:

INSERT INTO forest(parent_id, name) VALUES (LAST_INSERT_ID(), "root 1");

以下确实有效，但不再使用自动增量:

INSERT INTO forest(id, parent_id, name) VALUES (1234, 1234, "root 1");

有没有一种方法可以使用以这种方式定义的表，同时仍然依赖于自动生成的 PK，同时还保留所有约束(NOT NULL 和 FOREIGN KEY)？

更新 - 可能的解决方案:

START TRANSACTION;
SET FOREIGN_KEY_CHECKS = 0;
INSERT INTO forest(parent_id, name) VALUES (0, "root X");
UPDATE forest SET parent_id = LAST_INSERT_ID() WHERE id = LAST_INSERT_ID();
SET FOREIGN_KEY_CHECKS = 1; /* This seems to be important. */
COMMIT;

Answer 1

在 INSERT 完成之前不可能知道行的 id 是什么，所以调用 LAST_INSERT_ID() 时没有INSERT 已完成不执行任何操作。相反，您需要两个单独的语句(它们可以作为存储过程中的事务运行或您有什么)。

INSERT INTO forest(name) VALUES ("root 1");
UPDATE forest SET parent_id = LAST_INSERT_ID() WHERE id = LAST_INSERT_ID();

不过，我认为根节点将自己作为父节点引用是没有意义的。只是说。