mysql的递归查询

描述:最近遇到了一个问题,在mysql中如何完成节点下的所有节点或节点上的所有父节点的查询? 在Oracle中我们知道有一个Hierarchical Queries可以通过CONNECT BY来查询,但是,在MySQL中还没有对应的函数!!! 下面给出一个function来完成的方法 下面是sql脚本,想要运行的直接赋值粘贴进数据库即可。

好记性不如烂笔头
下面给出一个function来完成的方法

下面是sql脚本,想要运行的直接赋值粘贴进数据库即可。

创建表treenodes(可以根据需要进行更改)


– Table structure for treenodes


DROP TABLE IF EXISTS treenodes;
CREATE TABLE treenodes (
id int(11) NOT NULL,
nodename varchar(20) DEFAULT NULL,
pid int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


– Table structure for treenodes


插入几条数据


– Records of treenodes


INSERT INTO treenodes VALUES (‘1’, ‘A’, ‘0’);
INSERT INTO treenodes VALUES (‘2’, ‘B’, ‘1’);
INSERT INTO treenodes VALUES (‘3’, ‘C’, ‘1’);
INSERT INTO treenodes VALUES (‘4’, ‘D’, ‘2’);
INSERT INTO treenodes VALUES (‘5’, ‘E’, ‘2’);
INSERT INTO treenodes VALUES (‘6’, ‘F’, ‘3’);
INSERT INTO treenodes VALUES (‘7’, ‘G’, ‘6’);
INSERT INTO treenodes VALUES (‘8’, ‘H’, ‘0’);
INSERT INTO treenodes VALUES (‘9’, ‘I’, ‘8’);
INSERT INTO treenodes VALUES (‘10’, ‘J’, ‘8’);
INSERT INTO treenodes VALUES (‘11’, ‘K’, ‘8’);
INSERT INTO treenodes VALUES (‘12’, ‘L’, ‘9’);
INSERT INTO treenodes VALUES (‘13’, ‘M’, ‘9’);
INSERT INTO treenodes VALUES (‘14’, ‘N’, ‘12’);
INSERT INTO treenodes VALUES (‘15’, ‘O’, ‘12’);
INSERT INTO treenodes VALUES (‘16’, ‘P’, ‘15’);
INSERT INTO treenodes VALUES (‘17’, ‘Q’, ‘15’);
把下面的语句直接粘贴进命令行执行即可(注意修改传入的参数,默认rootId,表明默认treenodes)

根据传入id查询所有父节点的id

delimiter // 
CREATE FUNCTION `getParList`(rootId INT)
RETURNS varchar(1000) 
BEGIN
    DECLARE sTemp VARCHAR(1000);
    DECLARE sTempPar VARCHAR(1000); 
    SET sTemp = ''; 
    SET sTempPar =rootId; 

    #循环递归
    WHILE sTempPar is not null DO 
        #判断是否是第一个,不加的话第一个会为空
        IF sTemp != '' THEN
            SET sTemp = concat(sTemp,',',sTempPar);
        ELSE
            SET sTemp = sTempPar;
        END IF;
        SET sTemp = concat(sTemp,',',sTempPar); 
        SELECT group_concat(pid) INTO sTempPar FROM treenodes where pid<>id and FIND_IN_SET(id,sTempPar)>0; 
    END WHILE; 

RETURN sTemp; 
END
//

执行命令

select * from treenodes where FIND_IN_SET(id,getParList(15)); //直接运行就可以了  id为表本来的id 15为父级id
结果:

这里写图片描述
根据传入id查询所有子节点的id

delimiter // 
CREATE FUNCTION `getChildList`(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  treeNodes where FIND_IN_SET(pid,sTempChd)>0;
    END WHILE;
    RETURN sTemp; 
END
//

执行命令

select * from treenodes where FIND_IN_SET(id,getChildList(7));
结果:

这里写图片描述


转载自:https://blog.youkuaiyun.com/zhangpengself520/article/details/77451805

<think>嗯,用户问的是如何在MySQL中使用递归查询,特别是CTE的例子。我需要先回忆一下MySQL对CTE的支持情况。记得从MySQL 8.0开始支持CTE,包括递归的。那用户可能用的是8.0或更高版本,但需要确认版本兼容性。 首先,CTE的基本结构是WITH子句,然后定义CTE的名称和列,再写递归部分。递归CTE通常由两部分组成:非递归的种子查询递归部分,用UNION ALL连接。比如生成数字序列,或者层次结构查询,比如组织架构或者树形数据。 用户可能想要一个具体的例子,比如生成一个数列,或者遍历树形结构的数据。例如,生成1到5的数字,或者查询员工层级。比如引用中的例子,递归部分是从种子开始,逐步增加n直到满足条件。例如,引用1和3中提到的例子,使用n < 5或者n < 6,然后递归增加n的值。 我需要确保例子正确,符合MySQL语法。比如,在递归CTE中,种子部分初始化,然后递归部分引用CTE自身。需要注意,递归CTE必须使用UNION ALL,并且递归部分不能包含聚合函数、窗口函数等。 另外,用户可能遇到的问题,比如无限递归,这时候可能需要设置cte_max_recursion_depth参数。比如默认是1000,超过会报错。所以在例子中可能需要提到这一点,或者给出如何修改这个设置的方法。 可能还需要举一个实际数据表的例子,比如员工表,里面有id和manager_id,然后通过递归CTE查询某个员工的所有下属。这时候CTE的种子是选择某个经理,然后递归连接下属员工。 需要确保例子中的SQL语句正确,比如正确的列名,正确的递归条件。比如在员工例子中,种子部分选择id为1的员工,然后递归部分连接员工表,找到manager_id等于当前层id的员工,直到没有更多层级。 此外,用户可能想知道递归CTE的应用场景,比如处理树形数据、生成序列、展开层次结构等。可以结合引用中的例子,如引用2提到的重用子查询,但这里主要是递归应用。 最后,可能需要提醒用户关于版本要求,以及常见错误,比如忘记UNION ALL,或者递归条件不正确导致无限循环。同时,给出如何限制递归深度的方法,避免报错。</think>在MySQL中,使用公共表表达式(CTE)实现递归查询的步骤如下: ### 递归CTE基本结构 ```sql WITH RECURSIVE cte_name AS ( -- 初始查询(种子成员) SELECT ... UNION ALL -- 递归成员(引用CTE自身) SELECT ... FROM cte_name ... ) SELECT * FROM cte_name; ``` ### 示例1:生成数字序列 生成1-5的整数序列: ```sql WITH RECURSIVE numbers AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM numbers WHERE n < 5 ) SELECT * FROM numbers; ``` 结果: ``` n 1 2 3 4 5 ``` 该查询通过递归将初始值1逐次+1,直到满足`n < 5`的条件[^1][^3] ### 示例2:层次结构遍历 假设有员工表`employees`: ```sql CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(20), manager_id INT ); INSERT INTO employees VALUES (1, 'CEO', NULL), (2, 'CTO', 1), (3, 'Dev Lead', 2), (4, 'Senior Dev', 3); ``` 查询CTO(id=2)的下属层级: ```sql WITH RECURSIVE emp_tree AS ( SELECT id, name, manager_id, 0 AS depth FROM employees WHERE id = 2 UNION ALL SELECT e.id, e.name, e.manager_id, et.depth + 1 FROM employees e INNER JOIN emp_tree et ON e.manager_id = et.id ) SELECT * FROM emp_tree; ``` 结果: ``` id | name | manager_id | depth 2 | CTO | 1 | 0 3 | Dev Lead | 2 | 1 4 | Senior Dev| 3 | 2 ``` 通过递归连接实现层级展开,`depth`列记录层级深度[^4] ### 注意事项 1. 必须包含`RECURSIVE`关键字 2. 递归成员不能包含: - 聚合函数 - 窗口函数 - `GROUP BY`/`ORDER BY` - `DISTINCT` 3. 默认递归深度限制为1000次,可通过`SET @@cte_max_recursion_depth = 1000000;`调整[^1]
评论 4
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值