主页 / dba / 问题 / 19109
Accepted
Robottinosino
Asked: 2012-06-12 20:29:34 +0800 CST

MySQL函数从层次结构返回类似面包屑的字符串

  • 772

显示创建表：

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 层深。

mysql hierarchy

3 个回答

  1. delimiter //
create function derp(IN myId int) returns varchar(255) begin
    select concat(t1.name, ' > ', t2.name, ' > ' t3.name) from a t1 join a t2 on t1.parent_id=t2.id join a t3 on t2.parent_id = t3.id where t1.id=myId;
end//

    没有测试，只是在我的头顶。供参考http://dev.mysql.com/doc/refman/5.5/en/create-procedure.html

    • 0
  2. Best Answer

    您可以使用以下函数返回 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 ;

    试用

    • 0

  3. 现在，受到我试图遵循的帕南德拉教义的启发，我写了对他正确答案的改写，在我看来有点“容易理解”。留给后人。现在的问题是：根据评论编写这样的函数真的是个坏主意吗？如果是这样，为什么？

    DELIMITER $$

DROP FUNCTION IF EXISTS places.get_breadcrumb_string $$

CREATE FUNCTION places.get_breadcrumb_string
    (LEAF_ID INTEGER)
        RETURNS VARCHAR(1024)

    BEGIN

        DECLARE HIERARCHY_SEPARATOR VARCHAR(255);  # Hierarchy step-down marker
        DECLARE BREADCRUMB_STRING   VARCHAR(1024); # Value to return, will take the form "Node -separator- Node ..."

        DECLARE V_PLACE_ID          INTEGER;
        DECLARE V_PLACE_NAME        VARCHAR(255);
        DECLARE V_PARENT_PLACE_ID   INTEGER;

        DECLARE ID_TO_LOOKUP_NEXT   INTEGER;

        SET HIERARCHY_SEPARATOR   = ' > ';         # Intended format: "Node > Node > Node"
        SET BREADCRUMB_STRING     = '';

        SET ID_TO_LOOKUP_NEXT     = LEAF_ID;

        LoopingLookUp: LOOP

            SELECT
                `id`,
                `name`,
                `parent_id`
            INTO
                V_PLACE_ID,
                V_PLACE_NAME,
                V_PARENT_PLACE_ID
            FROM
                `places`
            WHERE
                `_id` = ID_TO_LOOKUP_NEXT
            ;

            SET BREADCRUMB_STRING = CONCAT(
                HIERARCHY_SEPARATOR,
                V_PLACE_NAME,
                BREADCRUMB_STRING
            );

            IF V_PARENT_PLACE_ID = V_PLACE_ID THEN
                LEAVE LoopingLookUp;
            END IF;

            SET ID_TO_LOOKUP_NEXT = V_PARENT_PLACE_ID;

        END LOOP;

        SET BREADCRUMB_STRING = TRIM(LEADING HIERARCHY_SEPARATOR FROM BREADCRUMB_STRING);

        RETURN BREADCRUMB_STRING;

END $$

DELIMITER ;
    • 0

相关问题