MySQL实现树状所有子节点查询的方法
刚才小姐妹给我发了个消息,她面试的公司先给她发了个题目,然后做出来发邮箱,剩下的以后再说。
有一说一,现在面试都这么玩了吗?面试造大炮,入职拧螺丝我就不说了。这几天我也在面试,好家伙 杭州各种大小周,单休,晚上到10点以后才算加班,也不给钱,周末加班才可以调休???啊?钱也给不到位 5年以上给个一两万,你当我卖白菜呢?跟阿里学的什么臭毛病,人阿里给钱到位,加班也可以学到技术,你这纯粹是为了铺你生产线啊,凭什么?
好了 都是题外话,先看一下题目吧。
题目很简单,就是查一个无限分类,具体来说就是MySQL实现树状所有子节点查询的方法。
但是需要运行时间尽量短,这个就不好弄了。
众所周知 Oracle中有 start with , connect by 去使用
MySQL8有临时表 with 可用,但是一般人家都是MySQL5.6、5.7,那应该怎么高效去操作呢
当然办法很多 有利用函数来得到所有子节点号,利用临时表和过程递归,以及利用中间表和过程
每种办法都有优缺点,这里不再赘述,我用的办法是最后一种 存储过程+中间表
先看一下解决办法吧
创建存储过程如下。由于MySQL中不允许在同一语句中对临时表多次引用,只以使用普通表tmpLst来实现了。当然你的程序中负责在用完后清除这个表。
drop PROCEDURE IF EXISTS showTreeNodes;
CREATE PROCEDURE showTreeNodes (IN rootid INT)
BEGIN
DECLARE Level int ;
drop TABLE IF EXISTS tmpLst;
CREATE TABLE tmpLst (
id int,
nLevel int,
sCort varchar(8000)
);
Set Level=0 ;
INSERT into tmpLst SELECT id,Level,ID FROM `category` WHERE PID=rootid;
WHILE ROW_COUNT()>0 DO
SET Level=Level+1 ;
INSERT into tmpLst
SELECT A.ID,Level,concat(B.sCort,A.ID) FROM `category` A,tmpLst B
WHERE A.PID=B.ID AND B.nLevel=Level-1 ;
END WHILE;
END;
CALL showTreeNodes(0);
执行完后会产生一个tmpLst表,nLevel 为节点深度,sCort 为排序字段。
当然 这是我在可视化客户端里创建的,如果你需要终端中创建 ,则需要使用delimiter 将分号换成其他符号
使用方法
SELECT concat(SPACE(B.nLevel*2),'+--',A.nodename) FROM `category` A,tmpLst B WHERE A.ID=B.ID ORDER BY B.sCort;
优点 : 层数的显示。并且可以按照树的遍历顺序得到结果。没有递归限制。
缺点 : MySQL中对临时表的限制,只能使用普通表,需做事后清理。