MySQL：选择行和所有相关行

Question

我有一个categories表格设置如下：

ID    CatName      CatParent
1     Websites     NULL
2     Recipes      NULL
3     Programming  1
4     Helpful      3
5     Useless      3
6     Desserts     2

如果我有一个类别ID，我想查询数据库以选择类别和所有父类别，按祖先顺序排列。每个类别都有一个CatParent属性，它是其父级，如果没有父级，则为NULL。

例如，如果我有一个类别ID为4，我希望查询返回以下结果：

array('4','3','1'); // Helpful > Programming > Websites

如果我有一个类别ID为6的话：

array('6','2'); // Desserts > Recipes

或者类别ID为1：

array('1');

我该如何构建这个查询？

- MultiDev

---

你尝试过使用循环吗？（针对 SQL，而非 PHP） - user2506641

你在数据库中期望有多少个类别？ - Maxime

这可以在未定义数字的情况下完成...我正在测试我的假设，今天稍后会回来。 - user2506641

要执行这些类型的查询，您需要存储更多的层次结构，例如使用MPTT（https://www.sitepoint.com/hierarchical-data-database-2/），但我发现将节点的物化路径存储到数据库中更容易，例如`Helpful`的mpath将是`1/3/4`（主键的串联）。 - thebjorn

可能是如何在MySQL中执行递归SELECT查询？的重复问题。 - Reeno

Answer 1

如果只有有限的分类深度，您可以使用left join获取父级类别，但这只有意义。对于无限分类深度，我会在PHP中完成。尽管如此，这里是一个示例查询：

select c1.id, c2.id, c3.id
from categories c1 
left join categories c2 on c2.id = c1.catparent
left join categories c3 on c3.id = c2.catparent
where c1.id = 4

如果类别4只有一个父级，则最后一个ID（c3.id）将为NULL。您需要在代码中考虑到这一点。

- Quagaar

---

谢谢您发布这篇文章，但我始终无法让它返回任何结果。 - MultiDev

连接表的表名中有一个拼写错误（catgories而不是categories）。但除此之外，查询是有效的。 - Quagaar

Answer 2

为了实现这一点，您可以创建一个过程。如果使用phpmyadmin，您可以进入数据库，转到SQL并插入以下内容：

DELIMITER //
CREATE PROCEDURE get_parents(IN cid int)
BEGIN
    DECLARE child_id int DEFAULT 0;
    DECLARE prev_id int DEFAULT cid;
    DECLARE loopran int DEFAULT 0; 

    SELECT CatParent into child_id 
    FROM categories WHERE ID=cid ;

    create TEMPORARY  table IF NOT EXISTS temp_table as (select * from categories where 1=0);
    truncate table temp_table;

    WHILE child_id <> 0 OR loopran <> 1 DO
        SET loopran = 1;

        insert into temp_table select * from categories WHERE ID=prev_id;
        SET prev_id = child_id;
        SET child_id=0;
        SELECT CatParent into child_id
        FROM categories WHERE ID=prev_id;
    END WHILE;

    select * from temp_table;
END //

该过程创建一个临时表来存储数据。变量loopran的作用是确保即使类别没有子项，父项也会作为结果返回。

接下来，检索结果：

$id = 5;

$result = "
CALL get_parents($id)
"; // Call the procedure just like as if it were a php function

$query = mysqli_query($conn, $result) or die(mysqli_error($conn));

$x = 0;

while ($row = mysqli_fetch_array($query)) {
    if ($x > 0) echo ", ";
    echo $row['ID'] . " | " . $row['CatParent'];
    $x++;
}

$id = 4 返回：4 | 3, 3 | 1

$id = 6 返回：6 | 2

$id = 1 返回：1 |

$id = 9 没有返回任何内容（如果行不存在，当然）。

存在一个巨大的问题。那就是，如果你最终陷入了一个指向循环中先前 id 的循环中，它将导致无限循环。为了解决这个问题，你需要退出 while 循环，在它试图添加已经添加过的东西的条件下。但我假设这种情况永远不会自然发生。（当然，这取决于你使用它的方式以及 ParentID 的设置）

来源和信用：@Meherzad - https://dev59.com/zWQn5IYBdhLWcg3w36M1#16514403

- user2506641