MYSQL再帰クエリ使用概要

メニュー権限の割り当てに使用するSQL再帰クエリをまとめます.

SELECT 
  * 
FROM
  treenodes 
WHERE id IN 
  (SELECT 
    id 
  FROM
    treenodes 
  WHERE pid = 1 
  UNION
  (SELECT 
    * 
  FROM
    (SELECT 
      id,
      nodename,
      pid,
      (@pv := CONCAT(@pv, ',', id)) AS a 
    FROM
      treenodes 
    ORDER BY pid,
      id) depart_sorted,
    (SELECT 
      @pv := 1) initialisation 
  WHERE FIND_IN_SET(pid, @pv) 
    AND LENGTH(@pv := CONCAT(@pv, ',', id)))) ;

-- --------------------------------------------------------------------------------------------
SELECT 
  id AS ID,
  pid AS  ID,
  levels AS        ,
  paths AS       
FROM
  (SELECT 
    id,
    pid,
    @le := IF (
      pid = 0,
      0,
      IF(
        LOCATE(CONCAT('|', pid, ':'), @pathlevel) > 0,
        -- @pathlevel      |pid
        SUBSTRING_INDEX(
          SUBSTRING_INDEX(@pathlevel, CONCAT('|', pid, ':'), - 1),
          '|',
          1
        ) + 1 --    
        ,
        @le + 1
      )
    ) levels,
    @pathlevel := CONCAT(@pathlevel, '|', id, ':', @le, '|') pathlevel,
    @pathnodes := IF(
      pid = 0,
      ',0',
      CONCAT_WS(
        ',',
        IF(
          LOCATE(CONCAT('|', pid, ':'), @pathall) > 0,
          SUBSTRING_INDEX(
            SUBSTRING_INDEX(@pathall, CONCAT('|', pid, ':'), - 1),
            '|',
            1
          ),
          @pathnodes
        ),
        pid
      )
    ) paths,
    @pathall := CONCAT(@pathall, '|', id, ':', @pathnodes, '|') pathall 
  FROM
    treenodes,
    (SELECT 
      @le := 0,
      @pathlevel := '',
      @pathall := '',
      @pathnodes := '') vv 
  ORDER BY pid,
    id) src 
ORDER BY id ;

-- -----------------------------------------------------------------------     （   ）
SELECT 
  id,
  nodename,
  pid 
FROM
  (SELECT 
    @r AS _id,
    (SELECT 
      @r := pid 
    FROM
      treenodes 
    WHERE id = _id) AS parent_id,
    @l := @l + 1 AS lvl 
  FROM
    (SELECT 
      @r := 4,
      @l := 0) vars,
    treenodes h 
  WHERE @r <> 0) T1 
  JOIN treenodes T2 
    ON T1._id = T2.id 
ORDER BY id ;

-- -----------------------------------------------------------------------------     （  ）
DELIMITER $$

CREATE FUNCTION queryParents (nodeId INT) RETURNS VARCHAR (4000) DETERMINISTIC 
BEGIN
  DECLARE sTemp VARCHAR (4000) ;
  DECLARE sTempChd VARCHAR (4000) ;
  SET sTemp = '$' ;
  SET sTempChd = CAST(nodeId AS CHAR) ;
  SET sTemp = CONCAT(sTemp, ',', sTempChd) ;
  SELECT 
    pid INTO sTempChd 
  FROM
    treenodes 
  WHERE id = sTempChd ;
  WHILE
    sTempChd <> 0 DO SET sTemp = CONCAT(sTemp, ',', sTempChd) ;
    SELECT 
      pid INTO sTempChd 
    FROM
      treenodes 
    WHERE id = sTempChd ;
  END WHILE ;
  RETURN sTemp ;
END $$

DELIMITER ;

SELECT 
  queryParents (11) ;

SELECT 
  * 
FROM
  treenodes 
WHERE FIND_IN_SET(id, queryParents (11)) ;

