MySql sql递归循环

Question

我正在从页面列表构建导航菜单。表格是这样的:

Table name: pages

id | type | parent | name
-------------------------------
1,    1,     null,   root1
2,    1,     null,   root2
3,    2,     2,      home
4,    2,     3,      child
5,    2,     4,      sub_child
6,    3,     5,      sub_sub_child


type:
1 = root page / site
2 = page
3 = ...

我的问题是，我必须从任何页面找到根页面。我有一个 parent 列，它引用父页面，根页面除外。

我可以在表中有多个根页面，但每个页面只有一个父页面。

谁能帮我写一个递归查询？

我正在尝试使用此查询，但它不起作用:

with recursive pages (id, parent) as 
(
    select pages.id, 
    pages.parent, 
    from pages
    where pages.id = 4

union all
    select pages.id, 
    pages.parent,
    from pages
    inner join pages p on p.id = pages.parent
)
select id
from pages;

谢谢

Answer 1

我在数据库中处理树结构数据的 farovite 技巧是向表中添加一列 FullID 以避免复杂的(可能是递归的)SQL/存储过程。

FullID     id  parent   name
-----------------------------
1          1   null     root1
2          2   null     root2
2.3        3   2        home
2.3.4      4   3        child
2.3.4.5    5   4        sub_child
2.3.4.5.6  6   5        sub_sub_child

因此，要找到根页面 ID，只需通过 SQL 或您的应用程序语言提取 FullID 的第一部分。

如果使用SQL，你可以使用下面的SQL来获取root id。

-- MySQL dialect
select substring_index(FullID,'.',1) as RootID from table;

-- SQL Server dialect
select case charindex('.', FullID) when 0 then FullID else substring(FullID, 1, charindex('.', FullID)-1) end as RootID from table

删除节点及其子节点

DELETE table WHERE id=<CURRENT_NODE_ID> OR FullID LIKE '<CURREN_NODE_FULLID>.%'

移动节点及其子节点

-- change the parent of current node:
UPDATE table
SET parent=<NEW_PARENT_ID>
WHERE id=<CURRENT_NODE_ID>

-- update it's FullID and all children's FullID:
UPDATE table
SET FullID=REPLACE(FullID,<CURRENT_NODE_PARENT_FULLID>, <NEW_PARENT_FULLID>)
WHERE (id=<CURRENT_NODE_ID> OR FullID LIKE '<CURRENT_NODE_FULLID>.%')

注意

此技巧仅适用于有限的树级情况，或者如果树级太深，FullID 无法容纳长内容。