mysql 树形结构表 mysql 树形结构查询

树形结构的数据

---

此类结构的数据，通常需要表结构中含有id 、parentId等自关联字段，有时为了提高查询效率还可增加更多冗余字段，如index，index的值为所有父级目录的id字符串集合。
关于树结构数据的组装，常见的写法是在程序中通过递归的方式去构建出一颗完整的树，单纯通过sql的方式其实并不常用，下面分别给出两种方式的例子。

---

MySQL自定义函数的方式

什么是MySQL自定义函数：聚合函数，日期函数之类的都是MySQL的函数，此处我们定义的函数可同他们一样使用，不过只能在定义的数据库中使用，自定义函数和存储过程类似，不同的是，函数只会返回一个值，不允许返回一个结果集。

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, '广州市');

获取 某节点下所有子节点

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

---

 Oracle数据库的方式

只需要使用start with connect by prior语句即可完成递归的树查询，详情请自己查阅相关资料。