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') ;