在开发新项目的时候,可能会遇上类似的功能需求,递归获取所有下级子节点的数据。这里介绍一种通过存储过程,实现的查询功能。本次测试,用的是MariaDB数据库,MariaDB是MySql数据库的一个分支,基本上完全兼容MySql数据库,所以本次分享的递归获取子节点数据的方法也适用于Mysql。
假设存在表mo2g_address,数据结构如下:idpidname
10中国
21广西
31湖南
42南宁
53永州
64江南区
75道县
下边就来说明如何实现如下的查询功能
a)获取中国包含的全部地址数据
b)获取南宁包含的全部地址数据
1)在MariaDB中执行下边的语句,创建存储函数,也可以通过phpmyadmin或其他管理工具执行delimiter //
CREATE FUNCTION `getChildList`(rootId INT) #rootId为你要查询的节点。
RETURNS varchar(1000)
BEGIN
#声明两个临时变量
DECLARE sTemp VARCHAR(1000);
DECLARE sTempChd VARCHAR(1000);
SET sTemp = '$';
SET sTempChd = cast(rootId as CHAR); #把rootId强制转换为字符。
WHILE sTempChd is not null DO
SET sTemp = concat(sTemp,',',sTempChd);#循环把所有节点连接成字符串。
SELECT group_concat(id) INTO sTempChd FROM mo2g_address where FIND_IN_SET(pid,sTempChd) > 0;
END WHILE;
RETURN sTemp;
END
2)执行查询语句,select * from mo2g_address where FIND_IN_SET(id, getChildList(?));
a)获取中国包含的全部地址数据select * from mo2g_address where FIND_IN_SET(id, getChildList(1));
b)获取南宁包含的全部地址数据select * from mo2g_address where FIND_IN_SET(id, getChildList(4));
如果要获取广西的所有子节点信息,只需要把?改为2,即可。
再进一步,如果存在用户表mo2g_useridaddress_idname
14A
25B
32C
46D
54E
我们要获取地址为南宁(ID=4)的所有用户数据,sql语句如下:select * from mo2g_user where address_id in (
select id from mo2g_address where FIND_IN_SET(id, getChildList(4))
)