这里主要是针对于MySQL8.0以下版本,因为MySQL8.0版本出来了一个WITH RECURSIVE函数专门用来进行递归查询的
先看下表格数据,就是很普通的树结构数据,通过parentId关联上下级关系
下面我们先根据上级节点id递归获取所有的下级节点数据,比如id为1
SELECT T2.id, T2.parent_id, T2.name
FROM (SELECT @r AS _id, (SELECT @r := id FROM t_test WHERE parent_id = _id LIMIT 1) AS idFROM (SELECT @r := 1) vars,t_test h WHERE @r IS NOT NULL
) T1
JOIN t_test T2 ON T1._id = T2.parent_id
ORDER BY T2.id;
执行一下,可以看到可以获取到下级节点所有的数据
这里有个问题就是没有拿到当前节点数据,我们改一下
SELECT T2.id, T2.parent_id, T2.name
FROM (SELECT @r AS _id, @r := (SELECT id FROM t_test WHERE parent_id = _id LIMIT 1) AS idFROM (SELECT @r := 1) vars,t_testWHERE @r IS NOT NULL) T1
JOIN t_test T2
ON T1._id = T2.parent_id OR T1._id = T2.id
ORDER BY T2.id;
运行一下
可以发现虽然拿到了当前节点,但是也出现了重复数据问题,目前还没想好要怎么改,所以只能在外层接收数据的时候处理,或者再套一层group by一下
还有根据子节点查询所有上级节点的
SELECT T2.id, T2.parent_id, T2.name
FROM (SELECT @r AS _id, (SELECT @r := parent_id FROM t_test WHERE id = _id) AS parent_idFROM (SELECT @r := 5) vars,t_test h WHERE @r <> 0) T1 JOIN t_test T2 ON T1._id = T2.id
执行一下
如果只想要顶层的
SELECT T2.id, T2.parent_id, T2.name
FROM (SELECT @r AS _id, (SELECT @r := parent_id FROM t_test WHERE id = _id) AS parent_idFROM (SELECT @r := 5) vars,t_test h WHERE @r <> 0) T1 JOIN t_test T2 ON T1._id = T2.id
ORDER BY T2.id LIMIT 1;
运行一下
最后告诫大家一点,使用递归一定要小心小心再小心,特别是在SQL里面递归,一旦因为数据问题等导致无限循环就麻烦了,所以使用一定要慎重