Resumo : neste tutorial, você aprenderá como usar o modelo de lista de adjacências para gerenciar dados hierárquicos no MySQL.
Introdução ao modelo de lista de adjacências
Os dados hierárquicos estão em toda parte, aparecendo em diversas formas, como categorias de blogs, hierarquias de produtos ou estruturas organizacionais.
O gerenciamento de dados hierárquicos no MySQL pode ser abordado de diversas maneiras, e o modelo de lista de adjacências é frequentemente considerado a solução mais simples. Devido à sua simplicidade, o modelo de lista de adjacências é uma escolha altamente preferida entre desenvolvedores e administradores de banco de dados.
No modelo de lista de adjacências, cada nó possui um ponteiro para seu pai, e o nó superior não possui pai. Aqui estão alguns exemplos de categorias de produtos eletrônicos:
Antes de mergulhar no modelo de lista de adjacências, é essencial familiarizar-se com os seguintes termos-chave:
Electronics
é um nó superior ou nó raiz.Laptops, Cameras & photo, Phones & Accessories
nós são os filhos doElectronics
nó. E vice-versa, o nó eletrônico é o pai dosLaptops, Cameras & photo, Phones & Accessories
nós.- Os nós folha são os nós que não têm filhos, por exemplo,
Laptops
,PC
,Android
,iOS
, etc., enquanto os nós não folha são aqueles que têm pelo menos um filho. - Os filhos e netos de um nó são chamados de descendentes. E os pais, avós, etc. de um nó também são conhecidos como ancestrais.
Para modelar esta árvore de categorias, crie uma tabela chamada category
com três colunas: id
, title
, e parent_id
como segue:
CREATE TABLE category (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
title varchar(255) NOT NULL,
parent_id int(10) unsigned DEFAULT NULL,
PRIMARY KEY (id),
FOREIGN KEY (parent_id) REFERENCES category (id) ON DELETE CASCADE ON UPDATE CASCADE
);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Cada linha de uma tabela representa um nó na árvore identificado pela id
coluna. A parent_id
coluna serve como chave estrangeira para a id
coluna da mesma category
tabela, atuando como um ponteiro.
Inserindo dados
O nó raiz da árvore não tem pai, portanto, parent_id
é definido como NULL
. Os outros nós devem ter um e apenas um pai.
Para inserir um nó raiz, defina parent_id
como NULL
a seguir:
INSERT INTO category(title, parent_id)
VALUES
('Electronics', NULL);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Para inserir um nó não raiz, você só precisa defini-lo parent_id
como o id de seu nó pai.
Por exemplo, os nós parent_id
of Laptop & PC
e são definidos como 1:Cameras & Photos
Phone & Accessories
INSERT INTO category(title, parent_id)
VALUES
('Laptops & PC', 1);
INSERT INTO category(title, parent_id)
VALUES
('Laptops', 2);
INSERT INTO category(title, parent_id)
VALUES
('PC', 2);
INSERT INTO category(title, parent_id)
VALUES
('Cameras & photo', 1);
INSERT INTO category(title, parent_id)
VALUES
('Camera', 5);
INSERT INTO category(title, parent_id)
VALUES
('Phones & Accessories', 1);
INSERT INTO category(title, parent_id)
VALUES
('Smartphones', 7);
INSERT INTO category(title, parent_id)
VALUES
('Android', 8);
INSERT INTO category(title, parent_id)
VALUES
('iOS', 8);
INSERT INTO category(title, parent_id)
VALUES
('Other Smartphones', 8);
INSERT INTO category(title, parent_id)
VALUES
('Batteries', 7);
INSERT INTO category(title, parent_id)
VALUES
('Headsets', 7);
INSERT INTO category(title, parent_id)
VALUES
('Screen Protectors', 7);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Encontrando o nó raiz
O nó raiz é o nó que não possui pai. Em outras palavras, parent_id
é NULL
:
SELECT
id, title
FROM
category
WHERE
parent_id IS NULL;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Encontrando os filhos imediatos de um nó
A consulta a seguir obtém os filhos imediatos do nó raiz:
SELECT
id, title
FROM
category
WHERE
parent_id = 1;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Encontrando os nós folha
Os nós folha são os nós que não têm filhos.
SELECT
c1.id, c1.title
FROM
category c1
LEFT JOIN
category c2 ON c2.parent_id = c1.id
WHERE
c2.id IS NULL;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Consultando a árvore inteira
A seguinte expressão de tabela comum recursiva (CTE) recupera toda a árvore de categorias. Observe que o recurso CTE está disponível desde o MySQL 8.0
WITH RECURSIVE category_path (id, title, path) AS
(
SELECT id, title, title as path
FROM category
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.title, CONCAT(cp.path, ' > ', c.title)
FROM category_path AS cp JOIN category AS c
ON cp.id = c.parent_id
)
SELECT * FROM category_path
ORDER BY path;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Consultando uma subárvore
A consulta a seguir obtém a subárvore cuja Phone & Accessories
é id
7.
WITH RECURSIVE category_path (id, title, path) AS
(
SELECT id, title, title as path
FROM category
WHERE parent_id = 7
UNION ALL
SELECT c.id, c.title, CONCAT(cp.path, ' > ', c.title)
FROM category_path AS cp JOIN category AS c
ON cp.id = c.parent_id
)
SELECT * FROM category_path
ORDER BY path;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Consultando um único caminho
Para consultar um único caminho de baixo para cima, por exemplo, de iOS
para Electronics
, você usa a seguinte instrução:
WITH RECURSIVE category_path (id, title, parent_id) AS
(
SELECT id, title, parent_id
FROM category
WHERE id = 10 -- child node
UNION ALL
SELECT c.id, c.title, c.parent_id
FROM category_path AS cp JOIN category AS c
ON cp.parent_id = c.id
)
SELECT * FROM category_path;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Calculando o nível de cada nó
Suponha que o nível do nó raiz seja 0, cada nó abaixo tenha um nível que é igual ao nível do nó pai mais 1.
WITH RECURSIVE category_path (id, title, lvl) AS
(
SELECT id, title, 0 lvl
FROM category
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.title,cp.lvl + 1
FROM category_path AS cp JOIN category AS c
ON cp.id = c.parent_id
)
SELECT * FROM category_path
ORDER BY lvl;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Excluindo um nó e seus descendentes
Para deletar um nó e seus descendentes, basta remover o próprio nó, todos os descendentes serão deletados automaticamente pela DELETE CASCADE
restrição de chave estrangeira.
Por exemplo, para excluir o Laptops & PC
nó e seus filhos ( Laptops
, PC
), use a seguinte instrução:
DELETE FROM category
WHERE
id = 2;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Excluindo um nó e promovendo seus descendentes
Para excluir um nó não folha e promover seus descendentes:
- Primeiro, atualize o
parent_id
dos filhos imediatos do nó para oid
do novo nó pai. - Em seguida, exclua o nó.
Por exemplo, para excluir o Smartphones
nó e promover seus filhos, como Android
, iOS
, Other Smartphones
nós:
Primeiro, atualize parent_id
para todos os filhos imediatos de Smartphones
:
UPDATE category
SET
parent_id = 7 -- Phones & Accessories
WHERE
parent_id = 8; -- Smartphones
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Segundo, exclua o Smartphones
nó:
DELETE FROM category
WHERE
id = 8;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Ambas as instruções devem ser agrupadas em uma única transação:
BEGIN;
UPDATE category
SET
parent_id = 7
WHERE
parent_id = 5;
DELETE FROM category
WHERE
id = 8;
COMMIT;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Movendo uma subárvore
Para mover uma subárvore, basta atualizar o parent_id
nó superior da subárvore. Por exemplo, para mover os Cameras & photo
como filhos de Phone and Accessories
, você usa a seguinte instrução:
UPDATE category
SET
parent_id = 7
WHERE
id = 5;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Neste tutorial, você aprendeu como usar o modelo de lista de adjacências para gerenciar dados hierárquicos no MySQL.