我遇到過幾次這樣的情況，客戶能夠將資料匯入具有父子關系的目錄中，但我遇到了上述關系的問題。我需要找到一種方法來防止以下情況：
- 物件 1 有物件 2 的子物件
- 物件 2 具有物件 3 的子物件
- 物件 3 具有物件 1 的子物件
這會使服務器陷入無限遞回回圈并最終使其陷入困境。我似乎無法將我的頭腦圍繞在一個可以用來檢測這種遞回瘋狂的 SQL 查詢上。這個問題很普遍，我需要找到一些解決方案。我已經嘗試使用 CTE、嵌套選擇/子選擇進行查詢，但似乎無法撰寫一個可以解決此問題的方法。任何幫助將不勝感激。
with recursive parents as (
select
s.id,
s.parent_id,
1 as depth
from categories s
where s.id = <passed in id>
union all
select
t.id,
t.parent_id,
c.depth 1 as depth
from categories t
inner join parents c
on t.id = c.parent_id
where t.id <> t.parent_id)
select distinct parent_id from parents where parent_id <> 0 order by depth desc
這就是我最終想出的“檢測”回圈條件
with recursive find_cycle as (
select
categories_id,
parent_id,
0 depth
from
categories
where categories_id = <passed in id>
union all
select
f.categories_id,
c.parent_id,
f.depth 1
from
categories c
inner join find_cycle f
ON f.parent_id = c.categories_id
where c.parent_id <> c.categories_id
and f.parent_id <> f.categories_id
)
select
f.parent_id as categories_id,
c.parent_id
from find_cycle f
inner join categories c
on f.parent_id = c.categories_id
where exists (
select
1
from find_cycle f
inner join categories c
on f.parent_id = c.categories_id
where f.parent_id = <passed in id>)
order by depth desc;
It will return rows with the offending path and no rows if no cycle detected. Thanks for all the tips folks.
uj5u.com熱心網友回復：
看起來您已經想出辦法來阻止自行車比賽，但正在尋找識別周期的方法。在這種情況下，請考慮使用path：
with recursive parents as (
select
s.id,
s.parent_id,
1 as depth,
CONCAT(s.id,'>',s.parent_id) as path,
NULL as cycle_detection
from categories s
where s.id = <passed in id>
union all
select
t.id,
t.parent_id,
c.depth 1 as depth,
CONCAT(c.path, '>', t.parent_id),
CASE WHEN c.path LIKE CONCAT('%',t.parent_id,'>%') THEN 'cycle' END
from categories t
inner join parents c
on t.id = c.parent_id
where t.id <> t.parent_id)
select distinct parent_id, cycle_detection from parents where parent_id <> 0 order by depth desc
我可能有點偏離我的語法，因為自從我撰寫 mysql/mariadb 語法以來，它已經永遠存在了，但這是基本思想。捕獲遞回所采用的路徑，然后查看您當前的專案是否已經在路徑中。
uj5u.com熱心網友回復：
如果生成的樹的深度不是非常深，那么您可以通過存盤遞回 CTE 正在行走的面包屑來檢測回圈。了解面包屑，您可以輕松檢測周期。
例如：
with recursive
n as (
select id, parent_id, concat('/', id, '/') as path
from categories where id = 2
union all
select c.id, c.parent_id, concat(n.path, c.id, '/')
from n
join categories c on c.parent_id = n.id
where n.path not like concat('%/', c.id, '/%') -- cycle pruning here!
)
select * from n;
結果：
id parent_id path
--- ---------- -------
2 1 /2/
3 2 /2/3/
1 3 /2/3/1/
請參閱DB Fiddle上的運行示例。
uj5u.com熱心網友回復：
這是我想出的 MariaDB 函式，如果沒有回圈，則回傳 0，如果傳入函式的 id 有回圈，則回傳 1。
create function `detect_cycle`(id int, max_depth int) RETURNS tinyint(1)
begin
declare cycle_exists int default 0;
select (case when count(*) = 1 then 0 else 1 end) into cycle_exists
from
(
with recursive find_cycle as (
select
categories_id,
parent_id,
0 depth
from
categories
where categories_id = id
union all
select
f.categories_id,
c.parent_id,
f.depth 1
from
categories c
inner join find_cycle f
ON f.parent_id = c.categories_id
where
c.parent_id <> c.categories_id
and f.parent_id <> f.categories_id
and f.depth < max_depth
)
select
c.parent_id
from find_cycle f
inner join categories c
on f.parent_id = c.categories_id
order by depth desc
limit 1
) __temp
where parent_id = 0;
return cycle_exists;
end;
然后可以通過執行來呼叫它
select categories_id, detect_cycle(categories_id, 5) as cycle_exists
from categories
where categories_id = <whatever id you want to check for a cycle condition>;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/449670.html