mysql 父子结构数据查询

父子结构主要应用在血缘分析和影响性分析方面。

create table test.mysql_lineage_demo (
​	id int,
  pid int,
  nm varchar(64)
);
insert into test.mysql_lineage_demo 
Select 1,     NULL,    'tt'
Union All Select 2,     1,      'aa'
Union All Select 3,     1,      'bb'
Union All Select 4,     2,      'cc'
Union All Select 5,     2,      'gg'
Union All Select 6,     4,      'yy'
Union All Select 7,     4,      'jj'
Union All Select 8,     7,      'll'
Union All Select 9,     NULL,  'uu'
Union All Select 10,    9,     'oo'
;

*血缘分析*

创建查询子节点的函数：

CREATE FUNCTION `getChildIdList`(rootId) int RETURNS varchar(1000) CHARSET utf8mb4
    DETERMINISTIC
BEGIN
    DECLARE sTemp VARCHAR(1000);
    DECLARE sTempChd VARCHAR(1000);
 
    SET sTemp = '$';
    SET sTempChd = cast(rootId as CHAR);
 
    WHILE sTempChd is not null DO
        SET sTemp = concat(sTemp,',',sTempChd);
        SELECT group_concat(id) INTO sTempChd FROM test.mysql_lineage_demo where FIND_IN_SET(pid,sTempChd) > 0;
    END WHILE;
    RETURN sTemp;
END

查询语句：

select * from test.mysql_lineage_demo  where FIND_IN_SET(pid,getChildIdList(2)); 

*影响分析*

创建查询子节点的函数：

CREATE FUNCTION `getParentIdList`(rootid int) RETURNS varchar(1000) CHARSET utf8mb4
    DETERMINISTIC
BEGIN
    DECLARE sTemp VARCHAR(1000);
    DECLARE sTempChd VARCHAR(1000);
    SET sTemp = '$';
    SET sTempChd = cast(rootid as CHAR);
  WHILE sTempChd is not null DO
        SET sTemp = concat(sTemp,',',sTempChd);
        SELECT group_concat(pid) INTO sTempChd FROM test.mysql_lineage_demo where FIND_IN_SET(id,sTempChd) > 0;
  END WHILE;
    RETURN sTemp;
END

查询语句：

select * from test.mysql_lineage_demo  where FIND_IN_SET(id,getParentIdList(2));