Pular para o conteúdo principal

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →
Uma CTE comum lê tabelas. Uma CTE recursiva lê a si mesma, de modo que cada rodada de linhas produz a seguinte. É assim que o SQL percorre uma hierarquia ou gera uma série, coisas que um SELECT simples não consegue fazer. Abra o cliente com mysql -u root -p sakila antes de começar.

Como uma CTE recursiva funciona

Substitua CTE_NAME por um nome para o resultado, COLUMN_NAME pela coluna que ela produz, SEED_VALUE pelo valor inicial, NEXT_EXPRESSION pelo que gera a próxima linha a partir da anterior, e STOP_CONDITION pelo teste que em algum momento falha. Uma CTE recursiva tem 2 tipos de parte, unidas por UNION ALL:
  • Uma semente, também chamada de membro âncora, que não menciona a CTE em lugar nenhum e produz as linhas iniciais.
  • Um membro recursivo, que seleciona a partir da própria CTE.
A maioria tem exatamente 1 de cada, que é a forma mostrada abaixo. O MySQL aceita mais de 1 de qualquer uma delas, e aceita UNION no lugar de UNION ALL, o que desduplica cada rodada. Nenhum dos dois casos é comum, e vale a pena reconhecer ambos quando você os encontrar. O servidor executa a semente, depois executa a parte recursiva sobre o que a rodada anterior produziu, repetidamente, até que uma rodada não produza nenhuma linha. O WHERE na parte recursiva é o que faz isso acontecer em algum momento. A palavra-chave é WITH RECURSIVE, e ela vai no WITH, não na CTE.

Contando até 5

A semente gera a linha 1. A parte recursiva pega essa linha e gera 2, depois pega 2 e gera 3, e para quando chega a 5, porque WHERE n < 5 não produz a próxima linha. Uma série como essa é mais útil do que parece. Juntada a dados reais, ela dá a você uma linha para cada dia de um intervalo, incluindo os dias em que nada aconteceu, o que nenhuma consulta sobre os dados sozinhos consegue produzir.

Omitindo a condição de parada

Retire o WHERE e a parte recursiva sempre produz mais uma linha.
O servidor a interrompe em vez de executar para sempre. O limite é uma configuração:
A contagem na mensagem é 1 a mais do que a configuração, porque a rodada que ultrapassa o limite é contada antes de o servidor parar. Leia ERROR 3636 primeiro como uma condição de parada ausente ou errada. Aumentar o limite com SET SESSION cte_max_recursion_depth = 5000; é certo apenas quando você realmente precisa de mais rodadas do que o padrão permite. É errado quando a consulta nunca teria parado, porque ela então demora mais para falhar e falha mesmo assim. A configuração é uma variável, então o 1.000 acima é o valor deste servidor, e não uma propriedade fixa do MySQL.

Resumo

  • Escreva WITH RECURSIVE quando uma CTE lê a si mesma.
  • Dê a ela uma parte semente e um membro recursivo, unidos por UNION ALL.
  • Coloque a condição de parada no WHERE da parte recursiva.
  • Leia ERROR 3636 como uma recursão descontrolada antes de recorrer à configuração.

Veja também