时间戳

首页 > 解决方案 > 递归sql查询中的无限循环

mysql - 递归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)

当你在列表中都是 4 lagr_id 时，我的 sql 代码挂断了。只有服务器重启有帮助:(有人对如何解决这个问题有任何想法吗？如何避免我的 sql 代码中的无限循环？MariaDB 版本 10.5.12

标签： mysqlmariadbinfinite-looprecursive-cte

解决方案

这不是一个无限循环，但您正在耗尽一些资源。

您可以通过以下方式改进查询：

  • 我在子查询中添加DISTINCT子句edges以避免重复行。
  • 我在子查询中添加CASE语句cte以避免在visited列中设置两次相同的值（当 lagr_id1 = lagr_id2 时）。
  • 我替换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,2158,2157'与'2154,2156,2157,2158'相同，因为组合的顺序不是在结果中很重要，因此查询应该丢弃其中之一。

推荐阅读