使用纯MySQL获取树的根路径 [英] Get root path of a tree with pure MySQL

问题描述

我想获取从节点到树根的路径.例如，这是我的树:

I want to get path from node to root of a tree. This is my tree for example:

id  |   name    |   parent_id
------------------------------
1   |   mike    |   0
2   |   danny   |   1
3   |   peter   |   1
4   |   clark   |   2
5   |   lily    |   1
6   |   stefan  |   3
7   |   simon   |   3
8   |   boby    |   1
9   |   john    |   4
10  |   elly    |   4

我用php和mysql编写了一个算法，但是速度很慢

I write an algoritm with php and mysql but it is slowly

public function GetRootPath($a_id) {
    $root="";
    $results="";
    while(1==1){
        $result = DB::select("SELECT id, parent_id FROM users WHERE id=$a_id");
        if($result[0]->refr!=0) {
               if($root==""){
                   $root=$result[0]->parent_id;
               }
               else {
                   $root=$result[0]->parent_id.'.'.$root;
               } 
            $a_id=$result[0]->parent_id;
        }
       else {
           break;
       }
    }
    return $root;
}

这怎么用纯MySQL编写?我对MySQL的过程和功能不是很了解.

How could this be written in pure MySQL? I'm not very aware with MySQL procedures and functions.

推荐答案

我认为存储过程可以工作:

I think stored procedures could work:

DELIMITER $$

DROP PROCEDURE IF EXISTS get_root;
CREATE PROCEDURE get_root(
   IN parentID INT,
   OUT rootID INT
)

BEGIN   
    SELECT parent_id FROM tree WHERE id = parentID INTO rootID;

    IF rootID = 0
        THEN SET rootID = parentID;
    ELSE
        CALL get_root(rootID, rootID);
    END IF;

END$$
DELIMITER ;

SET @@GLOBAL.max_sp_recursion_depth = 255;
SET @@session.max_sp_recursion_depth = 255; 

CALL get_root(4, @rootID);
SELECT @rootID;

这篇关于使用纯MySQL获取树的根路径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！