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

港控/mmm° 2022-06-14 09:52 1574阅读 0赞

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

创建getParentList函数

SQL语句如下:

delimiter //
    CREATEFUNCTION `getParentList`(rootId INT)
    RETURNS varchar(1000)
    
    BEGIN
    DECLARE sTempVARCHAR(1000);
    DECLARE sTempParVARCHAR(1000);
    SET sTemp ='';
    SET sTempPar =rootId;
    
    #循环递归
    WHILE sTempPar is not null DO
    #判断是否是第一个，不加的话第一个会为空
    IF sTemp !='' THEN
    SET sTemp =concat(sTemp,',',sTempPar);
    ELSE
    SET 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可能会报如下错误:

![Center][]

解决方法:

执行此语句: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了

![Center 1][]

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

新建一张数据表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');

创建成功，如图:

![Center 2][]

执行查询语句:select \* from province where FIND\_IN\_SET(id,getParentList(17))

查询结果:

![Center 3][]

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

创建函数getChildrenList

SQL语句如下:

delimiter //
    CREATE FUNCTION `getChildrenList`(rootId INT)
    RETURNS varchar(1000)
    
    
    BEGIN
    DECLARE sTemp VARCHAR(1000);
    DECLARE sTempChd VARCHAR(1000);
    
    
    SET sTemp = '$';
    SET sTempChd =cast(rootId as CHAR);
    
    
    WHILE sTempChd is not null DO
    SET 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))

查询结果如下:

![Center 4][]

[Center]: /images/20220614/ce7e153290524af1a30c0ac9697466d9.png
[Center 1]: /images/20220614/fe709c99567c4befa9c0a16fbe6584f0.png
[Center 2]: /images/20220614/551f30cbd1a8472eb7818b0375354cb5.png
[Center 3]: /images/20220614/33b9b4794dec49b08b77e3c5f0a50697.png
[Center 4]: /images/20220614/c96e0d74eace4d01a7cbecd338382bcd.png