Corso MariaDB / Interrogare gerarchie e prevenire cicli con le CTE ricorsive
Una Common Table Expression ricorsiva, o CTE ricorsiva, parte da un insieme iniziale di righe e segue ripetutamente le relazioni per trovarne altre. Una query gerarchica affidabile richiede punto iniziale, regola di arresto, colonne di percorso sufficientemente ampie, prevenzione delle visite ripetute e ordinamento finale esplicito.
Riconoscere quando serve la ricorsione
Una CTE ordinaria denomina una fase della query. La CTE ricorsiva richiama quel nome anche nella propria definizione. La parte iniziale, o anchor, produce le prime righe; la parte ricorsiva trova quelle successive usando il risultato della fase precedente. L'esempio unisce le parti con UNION ALL.
Alberi di categorie e organigrammi possono avere profondità variabile. Per due livelli fissi può bastare un JOIN. Una gerarchia grande richiede anche accesso adeguato ai figli e un limite di profondità intenzionale. La prova degli indici aiuta a valutare l'accesso. La panoramica ufficiale delle CTE ricorsive MariaDB descrive iterazione e tipi delle colonne determinati dall'anchor.
Preparare un albero di categorie con sei righe
Usare appops_lab con un amministratore o un account di studio autorizzato a creare database e tabelle. Controllare prima SELECT VERSION(), DATABASE(); e gli oggetti esistenti. Se appops_lab è già usato dall'esercizio Docker, conservarne il contenuto. CREATE DATABASE IF NOT EXISTS non sostituisce il controllo. Se category_tree esiste già, esaminarne lo stato prima di ripetere la creazione.
La chiave esterna garantisce l'esistenza del genitore referenziato. Non dimostra che l'intera gerarchia sia priva di cicli, percorsi che tornano a un nodo già attraversato.
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');
I nomi inglesi delle categorie restano valori dimostrativi dell'SQL. Catalog è la radice, con Books, libri, e Tools, strumenti. Books contiene SQL e Python; Tools contiene Editors, editor. L'indice su parent_id supporta la ricerca dei figli. Le sei righe mostrano relazioni e correttezza, senza dimostrare miglioramenti generali di velocità.
Eseguire la query completa con profondità e percorso
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;
La prima SELECT restituisce la categoria 1 a profondità zero. La SELECT ricorsiva trova categorie con parent_id uguale alla categoria corrente e incrementa la profondità. Il percorso visited racchiude gli ID numerici tra barre: cercare l'ID 1 non corrisponde per errore all'ID 11.
Entrambe le colonne di percorso nell'anchor sono convertite esplicitamente in CHAR(1000). Se un testo iniziale breve determina un tipo troppo stretto, concatenazioni successive possono superarne la capacità. L'esempio presume ID interi, nomi brevi e profondità massima otto. Ricalcolare capacità e limite per altri requisiti: 1000 non è una dimensione sempre sufficiente.
| ID | Genitore | Profondità | Percorso |
|---|---|---|---|
| 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 |
L'ORDER BY finale per profondità e ID determina la sequenza mostrata. Non dedurre l'ordine del risultato dal processo ricorsivo. Ordinare il percorso testuale produce un ordine lessicografico che può differire da quello degli ID numerici.
Cambiare una sola condizione e osservare le righe incluse
Sostituire soltanto t.depth < 8 con t.depth < 1 e rieseguire. Il risultato contiene l'anchor a profondità zero e i suoi figli diretti: ID 1, 2 e 3. La condizione confronta la profondità del genitore, quindi un nodo a zero può ancora generare figli a uno. Ripristinare otto dopo il confronto.
In una prova distinta cambiare soltanto l'anchor in category_id = 2. Si ottiene il sottoalbero Books con ID 2, 4 e 5; Books è ora a profondità zero. Ripristinare l'anchor a 1 prima della prova del ciclo.
Un risultato tagliato dal limite non comprende necessariamente tutta la gerarchia. Verificare l'esistenza di livelli ulteriori prima di aumentarlo. La variabile max_recursive_iterations limita le iterazioni, senza sostituire una regola di arresto del dominio o la verifica delle relazioni.
Creare un ciclo temporaneo e annullare la modifica
Nella stessa connessione eseguire le due istruzioni seguenti, poi ripetere la query ricorsiva completa. Altre connessioni potrebbero non vedere la modifica del genitore non confermata.
START TRANSACTION;
UPDATE category_tree SET parent_id = 4 WHERE category_id = 1;
Assegnare il genitore 4 alla categoria 1 crea il ciclo 1 → 2 → 4 → 1 seguendo i figli. La query parte esplicitamente dalla categoria 1. Il controllo degli ID visitati impedisce il ritorno: la query termina ancora con sei righe, mentre il genitore della radice è temporaneamente 4. Annullare subito la modifica dopo l'osservazione:
ROLLBACK;
SELECT category_id, parent_id FROM category_tree WHERE category_id = 1;
Il genitore della categoria 1 deve tornare NULL. Un anchor parent_id IS NULL può perdere l'intera componente ciclica quando scompare la sua radice senza genitore. Evitare visite ripetute in una ricerca non dimostra quindi né l'assenza di cicli in tutto il database né la raggiungibilità di ogni componente. MariaDB offre anche la sintassi CYCLE ... RESTRICT; il percorso esplicito rende visibile la regola dell'esempio.
Verificare la gerarchia prima dell'uso applicativo
- Accertare che l'anchor scelga esattamente la radice o le radici desiderate.
- Distinguere genitori mancanti, autoriferimenti, cicli e componenti non raggiungibili.
- Scegliere profondità massima e capacità dei percorsi compatibili.
- Esaminare indice dei figli ed EXPLAIN e confrontare il numero di righe con le relazioni attese.
Per la pulizia concludere le transazioni aperte e accertare che la tabella contenga soltanto dati di prova eliminabili. Se appropriato, eseguire DROP TABLE appops_lab.category_tree;. Elimina struttura e righe e non viene annullato da un normale ROLLBACK. Non sostituire il nome con una tabella del dominio né eliminare un database condiviso con altri esercizi.





