我有一个使用MySQL递归查询在一个表中查找二级和三级子级的案例...
我正在使用的数据库结构:
id name parent
1 A 0
2 B 0
3 C 0
4 D 1
5 E 1
6 F 2
7 G 2
8 H 3
9 I 3
10 J 4
11 K 4
当我筛选id=1的数据时，产生了我期望的结果。
id name parent
4 D 1
5 E 1
10 J 4
11 K 4
这是一幅插图。
我已经到处查找并阅读了这篇文章http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/，但没有找到我需要的结果...
如果有帮助，将不胜感激，谢谢！
如果您想获取特定父级的所有子级，则应尝试此操作。
select id,
name,
parent
from (select * from tablename
order by parent, id) tablename,
(select @pv := '1') initialisation
where find_in_set(parent, @pv) > 0
and @pv := concat(@pv, ',', id)
我认为在层次结构表中查找所有子节点的关键是首先找到父节点的路径，然后使用 FIND_IN_SET 来查看请求的节点是否在路径中。向上搜索比向下搜索更容易和更有效，因为表中已经存在到父节点的链接。
让我们以这样的层次结构开始：
1 Pets
├─ 2 Dogs
│ ├─ 3 Katie
├─ 4 Cats
│ ├─ 5 George
│ ├─ 6 Pete
│ ├─ 7 Alice
├─ 8 Other
│ ├─ 9 Rabbits
│ │ ├─ 10 Noah
│ │ ├─ 11 Teddy
│ │ ├─ 12 Bella
│ ├─ 13 Rats
│ │ ├─ 14 Henry
如果您想要查找类别为 Other 的所有子级（包括类别本身），则预期结果如下：
8,9,10,11,12,13,14
现在让我们来看一下Henry的层次路径。Henry（14）的父亲是Rats（13），它有一个父亲是Other（8），最终是Pets（1）。如果我们使用ID来为Henry制作路径，它看起来像这样：
1,8,13,14
FIND_IN_SET发挥作用的地方。使用FIND_IN_SET函数，您可以过滤掉一个变量在逗号分隔列表中出现的结果。在这个例子中，我们正在寻找所有属于Other（8）类别的孩子，我们可以简单地使用FIND_IN_SET(8, path)。FIND_IN_SET函数。+----+--------+---------+
| id | parent | name |
+----+--------+---------+
| 1 | NULL | Pets |
| 2 | 1 | Dogs |
| 3 | 2 | Katie |
| 4 | 1 | Cats |
| 5 | 4 | George |
| 6 | 4 | Pete |
| 7 | 4 | Alice |
| 8 | 1 | Other |
| 9 | 8 | Rabbits |
| 10 | 9 | Noah |
| 11 | 9 | Teddy |
| 12 | 9 | Bella |
| 13 | 8 | Rats |
| 14 | 13 | Henry |
+----+--------+---------+
id的父级前加上路径，直到达到NULL父级。DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `PATH`(IN `input` INT, OUT `output` VARCHAR(128))
BEGIN
DECLARE _id INT;
DECLARE _parent INT;
DECLARE _path VARCHAR(128);
SET `max_sp_recursion_depth` = 50;
SELECT `id`, `parent`
INTO _id, _parent
FROM `database`.`table`
WHERE `table`.`id` = `input`;
IF _parent IS NULL THEN
SET _path = _id;
ELSE
CALL `PATH`(_parent, _path);
SELECT CONCAT(_path, ',', _id) INTO _path;
END IF;
SELECT _path INTO `output`;
END $$
DELIMITER ;
我们需要在 SELECT 查询中使用这些结果，因此我们需要一个 FUNCTION 包装 PROCEDURE 的结果。
DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `GETPATH`(`input` INT) RETURNS VARCHAR(128)
BEGIN
CALL `PATH`(`input`, @path);
RETURN @path;
END $$
DELIMITER ;
现在我们可以在查询中使用该路径。在我的工作站上，对于有10000行的表格，只需要略过一秒钟。
SELECT `id`, `parent`, `name`, GETPATH(`id`) `path` FROM `database`.`table`;
示例输出：
+----+--------+---------+-----------+
| id | parent | name | path |
+----+--------+---------+-----------+
| 1 | NULL | Pets | 1 |
| 2 | 1 | Dogs | 1,2 |
| 3 | 2 | Katie | 1,2,3 |
| 4 | 1 | Cats | 1,4 |
| 5 | 4 | George | 1,4,5 |
| 6 | 4 | Pete | 1,4,6 |
| 7 | 4 | Alice | 1,4,7 |
| 8 | 1 | Other | 1,8 |
| 9 | 8 | Rabbits | 1,8,9 |
| 10 | 9 | Noah | 1,8,9,10 |
| 11 | 9 | Teddy | 1,8,9,11 |
| 12 | 9 | Bella | 1,8,9,12 |
| 13 | 8 | Rats | 1,8,13 |
| 14 | 13 | Henry | 1,8,13,14 |
+----+--------+---------+-----------+
要查找Other (8)的所有子元素（包括Other本身），我们可以使用相同的查询和FIND_IN_SET过滤器：
SELECT `id`, `parent`, `name`, GETPATH(`id`) `path` FROM `database`.`table` WHERE FIND_IN_SET(8, GETPATH(`id`));
最终，这就是结果。我们在过程中设置了50级的递归限制，但除此之外，深度没有限制。
+----+--------+---------+-----------+
| id | parent | name | path |
+----+--------+---------+-----------+
| 8 | 1 | Other | 1,8 |
| 9 | 8 | Rabbits | 1,8,9 |
| 10 | 9 | Noah | 1,8,9,10 |
| 11 | 9 | Teddy | 1,8,9,11 |
| 12 | 9 | Bella | 1,8,9,12 |
| 13 | 8 | Rats | 1,8,13 |
| 14 | 13 | Henry | 1,8,13,14 |
+----+--------+---------+-----------+
7 rows in set (0,01 sec)
GROUP_CONCAT ，像这样：SELECT GROUP_CONCAT(`id`) `children` FROM `database`.`table` WHERE FIND_IN_SET(8, GETPATH(`id`));
+--------------------+
| children |
+--------------------+
| 8,9,10,11,12,13,14 |
+--------------------+
1 row in set (0,00 sec)
尝试这个，更简单易懂。
(但仅支持一级层次结构)
SET @pv = 1;
select * from tablename
where FIND_IN_SET(parentrecordID,@pv) and !isnull(@pv:= concat(@pv, ',', id));
我尝试过这个
select id from (select * from roles order by parent_role, id) roles,(select @pv := '1') initialisation
where find_in_set(parent_role, @pv) > 0
and @pv := concat(@pv, ',', id)
DROP PROCEDURE IF EXISTS store_procedure_name;
CREATE PROCEDURE `store_procedure_name`(IN cat_id INT)
BEGIN
declare loopId Int;
SET max_sp_recursion_depth = 255;
-- If the value of the category that comes as a parameter is not in the table as parent_id, no further action is required
IF(select count(id) from category_table where parent_id=cat_id) > 0 THEN
-- create temporary table
CREATE TEMPORARY TABLE IF NOT EXISTS temp_category_table (
`id` smallint(5) unsigned,
`status` tinyint(3)
) ENGINE=InnoDB ;
-- First, save the corresponding value in the temporary table.
INSERT INTO temp_category_table
(id, status)
VALUES (cat_id, 0);
-- continue loop as long as the appropriate record exists in the temporary table
WHILE (select count(id) from temp_category_table where status=0) > 0 DO
-- in this section, a record with a status of 0 will be extracted from the temporary table and assigned to the variable loopId
set loopId = (select id from temp_category_table where status=0 limit 1);
INSERT INTO temp_category_table
(id, status)
(select id, 0 from category_table where parent_id=loopId);
update temp_category_table set status=1 where id=loopId;
CALL store_procedure_name((select id from temp_category_table where status=0 limit 1));
END WHILE;
(select DISTINCT(id) from temp_category_table order by id );
END IF;
END;
试试这个，速度更快
SELECT *
FROM table AS T1
INNER JOIN (SELECT id FROM table WHERE parent = 1) AS T2
ON T2.id = T1.parent OR T1.parent = 1
GROUP BY T1.id
SELECT *
FROM TABLENAME
WHERE PARENT = 1
UNION
SELECT *
FROM TABLENAME
WHERE PARENT IN
(SELECT ID FROM TABLENAME WHERE PARENT = 1)