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 & Accessoriesnós são os filhos doElectronicsnó. E vice-versa, o nó eletrônico é o pai dosLaptops, Cameras & photo, Phones & Accessoriesnó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 categorycom três colunas: id, title, e parent_idcomo 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 idcoluna. A parent_idcoluna serve como chave estrangeira para a idcoluna da mesma categorytabela, 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_idcomo NULLa 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_idcomo o id de seu nó pai.
Por exemplo, os nós parent_idof Laptop & PCe são definidos como 1:Cameras & PhotosPhone & 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é id7.
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 iOSpara 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 CASCADErestrição de chave estrangeira.
Por exemplo, para excluir o Laptops & PCnó 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_iddos filhos imediatos do nó para oiddo novo nó pai. - Em seguida, exclua o nó.
Por exemplo, para excluir o Smartphonesnó e promover seus filhos, como Android, iOS, Other Smartphonesnós:
Primeiro, atualize parent_idpara 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 Smartphonesnó:
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_idnó superior da subárvore. Por exemplo, para mover os Cameras & photocomo 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.
