Curso de MariaDB / Consultar hierarquias e evitar ciclos com CTEs recursivas

Uma expressão de tabela comum recursiva, ou CTE recursiva, parte de um conjunto inicial e segue relações repetidamente para obter novas linhas. Uma consulta de hierarquia precisa de um ponto inicial, uma regra de parada, colunas de caminho com capacidade suficiente, tratamento de ciclos e ordenação final explícita.

Reconhecer quando a recursão é útil

Uma CTE comum dá nome a uma etapa da consulta. Uma CTE recursiva também usa esse nome dentro da própria definição: a parte inicial, ou âncora, produz as primeiras linhas e a parte recursiva busca as próximas a partir da etapa anterior. O exemplo reúne as duas com UNION ALL.

Árvores de categorias e organogramas podem ter profundidade variável. Para uma relação fixa de dois níveis, um JOIN pode ser suficiente. Em uma hierarquia grande, também é necessário localizar filhos pela chave do pai e definir um limite de profundidade; a prática de índices ajuda a examinar esse acesso. A referência oficial de CTEs recursivas explica o cálculo e os tipos definidos pela âncora.

Preparar uma árvore de seis categorias

Execute a preparação em appops_lab com uma conexão administrativa ou uma conta de estudos com permissões para criar o banco e as tabelas. Confira antes SELECT VERSION(), DATABASE(); e os bancos e tabelas existentes. Se appops_lab já estiver em uso na prática de Docker, preserve seu conteúdo; CREATE DATABASE IF NOT EXISTS não substitui essa revisão. Se category_tree existir, examine a prática anterior antes de repetir a criação.

A chave estrangeira verifica a existência do pai indicado; não demonstra que toda a hierarquia esteja livre de ciclos.

CREATE DATABASE IF NOT EXISTS appops_lab
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE appops_lab;
CREATE TABLE category_tree (
  category_id INT NOT NULL PRIMARY KEY,
  parent_id INT NULL,
  category_name VARCHAR(40) NOT NULL,
  INDEX ix_parent (parent_id),
  CONSTRAINT fk_category_parent FOREIGN KEY (parent_id)
    REFERENCES category_tree(category_id)
) ENGINE=InnoDB;
INSERT INTO category_tree VALUES
  (1, NULL, 'Catalog'), (2, 1, 'Books'), (3, 1, 'Tools'),
  (4, 2, 'SQL'), (5, 2, 'Python'), (6, 3, 'Editors');

Os nomes em inglês são valores literais do conjunto de dados: Catalog é o catálogo raiz e contém Books, livros, e Tools, ferramentas. Books contém SQL e Python; Tools contém Editors, editores. O índice de parent_id ajuda a localizar filhos. As seis linhas permitem estudar relações e resultados, sem afirmar melhorias gerais de velocidade.

Árvore do exemplo SQL: Catalog (1) na profundidade zero, Books (2) e Tools (3) na um, SQL (4), Python (5) e Editors (6) na dois

Executar a consulta completa com profundidade e caminhos

WITH RECURSIVE tree AS (
  SELECT category_id, parent_id, category_name, 0 AS depth,
         CAST(CONCAT('/', category_id, '/') AS CHAR(1000)) AS visited,
         CAST(category_name AS CHAR(1000)) AS name_path
  FROM category_tree WHERE category_id = 1
  UNION ALL
  SELECT c.category_id, c.parent_id, c.category_name, t.depth + 1,
         CONCAT(t.visited, c.category_id, '/'),
         CONCAT(t.name_path, ' > ', c.category_name)
  FROM category_tree c JOIN tree t ON c.parent_id = t.category_id
  WHERE t.depth < 8
    AND LOCATE(CONCAT('/', c.category_id, '/'), t.visited) = 0
)
SELECT category_id, parent_id, depth, name_path
FROM tree ORDER BY depth, category_id;

