mysql - SQL 在自引用表中查找祖先/后代

Question

我有一个引用自身的表，如下所示:

CREATE TABLE Foo (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
parent INT NULL,
name VARCHAR (30) NOT NULL,
FOREIGN KEY (parent) REFERENCES Foo(id) ON DELETE CASCADE);

示例数据:

id   parent name
1    NULL   a
2    NULL   b
3    1      a1
4    1      a2
5    3      a1x
6    3      a2x

我想编写查询来列出给定行的祖先和后代，例如

CALL find_ancestors('a1x')

会回来

id name
3  a1
1  a

和

CALL find_descendants('a')

会回来

id name
3  a1
5  a1x

如何为 MySQL 5 编写这些存储过程？谢谢

---

悬赏奖励问题:还选择返回行与源的距离并将最大距离参数传递给过程，例如

CALL find_ancestors('a1x')

会回来

id name distance
3  a1   1
1  a    2

和

CALL find_ancestors_bounded('a1x',1)

会回来

id name distance
3  a1   1

Answer 1

假设我们有一个包含四个元素的表，id、item、class 和 parent_id。我们想要任何给定项目的完整祖先，我们需要做的是一个自定义的 mysql 函数，它实际上将循环遍历每条记录以寻找我们的项目 parent_id 的匹配项，一旦找到匹配项，如果匹配项有parent_id，它将再次开始循环，依此类推。每次我们的函数找到匹配项时，它会将其存储在以逗号分隔的字符串中，该字符串将在最后返回(例如:1,2,3,4)

我们的函数看起来像这样:

DELIMITER $$
DROP FUNCTION IF EXISTS `junk`.`GetAncestry` $$
CREATE FUNCTION `junk`.`GetAncestry` (GivenID INT) RETURNS VARCHAR(1024)
DETERMINISTIC
BEGIN
    DECLARE rv VARCHAR(1024);
    DECLARE cm CHAR(1);
    DECLARE ch INT;

    SET rv = '';
    SET cm = '';
    SET ch = GivenID;
    WHILE ch > 0 DO
        SELECT IFNULL(parent_id,-1) INTO ch FROM
        (SELECT parent_id FROM pctable WHERE id = ch) A;
        IF ch > 0 THEN
            SET rv = CONCAT(rv,cm,ch);
            SET cm = ',';
        END IF;
    END WHILE;
    RETURN rv;
END $$
DELIMITER ;

此代码由 RolandoMySQLDBA 编写