MySQL 查询树结构方式- Starterknow

3. Oracle数据库的方式4. 程序代码递归的方式构建树5. 通过hashMap，只需要遍历一次

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));

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 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′,’总经理王X子’,’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’));此时查看查询出来的信息就是刘大瘸子下所有的员工信息了

以上为个人经验，希望能给大家一个参考，也希望大家多多支持共生网络。

原创文章，作者：starterknow，如若转载，请注明出处：https://www.starterknow.com/117889.html

MySQL 查询树结构方式

相关文章：

相关推荐

请登录

联系我们