MySQL通过SQL语句进行递归查询
这里主要是针对于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里面递归，一旦因为数据问题等导致无限循环就麻烦了，所以使用一定要慎重