mysql 递归查询子节点

表结构及数据与oracle递归查询子节点中的一致

数据结构稍有不同

drop table if EXISTS city;
create table city(
id int primary key,
name varchar(50) not null,
parent_id int
);

sql

1.找出所有的节点

select id,name,parent_id from city t order by parent_id, id 

2.如果节点的 父id 在 选择的节点id中 将该节点添加到选择的节点集合中（递归）

select t1.id,t1.name,parent_id,
              if(find_in_set(parent_id, @pids) > 0, @pids := concat(@pids, ',', id), 0) as ischild
              from ( select id,name,parent_id from city t order by parent_id, id ) t1,
                  (select @pids := 2) t2

3.最后将所有子节点返回

select id,name,ischild from (
              select t1.id,t1.name,
              if(find_in_set(parent_id, @pids) > 0, @pids:= concat(@pids, ',', id), 0) as ischild
              from ( 
                    select id,name,parent_id from city t order by parent_id, id 
                  ) t1,
                  (select @pids:= 选择的节点id) t2
             ) t3 
where ischild != 0;

关键方法

find_in_set(str,strList) ：查询字段(strlist)中包含(str)的结果

    参数：str 要查询的字符串,strlist 字段名 参数以”,”分隔 如 (1,2,6,8)

    结果：不包含——0, 包含——返回第一个存在的位置：select FIND_IN_SET('2', '1,2,6,8'); 结果 2

if（express1,express2,express3）：当exprss1成立时，执行express2,否则执行express3;

自定义函数实现

获取所有下级节点id

drop function if EXISTS getChildren;
CREATE FUNCTION getChildren (city_id INT) RETURNS VARCHAR (4000)
BEGIN
DECLARE result VARCHAR (4000);
DECLARE child VARCHAR (4000);
SET result = '';
SET child = CAST(city_id AS CHAR);
WHILE child IS NOT NULL DO
SET result = CONCAT(result, ',', child);
SELECT
	GROUP_CONCAT(id) INTO child
FROM
	city
WHERE
	FIND_IN_SET(parent_id, child) > 0;
END
WHILE;
RETURN result;
END

调用

select * from city where FIND_IN_SET(id,getChildren(2));

方法/关键字说明

表达式 into 变量名 将 表达式的值插入到变量中（赋值）

group_concat([distinct]要连接的字段 [order by 排序字段 asc/desc] [separator '分隔符']) : 将group by 产生的同一个分组中的值连接起来，返回一个字符串结果

    说明：通过使用 distinct 可以去重;如果希望对结果进行排序,可以使用 order by; separator 是一个字符串,缺省为一个逗号,用于分隔连接的字段

    注意：若没有明确有group by 语句时，将所有满足条件的结果作为一个分组