php - 根据mysql中的路径选择

Question

我有一列 id，一列 parent 和一列 path，它是具体化的路径。

看起来像

1  | \N | 1  
2  | 1  | 1/2  
3  | 2  | 1/2/3  
4  | 3  | 1/2/3/4  
5  | 3  | 1/2/3/5  
6  | 2  | 1/2/6  
7  | 6  | 1/2/6/7  
8  | 2  | 1/2/8  
9  | 1  | 1/9  
10 | 9  | 1/9/10  
11 | 10 | 1/9/10/11  
12 | 11 | 1/9/10/11/12  
13 | 11 | 1/9/10/11/13  
14 | 11 | 1/9/10/11/14  
15 | 14 | 1/9/10/11/14/15  
16 | 14 | 1/9/10/11/14/16  
17 | 14 | 1/9/10/11/14/17  
18 | 10 | 1/9/10/18  
19 | \N | 19  
20 | 19 | 19\20  
21 | 19 | 19\21

我需要根据这张表做一些查询。

我需要做的查询是

---

选择id 9的所有 child

SELECT * FROM `tester` WHERE 'path' LIKE '%/9/%';  

可以正常工作，直到您将 ID 替换为 1 或 19，因为开头没有 /。

SELECT * FROM `tester` WHERE 'path' LIKE '%1/%';

将选择数字以 1 结尾的所有行，因此，1、11、21、31、211 等

SELECT * FROM `tester` WHERE 'path' LIKE '1/%';

对于第 1 行或第 19 行都可以正常工作

所以 SELECT * FROMtesterWHERE 'path' LIKE '1/%' OR 'path' LIKE '%/1/%';
我能想出最好的办法吗？有什么建议吗？

---

选择 9 个 child 的直系 child ，但不选择子 child
为此 Select * fromtester where 'parent' = 9;将正常工作。

---

选择 9 的 child 的总数，x 层深度。

所以我想以一行 level1, level2, level3, ... levelx 或 x 行结束，代表不同的级别，

对于这个例子，我们假设 x 是 3此示例中的行将是 9, 8, 6(如果我们要求，第 4 层将是 3)

有什么想法吗？

编辑

#select count of children of specific node(5) down to a maximum of three levels, do no include the parent
SELECT COUNT(child.id) children, 
LENGTH(REPLACE(child.path, parent.path, '')) - LENGTH(REPLACE(REPLACE(child.path, parent.path, ''), '/', '')) AS LEVEL
FROM `tester` child JOIN `tester` parent ON child.path LIKE CONCAT(parent.path,'%') 
WHERE parent.id  =5 
GROUP BY LEVEL HAVING LEVEL <= 3 AND LEVEL > 0;

---

**选择 9 的 child 的 id 向下到 x 级别，级别相对于 9，

对于这个例子，我们将再次使用 3 作为 x。

我们正在寻找回来

10 | 1
11 | 2
18 | 2
12 | 3
13 | 3
14 | 3 

我再次完全不知道如何做到这一点。

编辑:

#select all information, and relative level from parent of children of specific node(5) down to a maximum of three levels, do no include the parent
SELECT child.*, 
LENGTH(REPLACE(child.path, parent.path, '')) - LENGTH(REPLACE(REPLACE(child.path, parent.path, ''), '/', '')) AS LEVEL
FROM `tester` child JOIN `tester` parent ON child.path LIKE CONCAT(parent.path,'%') 
WHERE parent.id  =9 
GROUP BY id HAVING LEVEL <= 3 AND LEVEL > 0;

Answer 1

请注意，这些解决方案基于字符串比较，未优化且无法使用索引。您应该考虑以不同方式规范化您的表格。 (参见 Managing Hierarchical Data in MySQL)

关于一些问题:

---

选择id为9的所有 child :

由于 Path 列不包含前导和尾部斜杠，您需要将它们连接到路径:

SELECT * 
FROM tester
WHERE CONCAT('/', path, '/') LIKE '%/9/%';

---

选择 9 个 child 的总数，x 层深:

我们需要按照路径中的斜杠数减去父路径中的斜杠数进行分组:

SELECT (LENGTH(c.Path) - LENGTH(REPLACE(c.Path, '/', '')))
    - (LENGTH(p.Path) - LENGTH(REPLACE(p.Path, '/', ''))) AS Level,
    COUNT(*)
FROM tester c
    JOIN tester p ON c.Parent = p.ID
WHERE CONCAT('/', path, '/') LIKE '%/9/%';
GROUP BY 1

为简单起见，我使用上面的查询来显示所有级别，如果您想限制 x 级别深度，请使用下面查询中的 WHERE 谓词。

---

选取9的 child id到x级，级别相对于9:

我们搜索 Path 列最多 x 层，同时考虑父级:

SELECT c.*
FROM tester c
    JOIN tester p ON c.Parent = p.ID
WHERE CONCAT(
    '/',
    SUBSTRING_INDEX(
        Path, 
        '/', 
        (LENGTH(p.Path) - LENGTH(REPLACE(p.Path, '/', ''))) + 4
    ),
'/') LIKE '%/9/%'

我们正在采取的步骤:

1. 我们需要找出父级有多深，我们可以通过计算父级路径中的斜杠来找到。 (LENGTH(p.Path) - LENGTH(REPLACE(p.Path, '/', '')))
2. 我们需要给这个数字加 1，因为带有 1 个斜线的路径是 2 层深。
3. 我们添加 x 个所需级别。
4. 抓取路径列直到级别总数，(使用 SUBSTRING_INDEX 函数)。
5. 添加开头和结尾的斜杠。
6. 在最后的字符串中搜索 9。