使用函数的方法查询: 1.创建函数get_all_children_node(targetId) CREATE DEFINER=`root`@`%` FUNCTION `get_all_childre
使用函数的方法查询:
1.创建函数get_all_children_node(targetId)
CREATE DEFINER=`root`@`%` FUNCTION `get_all_children_node`(`targetId` int) RETURNS varchar(1000) CHARSET utf8BEGIN declare resultNode varchar(1000); -- 返回的结果字符串 declare parentNode varchar(1000); -- 每次查询的父节点 set resultNode=""; set parentNode=targetId; while parentNode is not null do -- 当父节点不为空时执行 if (parentNode!=targetId) then if(resultNode!="") then set resultNode = concat(resultNode, ",", parentNode); else set resultNode = concat(parentNode); end if; end if; select group_concat(id) into parentNode from node where find_in_set(parent_id, parentNode) > 0; end while; return resultNode;END
2.调用函数。
select * from node where find_in_set(id,get_all_children_node(2));
3.数据库表语句。
CREATE TABLE `node` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `parent_id` bigint(20) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
4.插入数据。
INSERT INTO `node`(`id`, `parent_id`, `name`) VALUES (1, 0, 'A');INSERT INTO `node`(`id`, `parent_id`, `name`) VALUES (2, 1, 'B');INSERT INTO `node`(`id`, `parent_id`, `name`) VALUES (3, 1, 'C');INSERT INTO `node`(`id`, `parent_id`, `name`) VALUES (4, 2, 'D');INSERT INTO `node`(`id`, `parent_id`, `name`) VALUES (5, 2, 'E');INSERT INTO `node`(`id`, `parent_id`, `name`) VALUES (6, 3, 'F');INSERT INTO `node`(`id`, `parent_id`, `name`) VALUES (7, 4, 'G');INSERT INTO `node`(`id`, `parent_id`, `name`) VALUES (8, 4, 'H');
5.查询该节点的所有父节点。
CREATE DEFINER=`root`@`%` FUNCTION `get_all_parent_node`(`targetId` int) RETURNS varchar(1000) CHARSET utf8BEGIN declare resultNode varchar(1000); -- 返回的结果字符串 declare parentNode varchar(1000); -- 每次查询的父节点 set resultNode=""; set parentNode=targetId; while parentNode is not null do -- 当父节点不为空时执行 if (parentNode!=targetId) then if(resultNode!="") then set resultNode = concat(resultNode, ",", parentNode); else set resultNode = concat(parentNode); end if; end if; select group_concat(parent_id) into parentNode from node where find_in_set(id, parentNode) > 0; end while; return resultNode;END