本篇文章小编给大家分享一下MySQL查询树结构方式代码示例,文章代码介绍的很详细,小编觉得挺不错的,现在分享给大家供大家参考,有需要的小伙伴们可以来看看。
1. 关于树结构
此类结构的数据,通常需要表结构中含有id 、parentId等自关联字段,有时为了提高查询效率还可增加更多冗余字段,如index,index的值为所有父级目录的id字符串集合。
关于树结构数据的组装,常见的写法是在程序中通过递归的方式去构建出一颗完整的树,单纯通过sql的方式其实并不常用,下面分别给出两种方式的例子。
2. MySQL自定义函数的方式
什么是MySQL自定义函数:聚合函数,日期函数之类的都是MySQL的函数,此处我们定义的函数可同他们一样使用,不过只能在定义的数据库中使用,自定义函数和存储过程类似,不同的是,函数只会返回一个值,不允许返回一个结果集。
2.1 创建测试数据
CREATE TABLE `tree` ( `id` bigint(11) NOT NULL, `pid` bigint(11) NULL DEFAULT NULL, `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; INSERT INTO `tree` VALUES (1, 0, '中国'); INSERT INTO `tree` VALUES (2, 1, '四川省'); INSERT INTO `tree` VALUES (3, 2, '成都市'); INSERT INTO `tree` VALUES (4, 3, '武侯区'); INSERT INTO `tree` VALUES (5, 4, '红牌楼'); INSERT INTO `tree` VALUES (6, 1, '广东省'); INSERT INTO `tree` VALUES (7, 1, '浙江省'); INSERT INTO `tree` VALUES (8, 6, '广州市');
2.2 获取 某节点下所有子节点
CREATE FUNCTION `GET_CHILD_NODE`(rootId varchar(100)) RETURNS varchar(2000) BEGIN DECLARE str varchar(2000); DECLARE cid varchar(100); SET str = '$'; SET cid = rootId; WHILE cid is not null DO SET str = concat(str, ',', cid); SELECT group_concat(id) INTO cid FROM tree where FIND_IN_SET(pid, cid); END WHILE; RETURN str; END
调用自定义函数
select * from tree where FIND_IN_SET(id, GET_CHILD_NODE(2));
2.3 获取 某节点的所有父节点
CREATE FUNCTION `GET_PARENT_NODE`(rootId varchar(100)) RETURNS varchar(1000) BEGIN DECLARE fid varchar(100) default ''; DECLARE str varchar(1000) default rootId; WHILE rootId is not null do SET fid =(SELECT pid FROM tree WHERE id = rootId); IF fid is not null THEN SET str = concat(str, ',', fid); SET rootId = fid; ELSE SET rootId = fid; END IF; END WHILE; return str; END
调用自定义函数
select * from tree where FIND_IN_SET(id, GET_PARENT_NODE(5));
3. Oracle数据库的方式
只需要使用start with connect by prior语句即可完成递归的树查询,详情请自己查阅相关资料。
4. 程序代码递归的方式构建树
这里我就不给出完整代码了,递归的方式很简单,就是先查出所有树节点,然后通过一个TreeNode类中的add方法递归把所有子节点给加进来。核心代码如下:
public class TreeNodeDTO { private String id; private String parentId; private String name; private Listchildren = new ArrayList<>(); public void add(TreeNodeDTO node) { if ("0".equals(node.parentId)) { this.children.add(node); } else if (node.parentId.equals(this.id)) { this.children.add(node); } else { //递归调用add()添加子节点 for (TreeNodeDTO tmp_node : children) { tmp_node.add(node); } } } }
5. 通过hashMap,只需要遍历一次
就可以完成树的生成:五星推荐
Listlist = dbMapper.getNodeList(); ArrayList rootNodes = new ArrayList<>(); Map map = new HashMap<>(); for (TreeNodeDTO node :list) { map.put(node.getId(), node); Integer parentId = node.getParentId(); // 判断是否有父节点 (没有父节点本身就是个父菜单) if (parentId.equals('0')){ rootNodes.add(node); // 找出不是父级菜单的且集合中包括其父菜单ID } else if (map.containsKey(parentId)){ map.get(parentId).getChildren().add(node); } }
MySQL 查询带树状结构的信息
在Oracle中有函数应用直接能够查询出树状的树状结构信息,例如有下面树状结构的组织成员架构,那么如果我们想查其中一个节点下的所有节点信息
在Oracle中可以直接用下面的语法可以进行直接查询
START WITH CONNECT BY PRIOR
但是在Mysql中是没有这个语法的
而如果你也是想要查询这样的数据结构信息该怎么做呢?我们可以自定义函数。我们将上面的信息初始化信息进数据库中。首先先创建一张表用于存储这些信息,ID为存储自身的ID信息,PARENT_ID存储父ID信息
CREATE TABLE `company_inf` ( `ID` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `NAME` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `PARENT_ID` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL )
然后将图中的信息初始化表中
INSERT INTO company_inf VALUES ('1','总经理王大麻子','1'); INSERT INTO company_inf VALUES ('2','研发部经理刘大瘸子','1'); INSERT INTO company_inf VALUES ('3','销售部经理马二愣子','1'); INSERT INTO company_inf VALUES ('4','财务部经理赵三驼子','1'); INSERT INTO company_inf VALUES ('5','秘书员工J','1'); INSERT INTO company_inf VALUES ('6','研发一组组长吴大棒槌','2'); INSERT INTO company_inf VALUES ('7','研发二组组长郑老六','2'); INSERT INTO company_inf VALUES ('8','销售人员G','3'); INSERT INTO company_inf VALUES ('9','销售人员H','3'); INSERT INTO company_inf VALUES ('10','财务人员I','4'); INSERT INTO company_inf VALUES ('11','开发人员A','6'); INSERT INTO company_inf VALUES ('12','开发人员B','6'); INSERT INTO company_inf VALUES ('13','开发人员C','6'); INSERT INTO company_inf VALUES ('14','开发人员D','7'); INSERT INTO company_inf VALUES ('15','开发人员E','7'); INSERT INTO company_inf VALUES ('16','开发人员F','7');
例如我们想要查询研发部门经理刘大瘸子下的所有员工,在Oracle中我们可以这样写
SELECT * FROM T_PORTAL_AUTHORITY START WITH ID='1' CONNECT BY PRIOR ID = PARENT_ID
而在Mysql中我们需要下面这样自定义函数
CREATE FUNCTION getChild(parentId VARCHAR(1000)) RETURNS VARCHAR(1000) BEGIN DECLARE oTemp VARCHAR(1000); DECLARE oTempChild VARCHAR(1000); SET oTemp = ''; SET oTempChild =parentId; WHILE oTempChild is not null DO IF oTemp != '' THEN SET oTemp = concat(oTemp,',',oTempChild); ELSE SET oTemp = oTempChild; END IF; SELECT group_concat(ID) INTO oTempChild FROM company_inf where parentId<>ID and FIND_IN_SET(parent_id,oTempChild)>0; END WHILE; RETURN oTemp; END
然后这样查询即可
SELECT * FROM company_inf WHERE FIND_IN_SET(ID,getChild('2'));
此时查看查询出来的信息就是刘大瘸子下所有的员工信息了