O primeiro SELECT retorna a categoria 1 na profundidade zero. O SELECT recursivo encontra as categorias cujo parent_id corresponde à categoria atual e incrementa a profundidade. O caminho visited envolve os IDs numéricos em barras, para que a busca pelo ID 1 não o confunda com o 11.

A âncora converte os dois caminhos para CHAR(1000). Se uma cadeia inicial curta determinar um tipo estreito demais, as concatenações posteriores podem exceder sua capacidade. Aqui usamos IDs inteiros, nomes curtos e profundidade máxima de oito. Recalcule a capacidade e os limites para seus caminhos; 1000 não é um tamanho seguro universal.

ID Pai Profundidade Caminho
1 NULL 0 Catalog
2 1 1 Catalog > Books
3 1 1 Catalog > Tools
4 2 2 Catalog > Books > SQL
5 2 2 Catalog > Books > Python
6 3 2 Catalog > Tools > Editors

O ORDER BY final por profundidade e ID garante essa apresentação. Não deduza a ordem de saída pela avaliação recursiva. Ordenar um caminho de texto produz ordem lexicográfica, que pode ser diferente da ordem numérica dos IDs.

Alterar uma condição e observar o alcance

Troque somente t.depth < 8 por t.depth < 1 e consulte novamente. O resultado inclui a âncora de profundidade zero e seus filhos imediatos: IDs 1, 2 e 3. Depois, restaure o limite para oito. A condição compara a profundidade do pai, portanto o pai na profundidade zero ainda pode gerar filhos na profundidade um.

Outra comparação independente consiste em alterar apenas a âncora para category_id = 2. Ela retorna a subárvore Books: IDs 2, 4 e 5, agora com Books na profundidade zero. Restaure a âncora para 1 antes do teste de ciclo abaixo.

Um resultado cortado pelo limite não é necessariamente a hierarquia completa. Determine primeiro se existem níveis mais profundos. A variável max_recursive_iterations do servidor impõe outro limite à repetição, mas não substitui uma regra de parada do negócio nem valida as relações entre pais.

Introduzir um ciclo e desfazê-lo

Na mesma conexão, execute estas instruções e depois repita a consulta recursiva completa acima. Outra conexão pode não enxergar a mudança de pai ainda não confirmada.

START TRANSACTION;
UPDATE category_tree SET parent_id = 4 WHERE category_id = 1;

Atribuir o pai 4 à categoria 1 cria o ciclo 1 → 2 → 4 → 1 ao seguir os filhos. Como a consulta começa explicitamente na categoria 1, o controle de IDs visitados exclui o retorno a uma linha já visitada e ainda termina com seis linhas. A linha raiz mostra temporariamente o pai 4. Restaure os dados logo após observar o resultado:

ROLLBACK;
SELECT category_id, parent_id FROM category_tree WHERE category_id = 1;

O pai da categoria 1 deve voltar a NULL. Uma âncora baseada em parent_id IS NULL pode omitir todo esse componente cíclico quando sua raiz sem pai deixa de existir. Evitar revisitas em um percurso não demonstra que o banco inteiro esteja livre de ciclos ou componentes inacessíveis. MariaDB também oferece a sintaxe CYCLE ... RESTRICT; aqui o caminho explícito permite observar a regra de percurso.

Verificar a hierarquia antes de usá-la em uma aplicação

  • Confirme que a âncora selecione as raízes previstas.
  • Diferencie pais ausentes, referências a si mesmo, ciclos e componentes inacessíveis.
  • Escolha uma profundidade máxima e uma capacidade de caminho compatível.
  • Examine o índice de busca de filhos e EXPLAIN e compare a contagem com as relações esperadas.

Para limpar, encerre qualquer transação aberta, confirme que esta tabela é descartável e execute DROP TABLE appops_lab.category_tree;. A operação remove estrutura e registros e não pode ser desfeita com ROLLBACK comum. Não substitua nesse comando o nome de uma tabela de negócio nem remova o banco compartilhado com outras práticas.