在mqsql实现树形结构查询

子节点查询所有父节点

查询函数(包含自己,可以避免父节点查询无结果,以及区分传入的节点是否存在)

  DROP FUNCTION IF EXISTS queryParentProductCatalog;
     DELIMITER ;;
     CREATE FUNCTION queryParentProductCatalog(catalogId INT)
     RETURNS VARCHAR(21845)
     BEGIN
     DECLARE sTemp VARCHAR(21845);
     DECLARE sTempChd INT;
     SET sTemp= catalogId;
     SELECT parent_id INTO sTempChd FROM t_product_catalog WHERE catalog_id = catalogId;
     IF sTempChd = 111737865 OR sTempChd = 2018036623 THEN
     SET sTemp= CONCAT(sTemp,',',sTempChd);
     END IF;
     WHILE (sTempChd is not NULL AND sTempChd <> 0 AND sTempChd <> 111737865 AND sTempChd <> 2018036623) DO
     SELECT parent_id INTO sTempChd FROM t_product_catalog WHERE catalog_id = sTempChd;
     SET sTemp= CONCAT(sTemp,',',sTempChd);
     END WHILE;
     RETURN sTemp;
     END
     ;;
     DELIMITER ;

其中111737865和 2018036623 是两大顶级节点id

查询语句

select * from t_product_catalog,(select queryParentProductCatalog(?1) qcpc) q where FIND_IN_SET(catalog_id,qcpc) AND flag = 0

父节点查询所有子节点

查询函数(包含自己,可以避免子节点查询无结果,以及区分传入的节点是否存在)

DROP FUNCTION IF EXISTS queryChildrenProductCatalog;
  DELIMITER ;;
  CREATE FUNCTION queryChildrenProductCatalog(catalogId INT)
  RETURNS VARCHAR(21845)
  BEGIN
  DECLARE sTemp VARCHAR(21845);
  DECLARE sTempChd VARCHAR(21845);
  SET sTemp= catalogId;
  SET sTempChd= catalogId;
  WHILE (sTempChd is not NULL) DO
  SET sTemp= CONCAT(sTemp,',',sTempChd);
  SELECT GROUP_CONCAT(catalog_id) INTO sTempChd FROM t_product_catalog WHERE FIND_IN_SET(parent_id,sTempChd) > 0;
  END WHILE;
  RETURN sTemp;
  END
  ;;
  DELIMITER ;

查询语句

select * from t_product_catalog,(select queryChildrenProductCatalog(?1) qcpc) q where FIND_IN_SET(catalog_id,qcpc) AND flag = 0