Atenção: esse artigo tem um vídeo complementar. Clique e assista!

De que se trata o artigo:

Este artigo apresenta duas soluções para consulta a dados hierárquicos usando o MySQL através de um estudo de caso simples e prático.


Para que serve:

Dados hierárquicos estão disponíveis em diversas situações do nosso dia-a-dia, precisando ser consultados sempre que necessário. Nesse sentido, ter soluções preparadas para atuarem nestes cenários é de suma importância para qualquer DBA.


Em que situação o tema é útil:

Sempre que necessário realizar consultas em dados hierárquicos, buscando por diversas informações relacionadas a esses dados, como hierarquia entre eles, profundidade, contagem de subordinados, etc.

Resumo DevMan

Dados hierárquicos possuem um relacionamento do tipo pai-filho que não é naturalmente representado em uma tabela de banco de dados relacional. Neste sentido, este artigo apresenta duas soluções para consulta a dados hierárquicos usando o MySQL através de um estudo de caso simples e prático.

A maioria dos usuários precisa lidar eventualmente com dados hierárquicos em um banco de dados usando SQL, e não há dúvida que o gerenciamento de dados hierárquicos não é o carro chefe dos bancos de dados relacionais, ou seja, estes não foram idealizados para isso. As tabelas de um banco de dados relacional usualmente são simplesmente uma lista não ordenada sem relação hierárquica entre os elementos (como XML). Dados hierárquicos possuem um relacionamento do tipo pai-filho que não é naturalmente representado em uma tabela de banco de dados relacional.

Para o propósito deste artigo, dados hierárquicos são uma coleção de dados onde cada item possui um único pai (com exceção do item raiz, que não possui pai) e cada pai pode possuir um ou mais filhos. Dados hierárquicos podem ser encontrados em uma variedade de aplicações de banco de dados, incluindo fóruns e seqüências de lista de emails, gráficos organizacionais de negócio, categorias de gerenciamento de conteúdos e categorias de produtos. Neste artigo, será usado a seguinte hierarquia de categoria de produtos a partir de uma loja de eletrônicos fictícia chamada SQLMag Store, apresentada na Figura 1.

Figura 1. Categorias Hierárquicas usadas na SQLMag Store.

Essas categorias apresentadas na Figura 1 formam uma hierarquia de forma similar aos demais exemplos citados anteriormente. Neste artigo, iremos examinar dois modelos para lidar com dados hierárquicos no MySQL, iniciando com o modelo de lista de adjacência tradicional.

O Modelo de Lista de Adjacência

Tipicamente as categorias de exemplo apresentadas na Figura 1 serão armazenadas em uma tabela similar à tabela apresentada na Listagem 1, onde estamos incluindo as declarações completas de CREATEs e INSERTs.

Listagem 1. Script SQL para criação e consulta da tabela CATEGORIA

CREATE TABLE categoria(
          categoriaId INT AUTO_INCREMENT PRIMARY KEY,
          nome VARCHAR(20) NOT NULL,
          categoriaPai INT DEFAULT NULL);
   
  INSERT INTO categoria
  VALUES(1,'Eletrônicos',NULL),(2,'Televisões',1),(3,'Tubo',2),
  (4,'LCD',2),(5,'LED',2),(6,'Eletrônicos Portáveis',1),
  (7,'MP4 Players',6),(8,'Pendrives',7), (9,'CD Players',6),(10,'Laptops',6);
   
  SELECT * FROM categoria ORDER BY categoriaId;
   
  +-------------+----------------------+--------+
  | categoriaId | nome                 | pai    |
  +-------------+----------------------+--------+
  |           1 | Eletrônicos          |   NULL |
  |           2 | Televisões           |      1 |
  |           3 | Tubo                 |      2 |
  |           4 | LCD                  |      2 |
  |           5 | LED                  |      2 |
  |           6 | Eletrônicos Portáveis|      1 |
  |           7 | MP4 Players          |      6 |
  |           8 | Pendrives            |      7 |
  |           9 | CD Players           |      6 |
  |          10 | Laptops              |      6 |
  +-------------+----------------------+--------+
  10 rows in set (0.00 sec)

No modelo de lista de adjacência, cada item na tabela contém um ponteiro para seu pai. O elemento no topo da hierarquia, neste caso “Eletrônicos”, possui o valor NULL para seu pai. O modelo de lista de adjacência possui a vantagem de ser bem simples. Por exemplo, é fácil perceber que “Pendrives” é um filho de “MP4 Players”, que é um filho de “Eletrônicos Portáveis”, que é um filho de “Eletrônicos”. Enquanto o modelo de lista de adjacência nos permite lidar com bastante simplicidade no código implementado do lado do cliente (aplicação final), trabalhar com o modelo pode ser problemático em SQL pura.

Recuperando uma Árvore Completa

A primeira tarefa comum quando lidamos com dados hierárquicos é exibir a árvore inteira, normalmente com alguma forma de indentação. A forma mais comum de fazer isto usando SQL pura é através do uso de um SELF-JOIN (operação de JOIN realizado entre uma tabela e ela mesma), conforme apresentado na ...

Quer ler esse conteúdo completo? Tenha acesso completo