MySQL查询树结构方式
目录
MySQL 查询树结构
1. 关于树结构
2. MySQL自定义函数的方式
2.1 创建测试数据
2.2 获取 某节点下所有子节点
2.3 获取 某节点的所有父节点
3. Oracle数据库的方式
4. 程序代码递归的方式构建树
5. 通过hashMap，只需要遍历一次
MySQL 查询带树状结构的信息
在Oracle中可以直接用下面的语法可以进行直接查询
但是在Mysql中是没有这个语法的
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));
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));
只需要使用start with connect by prior语句即可完成递归的树查询，详情请自己查阅相关资料。
4. 程序代码递归的方式构建树这里我就不给出完整代码了，递归的方式很简单，就是先查出所有树节点，然后通过一个TreeNode类中的add方法递归把所有子节点给加进来。核心代码如下：
public class TreeNodeDTO {
private String id;
private String parentId;
private String name;
private List<TreeNodeDTO> children = 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，只需要遍历一次
就可以完成树的生成：五星推荐
List<TreeNodeDTO> list = dbMapper.getNodeList();
ArrayList<TreeNodeDTO> rootNodes = new ArrayList<>();
Map<Integer, TreeNodeDTO> 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'));
此时查看查询出来的信息就是刘大瘸子下所有的员工信息了
以上为个人经验，希望能给大家一个参考，也希望大家多多支持软件开发网。
相关文章
Yolanda
2020-03-14
Olathe
2023-07-20
Talia
2023-07-20
Serafina
2023-07-20
Hazel
2023-07-20
Ebony
2023-07-20
Olga
2023-07-20
Elizabeth
2023-07-20
Ianthe
2023-07-20
Valora
2023-07-20
Phemia
2023-07-20
Tia
2023-07-20
Summer
2023-07-20
Fredrica
2023-07-20
Maha
2023-07-21
Edie
2023-07-21
Bonnie
2023-07-21
Ilona
2023-07-22
Xylona
2023-07-22