재귀 SQL 쿼리의 무한 루프

재귀 SQL 쿼리의 무한 루프

누군가 내 문제를 어떻게 해결해야 할지 조언해 줄지도 몰라.왜 이런 일이 일어나는지, 어떻게 해결해야 할지 모르겠어요.내 생각에 내 SQL 코드가 작동하지 않는 이유는 무한 루프 상태가 되기 때문이다.테이블이 있습니다.

CREATE TABLE `c_logistics_tran_group3` (
  `ltrgr_id` int(10) UNSIGNED NOT NULL,
  `ltrgr_lagr_id` int(10) UNSIGNED NOT NULL,
  `ltrgr_ltran_id` int(10) UNSIGNED NOT NULL,
  `ltrgr_created` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `c_logistics_tran_group3`
  ADD PRIMARY KEY (`ltrgr_id`),
  ADD UNIQUE KEY `ltrgr_lagr_id` (`ltrgr_lagr_id`,`ltrgr_ltran_id`),
  ADD KEY `c_logistics_tran_group3_ibfk_2` (`ltrgr_ltran_id`);

ALTER TABLE `c_logistics_tran_group3`
  MODIFY `ltrgr_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

및 데이터:

INSERT INTO `c_logistics_tran_group3` 
(`ltrgr_id`, `ltrgr_lagr_id`, `ltrgr_ltran_id`, `ltrgr_created`) 
VALUES
(2373, 2154, 2312, '2021-09-09 07:54:55'),
(2378, 2154, 2314, '2021-09-09 08:05:25'),
(2382, 2154, 2318, '2021-09-09 10:37:37'),
(2450, 2154, 2386, '2021-09-17 11:44:58'),
(2375, 2156, 2312, '2021-09-09 07:57:14'),
(2380, 2156, 2316, '2021-09-09 10:25:01'),
(2381, 2156, 2317, '2021-09-09 10:37:07'),
(2451, 2156, 2387, '2021-09-17 11:45:37'),
(2376, 2157, 2312, '2021-09-09 08:03:10'),
(2387, 2157, 2323, '2021-09-10 10:36:15'),
(2388, 2157, 2324, '2021-09-10 10:42:59'),
(2449, 2157, 2385, '2021-09-17 11:41:36'),
(2377, 2158, 2312, '2021-09-09 08:04:35');
COMMIT;

내 SQL 코드:

with
  recursive 
      edges as (
          select t1.ltrgr_lagr_id as lagr_id1, t2.ltrgr_lagr_id as lagr_id2
          from c_logistics_tran_group3 t1 
          inner join c_logistics_tran_group3 t2 on t2.ltrgr_ltran_id = t1.ltrgr_ltran_id
          where 1 = 1
          and t1.ltrgr_lagr_id in(2154, 2156, 2157, 2158) 
          and t2.ltrgr_lagr_id in(2154, 2156, 2157, 2158)
      ),
      cte as (
          select lagr_id1, lagr_id2, concat(lagr_id1, ',', lagr_id2) as visited
          from edges
          union all
          select c.lagr_id1, e.lagr_id2, concat(c.visited, ',', e.lagr_id2)
          from cte c
          inner join edges e on e.lagr_id1 = c.lagr_id2
          where not find_in_set(e.lagr_id2, c.visited)
      )
select * from cte;

이 SQL 코드는 여기서 설명하는 작업을 수행합니다. 값으로 연결된 키를 선택하십시오.

lagr_id를 목록에서 삭제하면 모든 것이 정상적으로 작동합니다.예:

          and t1.ltrgr_lagr_id in(2154, 2156, 2157) 
          and t2.ltrgr_lagr_id in(2154, 2156, 2157)

목록에 lagr_id가 4개 있으면 sql 코드가 끊깁니다.서버의 재기동만이 도움이 됩니다. (이 문제를 해결하는 방법을 알고 있는 사람이 있습니까?내 SQL 코드에서 무한 루프를 피하는 방법?MariaDB 버전 10.5.12

무한 루프는 아니지만 리소스가 부족합니다.

다음과 같이 쿼리를 개선할 수 있습니다.

• 덧붙이다DISTINCT절을 붙이다edges하위 쿼리를 사용하여 행 중복을 방지합니다.
• 덧붙이다CASE에 있어서의 성명.cte(lagr_id1 = lagr_id2일 때) 같은 값의 2배를 설정하는 것을 피하기 위한 서브쿼리visited기둥.
• 교환하다UNION ALL타고UNION행의 중복을 방지합니다.

WITH
  RECURSIVE 
      edges AS (
          SELECT DISTINCT t1.ltrgr_lagr_id AS lagr_id1, t2.ltrgr_lagr_id AS lagr_id2
          FROM c_logistics_tran_group3 t1 
          INNER JOIN c_logistics_tran_group3 t2 ON t2.ltrgr_ltran_id = t1.ltrgr_ltran_id
          WHERE t1.ltrgr_lagr_id in(2154, 2156, 2157, 2158) 
                AND t2.ltrgr_lagr_id in(2154, 2156, 2157, 2158)
      ),

      cte AS (
          SELECT lagr_id1, lagr_id2, CASE WHEN lagr_id1 = lagr_id2 THEN lagr_id1 ELSE concat(lagr_id1, ',', lagr_id2) END AS visited
          FROM edges
          
          UNION
          
          SELECT c.lagr_id1, e.lagr_id2, concat(c.visited, ',', e.lagr_id2)
          FROM cte c
          INNER JOIN edges e ON e.lagr_id1 = c.lagr_id2
          WHERE NOT find_in_set(e.lagr_id2, c.visited)
      )

SELECT * FROM cte;

행의 수는 상당히 줄었지만, 예를 들어 제 질의는 개선될 수 있다고 생각합니다.visited값 '2154,2156,2156,2157,2158'은 '2154,2166,2157,2158'과 동일합니다.

언급URL : https://stackoverflow.com/questions/69287763/infinity-loop-in-recursive-sql-query