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.
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.