-- ------------------------------------------------------------     
WITH RECURSIVE TEMP AS 
(SELECT 
  id,
  nodename,
  pid 
FROM
  `treenodes` 
WHERE id = 1 
UNION
ALL 
SELECT 
  B.id,
  B.nodename,
  B.pid 
FROM
  TEMP A 
  INNER JOIN `treenodes` B 
    ON B.pid = A.Id) 
SELECT 
  Id,
  nodename,
  pid 
FROM
  TEMP -- ------------------------------- ------------------------------------------------------
  DELIMITER $$

CREATE FUNCTION getChildren (nodeId INT) RETURNS VARCHAR (4000) DETERMINISTIC 
BEGIN
  DECLARE oTemp VARCHAR (4000) ;
  DECLARE oTempChild VARCHAR (4000) ;
  SET oTemp = '' ;
  SET oTempChild = CAST(nodeId AS CHAR) ;
  WHILE
    oTempChild IS NOT NULL DO SET oTemp = CONCAT(oTemp, ',', oTempChild) ;
    SELECT 
      GROUP_CONCAT(id) INTO oTempChild 
    FROM
      treenodes 
    WHERE FIND_IN_SET(pid, oTempChild) > 0 ;
  END WHILE ;
  RETURN oTemp ;
END $$

DELIMITER ;

SELECT 
  getChildren (1) ;

SELECT 
  * 
FROM
  treenodes 
WHERE FIND_IN_SET(id, getChildren (1)) ;

SELECT 
  * 
FROM
  treenodes 
WHERE id IN 
  (SELECT 
    id 
  FROM
    getChildren (1)) ;

DELETE 
FROM
  (SELECT 
    id 
  FROM
    treenodes 
  WHERE FIND_IN_SET(id, getChildren (1))) ;

SELECT 
  FIND_IN_SET(id, getChildren (1)) 
FROM
  treenodes ;

--             
DELETE 
  treenodes 
FROM
  (SELECT 
    id 
  FROM
    treenodes 
  WHERE FIND_IN_SET(id, getChildren (1))) AS deleting,
  treenodes 
WHERE deleting.id = treenodes.id -- --------------------------------------------------------------------------
  --             
  SELECT 
    * 
  FROM
    (SELECT 
      id 
    FROM
      treenodes 
    WHERE FIND_IN_SET(id, getChildren (1))) AS deleting,
    treenodes 
  WHERE deleting.id = treenodes.id --   
    INSERT INTO `treenodes` 
    VALUES
      ('1', 'A', '0') ;

INSERT INTO `treenodes` 
VALUES
  ('2', 'B', '1') ;

INSERT INTO `treenodes` 
VALUES
  ('3', 'C', '1') ;

INSERT INTO `treenodes` 
VALUES
  ('4', 'D', '2') ;

INSERT INTO `treenodes` 
VALUES
  ('5', 'E', '2') ;

INSERT INTO `treenodes` 
VALUES
  ('6', 'F', '3') ;

INSERT INTO `treenodes` 
VALUES
  ('7', 'G', '6') ;

INSERT INTO `treenodes` 
VALUES
  ('8', 'H', '0') ;

INSERT INTO `treenodes` 
VALUES
  ('9', 'I', '8') ;

INSERT INTO `treenodes` 
VALUES
  ('10', 'J', '8') ;

INSERT INTO `treenodes` 
VALUES
  ('11', 'K', '8') ;

INSERT INTO `treenodes` 
VALUES
  ('12', 'L', '9') ;

INSERT INTO `treenodes` 
VALUES
  ('13', 'M', '9') ;

INSERT INTO `treenodes` 
VALUES
  ('14', 'N', '12') ;

INSERT INTO `treenodes` 
VALUES
  ('15', 'O', '12') ;

INSERT INTO `treenodes` 
VALUES
  ('16', 'P', '15') ;

INSERT INTO `treenodes` 
VALUES
  ('17', 'Q', '15') ;