56,677
社区成员
发帖
与我相关
我的任务
分享
use world;
create table if not exists tb(id int,name varchar(50),pid int,sort int,parent varchar(100),child varchar(100));
insert into tb
values
(1,'水果',0, null,null,null),
(2,'热带水果',1,null,null,null),
(3,'菠萝',2,null,null,null),
(4,'香蕉',2,null,null,null),
(5,'南美菠萝',3,null,null,null);
drop function if exists getchildlist;
/*
1.
本来是想要用while found_rows()>0 来判断的,
因为select group_concat(id) into strChildT from tb where find_in_set(pid,strChildT)
如果group_concat(id) 为null时,也会返回1,所以修改为 while strChildT is not null.
*/
CREATE DEFINER=`root`@`%` FUNCTION `getChildList`(idd int) RETURNS varchar(1000) CHARSET utf8
READS SQL DATA
begin
declare strT varchar(1000);
declare strChildT varchar(1000);
set strT = '$'; /*初始化*/
set strChildT = cast(idd as char); /*凡事需要查找子节点的 父节点都会放到这里*/
while strChildT is not null do
set strT = concat(strT , ',' , strChildT); /*第一次的时候就是: $,idd */
select group_concat(id) into strChildT from tb where find_in_set(pid,strChildT);
end while;
return strT; /*返回: idd所有子节点的逗号间隔的字符串,包含idd节点本身*/
end
--2.显示当前id的所有子节点列表
mysql> select *,getChildList(id) from tb;
+------+----------+------+------+--------+-------+------------------+
| id | name | pid | sort | parent | child | getChildList(id) |
+------+----------+------+------+--------+-------+------------------+
| 1 | 水果 | 0 | NULL | NULL | NULL | $,1,2,3,4,5 |
| 2 | 热带水果 | 1 | NULL | NULL | NULL | $,2,3,4,5 |
| 3 | 菠萝 | 2 | NULL | NULL | NULL | $,3,5 |
| 4 | 香蕉 | 2 | NULL | NULL | NULL | $,4 |
| 5 | 南美菠萝 | 3 | NULL | NULL | NULL | $,5 |
+------+----------+------+------+--------+-------+------------------+
5 rows in set (0.03 sec)
--3.查询节点1下面的所有子节点
mysql> select * from tb where find_in_set(id,getChildList(1));
+------+----------+------+------+--------+-------+
| id | name | pid | sort | parent | child |
+------+----------+------+------+--------+-------+
| 1 | 水果 | 0 | NULL | NULL | NULL |
| 2 | 热带水果 | 1 | NULL | NULL | NULL |
| 3 | 菠萝 | 2 | NULL | NULL | NULL |
| 4 | 香蕉 | 2 | NULL | NULL | NULL |
| 5 | 南美菠萝 | 3 | NULL | NULL | NULL |
+------+----------+------+------+--------+-------+
5 rows in set (0.16 sec)