MySQL中如何避免Node表的ParentID指向自身NodeID？

你碰到的这个情况是MySQL对自增列的CHECK约束有特殊限制，同时外键设置失败大概率是细节没处理好，下面给几个实用的解决办法：

方法一：用触发器兜底（最靠谱） #

因为自增列没法直接用CHECK约束，触发器是绕开这个限制的最优方案。你可以建两个触发器，分别在插入和更新数据前检查ParentID和NodeID是否相等，一旦相等就抛出错误阻止操作。

代码示例：

-- 插入前检查的触发器
DELIMITER //
CREATE TRIGGER node_no_self_ref_insert
BEFORE INSERT ON Node
FOR EACH ROW
BEGIN
    -- 只在手动指定NodeID的场景下检查，自动生成的自增ID不会出现这种情况
    IF NEW.NodeID IS NOT NULL AND NEW.ParentID = NEW.NodeID THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'ParentID不能和当前节点的NodeID相同';
    END IF;
END //
DELIMITER ;

-- 更新前检查的触发器
DELIMITER //
CREATE TRIGGER node_no_self_ref_update
BEFORE UPDATE ON Node
FOR EACH ROW
BEGIN
    IF NEW.ParentID = NEW.NodeID THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'ParentID不能和当前节点的NodeID相同';
    END IF;
END //
DELIMITER ;

说明：SQLSTATE '45000'是MySQL的自定义错误状态码，提示文本可以根据需求修改。另外，自动生成的自增ID在插入时NEW.NodeID是NULL，所以不会触发检查——毕竟你不可能提前知道下一个自增ID是什么，也就没法把ParentID设成这个值。

方法二：搞定外键的问题 #

你说没法设置外键，大概率是这几个原因：

• ParentID和NodeID的数据类型不匹配（比如NodeID是INT UNSIGNED，ParentID是普通INT）
• ParentID没有建索引（外键列必须要有索引）
• 表的存储引擎不是InnoDB（只有InnoDB支持外键）

排查完这些问题后，试试重新创建外键：

ALTER TABLE Node ADD CONSTRAINT fk_node_parent FOREIGN KEY (ParentID) REFERENCES Node(NodeID);

注意：如果根节点的ParentID允许为空，这个外键是可以正常创建的，空值不会触发外键约束。

方法三：应用层加校验（双重保障） #

除了数据库层面的限制，你也可以在应用代码里加一层校验：在执行插入或更新操作前，先检查ParentID和NodeID是否相等（如果是插入且手动指定NodeID的话），一旦相等就直接拦截，不往数据库发请求。

不过这个只能作为补充，不能替代数据库的约束——毕竟难保有人直接操作数据库（比如运维手动跑SQL）。

内容的提问来源于stack exchange，提问作者Oprah W.