Início / dba / Perguntas / 320367
Accepted
dakini
Asked: 2022-11-30 21:27:11 +0800 CST

Como posso lidar com ciclos/loops infinitos em CTEs recursivos no MySQL

  • 772

Ciclos em gráficos levam a loops infinitos em CTEs.

Lidar com eles no Postgres é simples.

Desde 8.0, o MySQL também permite CTEs. Como posso detectar ciclos e loops infinitos em MySQL CTEs?

O objetivo não é interromper a consulta após 1000 ou qualquer número de iterações, mas realmente tratá-la no código (por exemplo, coletando em um array a lista de nós visitados e tendo uma condição de desigualdade para evitar loops).

Ou existem opções "incorporadas" para lidar com isso, como cycle COLNAMEnos CTEs do Postgres?

Meu código atual se parece com isso

with recursive circle as (                                                               
select friend2, name2, 0 as depth from my_view1 where friend1 = 1
union
select m.friend2, m.name2, c.depth+1 from my_view1 m
inner join circle c on c.friend2 = m.friend1)
select * from circle where circle.depth < 2;

As tabelas subjacentes podem ser criadas com:

create table people (person_id integer primary key, name varchar(20) not null);

insert into people (person_id, name) values (1, 'tom'), (2, 'dick'), (3, 'harry'), (4, 'susan'), (5, 'mary'), (6, 'jill');

create table friends (friend1 integer references people (person_id), friend2 integer references people (person_id), primary key (friend1, friend2));

insert into friends (friend1, friend2) values (1,2), (2, 3), (3, 4), (5, 6);

insert into friends (friend1, friend2) values (2,1), (3,2), (4,3), (6,5);

create view my_view1 as select f.friend1, p.name as name1, f.friend2, p1.name as name2 from friends f join people p on p.person_id = f.friend1 join people p1 on p1.person_id = f.friend2 ;
mysql

1 respostas

  1. Best Answer

    Neste caso, UNION DISTINCT em CTE é suficiente para prevenir o ciclo:

    create table people (
  person_id integer primary key,
  name varchar(20) not null
  );
insert into people (person_id, name) values 
  (1, 'tom'), 
  (2, 'dick'),
  (3, 'harry'), 
  (4, 'susan'),
  (5, 'mary'),
  (6, 'jill');
TABLE people;
create table friends (
  friend1 integer,
  FOREIGN KEY (friend1) references people (person_id),
  friend2 integer,
  FOREIGN KEY (friend2) references people (person_id),
  primary key (friend1, friend2)
  );
insert into friends (friend1, friend2) values
  (1,2),
  (2, 3),
  (3, 4),
  (5, 6),
  (2, 1), 
  (3, 2),
  (4, 3),
  (6, 5);
TABLE friends;
    person_id nome
    1 Tom
    2 pau
    3 atormentar
    4 susan
    5 Mary
    6 Jill
    amigo1 amigo2
    2 1
    1 2
    3 2
    2 3
    4 3
    3 4
    6 5
    5 6 
    WITH RECURSIVE
cte AS (
  SELECT friend1, friend2
  FROM friends
--  WHERE friend1 = 1
  UNION DISTINCT
  SELECT cte.friend1, friends.friend2
  FROM cte
  JOIN friends ON cte.friend2 = friends.friend1
  )
SELECT DISTINCT t1.*, t2.*
FROM cte
JOIN people t1 ON t1.person_id = cte.friend1
JOIN people t2 ON t2.person_id = cte.friend2
WHERE cte.friend1 <> cte.friend2
ORDER BY 1,3;
    person_id nome person_id nome
    1 Tom 2 pau
    1 Tom 3 atormentar
    1 Tom 4 susan
    2 pau 1 Tom
    2 pau 3 atormentar
    2 pau 4 susan
    3 atormentar 1 Tom
    3 atormentar 2 pau
    3 atormentar 4 susan
    4 susan 1 Tom
    4 susan 2 pau
    4 susan 3 atormentar
    5 Mary 6 Jill
    6 Jill 5 Mary

    violino

    se você precisar dos dados de amizade para um usuário específico ou para uma lista de usuários, edite/descomente a condição na consulta base do CTE.

    Se você precisar usar esta consulta como uma exibição, adicione CREATE VIEW collect_friends ASno início.

    • 1

relate perguntas