使用 mysql/php 中的 id /parent_id 模型获取记录的所有父项的最简单方法是什么？

Question

我正在寻找使用邻接列表/单表继承模型（id，parent_id）从数据库递归获取所有父元素的最简单方法。

我的选择当前如下所示：

$sql = "SELECT
             e.id,
             TIME_FORMAT(e.start_time, '%H:%i') AS start_time,
             $title AS title,
             $description AS description,
             $type AS type,
             $place_name AS place_name,
             p.parent_id AS place_parent_id,
             p.city AS place_city,
             p.country AS place_country
         FROM event AS e
         LEFT JOIN place AS p ON p.id = e.place_id                          
         LEFT JOIN event_type AS et ON et.id = e.event_type_id
         WHERE e.day_id = '$day_id'
         AND e.private_flag = 0
         ORDER BY start_time";

每个事件都链接到一个地点，并且每个地点可以是另一个地点<的子级/code> （最多约 5 层深）

这可以在 mysql 的单次选择中实现吗？

目前我认为它可能是一个单独的函数，它循环返回的 $events 数组，添加 place_parent_X 元素，但我不确定如何实现这个。

Answer 1

可以在 MySQL 中执行此操作，但您需要创建一个函数并在查询中使用 in 。

有关详细说明，请参阅我的博客中的此条目：

• 分层查询在 MySQL 中

，以下是函数和查询：

CREATE FUNCTION hierarchy_connect_by_parent_eq_prior_id(value INT) RETURNS INT
NOT DETERMINISTIC
READS SQL DATA
BEGIN
        DECLARE _id INT;
        DECLARE _parent INT;
        DECLARE _next INT;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;

        SET _parent = @id;
        SET _id = -1;

        IF @id IS NULL THEN
                RETURN NULL;
        END IF;

        LOOP
                SELECT  MIN(id)
                INTO    @id
                FROM    place
                WHERE   parent = _parent
                        AND id > _id;
                IF @id IS NOT NULL OR _parent = @start_with THEN
                        SET @level = @level + 1;
                        RETURN @id;
                END IF;
                SET @level := @level - 1;
                SELECT  id, parent
                INTO    _id, _parent
                FROM    place
                WHERE   id = _parent;
        END LOOP;
END

SELECT  id, parent
FROM    (
        SELECT  hierarchy_connect_by_parent_eq_prior_id(id) AS id, @level AS level
        FROM    (
                SELECT  @start_with := 0,
                        @id := @start_with,
                        @level := 0
                ) vars, t_hierarchy
        WHERE   @id IS NOT NULL
        ) ho
JOIN    place hi
ON      hi.id = ho.id

后一个查询将选择给定节点的所有后代（您应该在 @start_with 变量）

要查找给定节点的所有祖先，您可以使用不带函数的简单查询：

SELECT  @r AS _id,
        @r := (
        SELECT  parent
        FROM    place
        WHERE   id = _id
        ) AS parent
FROM    (
        SELECT  @r := @node_id
        ) vars,
        place

我的博客中的这篇文章更详细地描述了此查询：

• 排序列表

为了使这两种解决方案在合理的时间内发挥作用，您需要在两个 id< 上都有索引/code> 和 父级。

确保您的 id 被定义为 PRIMARY KEY 并且您在 parent 上有辅助索引。

Answer 2

标准的父子数据库设计不可能做到这一点。

但是，您可以使用 嵌套集 方法并在一个查询，尽管这需要相当多的工作才能达到这一点。