MySQL函式從層次結構返回類似麵包屑的字元串

顯示創建表：

CREATE TABLE `a` (
 `id` int(11) NOT NULL,
 `name` varchar(255) DEFAULT NULL,
 `parent_id` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `parent_id` (`parent_id`),
 CONSTRAINT `a_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `a` (`id`) ON DELETE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1

選擇：

+----+---------+-----------+
| id | name    | parent_id |
+----+---------+-----------+
|  1 | World   |         1 |
|  2 | Europe  |         1 |
|  3 | USA     |         1 |
|  4 | France  |         2 |
|  5 | Germany |         2 |
+----+---------+-----------+

題：

• 我如何最好地定義一個函式，將 id 4 作為參數傳遞，返回：

“世界 > 歐洲 > 法國”

“parent_id”的這種分層“嵌套”可以任意深，而不僅僅是 3 層深。

您可以使用以下函式返回 N no:of 葉節點的層次結構，例如“世界 > 歐洲 > 法國 > S1 > S2”

DELIMITER $$

DROP FUNCTION IF EXISTS `test`.`test` $$ 
CREATE FUNCTION `test`.`test`(V_Argument_Parent_ID integer) 
RETURNS varchar(1024) No
SQL BEGIN

 Declare Done Integer default 0;   
 Declare V_id Integer default 0;  
 Declare V_parent_id Integer default 0;

 Declare V_name varchar(1024) default '';   
 Declare V_Result varchar(1024) default '';   
 Declare V_TempResult varchar(1024) default '';

 Declare continue handler for SQLWARNING set Done = 1;   
 Declare continue handler for NOT FOUND set Done = 1;   
 DECLARE EXIT HANDLER FOR 1001 set Done = 1;

 SELECT id, name, parent_id into V_id, V_name, V_parent_id 
 FROM a
 where ID = V_Argument_Parent_ID;

 IF V_parent_id = V_id then
   Set V_Result = V_name;   Else
   Set V_TempResult = ''; 
   Set V_TempResult = concat_ws('',V_TempResult,'->',V_name);

   MainLoop: Loop

    set V_Argument_Parent_ID = V_parent_id;
    SELECT id, name, parent_id into V_id, V_name, V_parent_id 
    FROM a where ID = V_Argument_Parent_ID;

    Set V_TempResult = concat_ws('',V_TempResult,'->',V_name);
    IF V_parent_id = V_id then
        Leave MainLoop;
    End IF;

   End Loop MainLoop;

   Set V_Result = V_TempResult;

   set V_Result = trim(both '->' from V_Result);

 End IF;

 Return V_Result;


END $$

DELIMITER ;

試用

引用自：https://dba.stackexchange.com/questions/19109