Mysql中的递归层次查询(根据父节点查找所有的子节点和根据子节点查询所有的父节点)的两种运用

来源：互联网发布：知乎python入门编辑：程序博客网时间：2024/04/30 06:12

1、根据子节点查询所有的父节点

创建getParentList函数

SQL语句如下:

delimiter //CREATEFUNCTION `getParentList`(rootId INT)RETURNS varchar(1000)BEGINDECLARE sTempVARCHAR(1000);DECLARE sTempParVARCHAR(1000);SET sTemp ='';SET sTempPar =rootId;#循环递归WHILE sTempPar is not null DO#判断是否是第一个，不加的话第一个会为空IF sTemp !='' THENSET sTemp =concat(sTemp,',',sTempPar);ELSESET sTemp = sTempPar;ENDIF;SET sTemp =concat(sTemp,',',sTempPar);SELECTgroup_concat(pid) INTO sTempPar FROM province where pid<>idand FIND_IN_SET(id,sTempPar)>0;ENDWHILE;RETURN sTemp;END//

此时Mysql可能会报如下错误:

解决方法:

执行此语句:show VARIABLES like "log_bin_trust_function_creators";

发现log_bin_trust_function_creators的值为OFF(这是默认值)

那么，我们再执行语句:set global log_bin_trust_function_creators = 1;

现在再来查看log_bin_trust_function_creators的值已经变为ON了

最后，函数就可以创建成功了。

新建一张数据表province

执行以下sql语句:

SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for province-- ----------------------------DROP TABLE IF EXISTS `province`;CREATE TABLE `province` (  `id` int(10) NOT NULL,  `name` varchar(10) NOT NULL,  `pid` int(10) unsigned NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------ Records of province-- ----------------------------INSERT INTO `province` VALUES ('1', '福建', '0');INSERT INTO `province` VALUES ('2', '湖南', '0');INSERT INTO `province` VALUES ('3', '湖北', '0');INSERT INTO `province` VALUES ('4', '长沙', '2');INSERT INTO `province` VALUES ('5', '郴州', '2');INSERT INTO `province` VALUES ('6', '武汉', '3');INSERT INTO `province` VALUES ('7', '武昌', '3');INSERT INTO `province` VALUES ('8', '厦门', '1');INSERT INTO `province` VALUES ('9', '福州', '1');INSERT INTO `province` VALUES ('10', '泉州', '1');INSERT INTO `province` VALUES ('11', '闽侯', '9');INSERT INTO `province` VALUES ('12', '长乐', '9');INSERT INTO `province` VALUES ('13', '安溪', '10');INSERT INTO `province` VALUES ('14', '晋江', '10');INSERT INTO `province` VALUES ('15', '凤城', '13');INSERT INTO `province` VALUES ('16', '参内', '13');INSERT INTO `province` VALUES ('17', '龙湖', '15');

创建成功，如图:

执行查询语句:select * from province where FIND_IN_SET(id,getParentList(17))

查询结果:

2、根据父节点查找所有的子节点

创建函数getChildrenList

SQL语句如下:

delimiter //CREATE FUNCTION `getChildrenList`(rootId INT)RETURNS varchar(1000)BEGINDECLARE sTemp VARCHAR(1000);DECLARE sTempChd VARCHAR(1000);SET sTemp = '$';SET sTempChd =cast(rootId as CHAR);WHILE sTempChd is not null DOSET sTemp = concat(sTemp,',',sTempChd);SELECT group_concat(id) INTO sTempChd FROM province where FIND_IN_SET(pid,sTempChd)>0;END WHILE;RETURN sTemp;END//

执行查询语句:select * from province where FIND_IN_SET(id,getChildrenList(1))

查询结果如下:

阅读全文

0 0