반응형
마리아의 재귀적 부모 자식 문제DB
클라이언트가 부모 자식 관계가 있는 카탈로그로 데이터를 가져올 수 있는 경우가 몇 번 있었는데, 이러한 관계에 문제가 발생했습니다.다음을 방지할 방법을 찾아야 합니다.
- 개체 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;
주기가 감지되지 않으면 행이 없고 위반 경로가 있는 행을 반환합니다.여러분, 많은 조언 감사합니다.
제가 생각해낸 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>;
다음은 동일한 작업을 수행하지만 모든 테이블, ID 열, 상위 열 조합을 처리할 수 있을 정도로 일반적인 저장 프로시저입니다.
CREATE PROCEDURE `detect_cycle`(table_name varchar(64), id_column varchar(32), parent_id_column varchar(32), max_depth int)
BEGIN
declare id int default 0;
declare sql_query text default '';
declare where_clause text default '';
declare done bool default false;
declare id_cursor cursor for select root_id from __temp_ids;
declare continue handler for not found set done = true;
drop temporary table if exists __temp_ids;
create temporary table __temp_ids(root_id int not null primary key);
set sql_query = concat('
insert into __temp_ids
select
`',id_column,'`
from ',table_name);
prepare statement from sql_query;
execute statement;
drop temporary table if exists __temp_cycle;
create temporary table __temp_cycle (id int not null, parent_id int not null);
open id_cursor;
id_loop: loop
fetch from id_cursor into id;
if done then
leave id_loop;
end if;
set where_clause = concat('where `',id_column,'` = ',id);
set sql_query = concat('
insert into __temp_cycle
select
t.`',id_column,'`,
t.`',parent_id_column,'`
from
(
with recursive find_cycle as (
select
`',id_column,'`,
`',parent_id_column,'`,
0 depth
from
`',table_name,'`
',where_clause,'
union all
select
f.`',id_column,'`,
c.`',parent_id_column,'`,
f.depth + 1
from
`',table_name,'` c
inner join find_cycle f
ON f.`',parent_id_column,'` = c.`',id_column,'`
where
c.`',parent_id_column,'` <> c.`',id_column,'`
and f.`',parent_id_column,'` <> f.`',id_column,'`
and f.depth < ',max_depth,'
)
select
c.`',id_column,'`,
c.`',parent_id_column,'`
from find_cycle f
inner join `',table_name,'` c
on f.`',parent_id_column,'` = c.`',id_column,'`
order by depth desc
limit 1
) t
where t.`',parent_id_column,'` > 0');
prepare statement from sql_query;
execute statement;
end loop;
close id_cursor;
deallocate prepare statement;
select distinct
*
from __temp_cycle;
drop temporary table if exists __temp_ids;
drop temporary table if exists __temp_cycle;
END
용도:
call detect_cycle(table_name, id_column, parent_id_column, max_depth);
그러면 지정된 테이블 내의 모든 사이클 조건의 결과 집합이 반환됩니다.
사이클링 이벤트를 중단하기 위해 이를 파악한 것처럼 보이지만 사이클을 식별하는 방법을 찾고 있습니다.이 경우 다음을 사용하는 것을 고려합니다.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 구문을 쓴 지 오래돼서 구문이 좀 틀릴 수도 있지만, 이것이 기본적인 생각입니다.재귀가 사용한 경로를 캡처한 다음 현재 항목이 이미 경로에 있는지 확인합니다.
결과 트리의 깊이가 그리 깊지 않으면 재귀 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/
언급URL : https://stackoverflow.com/questions/71592214/recursive-parent-child-problem-in-mariadb
반응형
'codememo' 카테고리의 다른 글
| 키클록 - 영역 간에 공통 사용자 집합을 공유할 수 있습니까? (0) | 2023.07.02 |
|---|---|
| Oracle 자동 증분 (0) | 2023.07.02 |
| 변수 인수 목록을 사용하는 디버그 전용 함수는 어떻게 생성합니까?printf()와 같이() (0) | 2023.07.02 |
| SQL Server의 조건을 기준으로 카운트 (0) | 2023.07.02 |
| 목록의 제품 반환 (0) | 2023.07.02 |