Início / dba / Perguntas / 19109
Accepted
Robottinosino
Asked: 2012-06-12 20:29:34 +0800 CST

Função MySQL para retornar string tipo breadcrumb da estrutura hierárquica

  • 772

Mostrar criar tabela:

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

Selecione:

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

Pergunta:

  • como defino melhor uma função que, passada id 4 como argumento, retorna:

    "Mundo > Europa > França"

Esse "aninhamento" hierárquico por "parent_id" pode ser arbitrariamente profundo, não apenas 3 níveis de profundidade.

mysql hierarchy

3 respostas

  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//

    Não testado, apenas de cabeça. Para referência http://dev.mysql.com/doc/refman/5.5/en/create-procedure.html

    • 0
  2. Best Answer

    Você pode usar a seguinte função para retornar a estrutura hierárquica para N no: de nós de folha como "Mundo > Europa > França > 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 ;

    Tente

    • 0

  3. Agora, inspirado pelos ensinamentos de Phanindra cujos princípios tentei seguir, escrevi o que me parece um pouco "mais fácil para os olhos" reformulando sua resposta correta. Aqui para a posteridade. Agora a pergunta: foi realmente uma má ideia escrever uma função como esta, de acordo com os comentários? E se sim, por quê?

    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

relate perguntas