CTE recursivo MySQL

Resumo : neste tutorial, você aprenderá sobre o CTE recursivo do MySQL e como usá-lo para percorrer dados hierárquicos.

Observe que uma  expressão de tabela comum  (CTE) só está disponível no MySQL versão 8.0 ou posterior. Portanto, certifique-se de ter a versão correta do MySQL instalada para usar as instruções deste tutorial.

Introdução ao CTE recursivo MySQL

No MySQL, uma Common Table Expression (CTE) recursiva é um conjunto de resultados temporário nomeado que faz referência a si mesmo no membro recursivo, permitindo a passagem hierárquica ou iteração sobre os dados até que uma condição de encerramento especificada seja atendida.

O seguinte ilustra a sintaxe de um CTE recursivo:

WITH RECURSIVE cte_name AS (
    initial_query  -- anchor member
    UNION ALL
    recursive_query -- recursive member that references to the CTE name
)
SELECT * FROM cte_name;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Um CTE recursivo consiste em três partes principais:

• Uma consulta inicial que forma o conjunto de resultados base da estrutura CTE. A parte inicial da consulta é chamada de membro âncora.
• Uma parte de consulta recursiva é uma consulta que faz referência ao nome CTE, portanto, é chamada de membro recursivo. O membro recursivo é unido ao membro âncora por um operador UNION ALLou UNION DISTINCT.
• Uma condição de encerramento que garante que a recursão pare quando o membro recursivo não retornar nenhuma linha.

A ordem de execução de um CTE recursivo é a seguinte:

1. Primeiro, separe os membros em dois: membros âncora e recursivos.
2. Em seguida, execute o membro âncora para formar o conjunto de resultados base ( R0) e use esse conjunto de resultados base para a próxima iteração.
3. Em seguida, execute o membro recursivo com Riresultado definido como entrada e make Ri+1como saída.
4. Depois disso, repita o terceiro passo até que o membro recursivo retorne um conjunto de resultados vazio, ou seja, a condição de término seja atendida.
5. Por fim, combine os conjuntos de resultados de R0para Rnusando UNION ALLo operador.

Restrições de membros recursivos

O membro recursivo não deve conter as seguintes construções:

• Funções agregadas, por exemplo, MAX , MIN , SUM , AVG , COUNT , etc.
• Cláusula GROUP BY
• Cláusula ORDER BY
• Cláusula LIMIT
• DISTINTO

Observe que a restrição acima não se aplica ao membro âncora. Além disso, a restrição de uso DISTINCTsó se aplica quando você usa UNIONo operador. Se você usar o UNION DISTINCToperador, o DISTINCTé permitido.

Além disso, o membro recursivo pode referenciar o nome do CTE apenas uma vez em sua FROMcláusula e não em qualquer subconsulta .

Exemplo básico de CTE recursivo do MySQL

Veja o seguinte exemplo de CTE recursivo simples:

WITH RECURSIVE cte_count (n) 
AS (
      SELECT 1
      UNION ALL
      SELECT n + 1 
      FROM cte_count 
      WHERE n < 3
    )
SELECT n 
FROM cte_count;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Neste exemplo, a seguinte consulta:

SELECT 1Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

é o membro âncora que retorna 1 como o conjunto de resultados base.

A seguinte consulta

SELECT n + 1
FROM cte_count 
WHERE n < 3Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

é o membro recursivo porque faz referência ao nome do CTE que é cte_count.

A expressão n < 3 no membro recursivo é a condição de terminação. Quando n for igual a 3, o membro recursivo retorna um conjunto vazio que interromperá a recursão.

A imagem a seguir ilustra os elementos do CTE acima:

O CTE recursivo retorna a seguinte saída:

As etapas de execução do CTE recursivo são as seguintes:

• Primeiro, separe os membros âncora e recursivos.
• Em seguida, o membro âncora forma a linha inicial ( SELECT 1), portanto a primeira iteração produz 1 + 1 = 2 com n = 1.
• Então, a segunda iteração opera na saída da primeira iteração (2) e produz 2 + 1 = 3 com n = 2.
• Depois disso, antes da terceira operação ( n = 3), a condição de encerramento ( n < 3) é atendida e a consulta é interrompida.
• Finalmente, combine todos os conjuntos de resultados 1, 2 e 3 usando o UNION ALLoperador

Usando CTE recursivo MySQL para percorrer os dados hierárquicos

Primeiro, crie um novo banco de dados chamado mydb:

CREATE DATABASE IF NOT EXIST mydb;

Segundo, altere o banco de dados atual para mydb:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    manager_id INT
);

Terceiro, insira algumas linhas na employeestabela:

INSERT INTO employees VALUES
    (1, 'John Doe', NULL),         -- CEO, no manager
    (2, 'Jane Smith', 1),          -- Manager, reports to CEO
    (3, 'Bob Johnson', 2),         -- Employee, reports to Jane Smith
    (4, 'Alice Brown', 2),         -- Employee, reports to Jane Smith
    (5, 'Charlie Davis', 3);       -- Employee, reports to Bob Johnson
Linguagem de código:  PHP  ( php )

Por fim, percorra os dados hierárquicos na employeestabela usando um CTE recursivo:

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT
        employee_id,
        employee_name,
        manager_id,
        0 AS level
    FROM
        employees
    WHERE
        manager_id IS NULL -- Anchor member (root of the hierarchy)
        
    UNION ALL
    
    SELECT
        e.employee_id,
        e.employee_name,
        e.manager_id,
        eh.level + 1
    FROM
        employees e
    INNER JOIN
        EmployeeHierarchy eh ON e.manager_id = eh.employee_id -- Recursive member
)
-- Final query to select from the CTE
SELECT
    employee_id,
    employee_name,
    manager_id,
    level
FROM
    EmployeeHierarchy
ORDER BY
    level, employee_id;Linguagem de código:  PHP  ( php )

Saída:

+-------------+---------------+------------+-------+
| employee_id | employee_name | manager_id | level |
+-------------+---------------+------------+-------+
|           1 | John Doe      |       NULL |     0 |
|           2 | Jane Smith    |          1 |     1 |
|           3 | Bob Johnson   |          2 |     2 |
|           4 | Alice Brown   |          2 |     2 |
|           5 | Charlie Davis |          3 |     3 |
+-------------+---------------+------------+-------+
5 rows in set (0.01 sec)Linguagem de código:  JavaScript  ( javascript )

Como funciona.

• Defina o CTE com o nome EmployeeHierarchy.
• Defina um membro âncora que selecione funcionários que não possuem gerente ( manager_id IS NULL), começando pela raiz da hierarquia (CEO).
• Use um membro recursivo para unir a employeestabela ao CTE, desde que o manager_idna employeestabela corresponda ao employee_idno CTE, atravessando efetivamente a hierarquia.
• Selecione informações do CTE, incluindo o ID do funcionário, o nome, o ID do gerente e o nível na hierarquia na consulta final. E classifique o conjunto de resultados por nível e ID do funcionário.

Resumo

• Use o CTE recursivo do MySQL para percorrer dados hierárquicos.