php - 查找顶级父级(递归)

Question

产品表:

id | id_parent | margin
------------------------
1  | NULL      | 10
2  | 1         | 20
3  | NULL      | 15
4  | 2         | 10
5  | 4         | 25

根据上面的数据，如何得到product tree (id: 5)的margin total？它没有固定的深度，它是动态的。

我目前通过 PHP 的迭代(递归 mysqli_query)实现了这一点，但它消耗了大量的托管内存(错误 508)。

这是 PHP 函数:

function get_margintotal($product) {
    global $link;

    $query = "SELECT id_parent, margin FROM `products` WHERE id = ".$product['id_parent'];
    if (!$temp_products = mysqli_query($link, $query)) {
        die(json_encode(array("errmsg" => "Selecting supplier's margin.")));
    }
    $temp_product = mysqli_fetch_assoc($temp_products);
    $temp_product['margin'] = $product['margin'] + $temp_product['margin'];

    if (isset($temp_product['id_parent'])) {
        return get_margintotal($temp_product);
    }
    else {
        return $temp_product['margin'];
    }
}

所以我想知道是否可以在 MySQL 中进行动态迭代或者如何优化函数以减少服务器内存使用？

谢谢。

Answer 1

使用变量递归的 MySQL 解决方案。

CREATE TABLE `finding_top_parent_recursive_46170847` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `id_parent` int(11) DEFAULT NULL,
  `margin` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;


INSERT INTO `finding_top_parent_recursive_46170847` (`id`, `id_parent`, `margin`)
VALUES
    (1, NULL, 10),
    (2, 1, 20),
    (3, NULL, 15),
    (4, 2, 10),
    (5, 4, 25);


-- set the seed leaf id
set @id = 5 ;
select sum(margin) margin from (
    -- include the seed 
    select id, margin from finding_top_parent_recursive_46170847 products
    where id = @id
    union all
    -- get the parents
    select @id := ( 
        select id_parent 
        from finding_top_parent_recursive_46170847 products
        where id = @id ) id,
        ( select margin 
        from finding_top_parent_recursive_46170847 products
        where id = @id ) margin
    from 
    finding_top_parent_recursive_46170847 products
    where @id is not null
) margins

-- results
margin
65

递归mysql结果的思路

https://www.google.com.au/search?q=mysql+recursive+left+join

https://dba.stackexchange.com/questions/46127/recursive-self-joins

Hierarchical queries in MySQL