Generazione di albero basato sulla profondità da dati gerarchici in MySQL (senza CTE)

Ciao Per molti giorni ho lavorato su questo problema in MySQL, tuttavia non riesco a capirlo. Qualcuno di voi ha suggerimenti?

Fondamentalmente, ho una tabella delle categorie con domini come: id , name (nome della categoria) e parent (id di parent della categoria).

Dati di esempio:

 1 Fruit 0 2 Apple 1 3 pear 1 4 FujiApple 2 5 AusApple 2 6 SydneyAPPLE 5 .... 

Ci sono molti livelli, forse più di 3 livelli. Voglio creare una query sql che raggruppa i dati in base alla gerarchia: genitore> figlio> nipote> ecc.

Dovrebbe generare la struttura ad albero, come segue:

 1 Fruit 0 ^ 2 Apple 1 ^ 4 FujiApple 2 - 5 AusApple 2 ^ 6 SydneyApple 5 - 3 pear 1 

Posso farlo usando una singola query SQL? L’alternativa, che ho provato e funziona, è la seguente:

 SELECT * FROM category WHERE parent=0 

Dopo di ciò, eseguo nuovamente il ciclo dei dati e seleziono le righe in cui parent = id. Questa sembra una ctriggers soluzione. Poiché è mySQL, non è ansible utilizzare CTE.

Puoi farlo in una singola chiamata da php a mysql se usi una stored procedure:

Chiamate di esempio

 mysql> call category_hier(1); +--------+---------------+---------------+----------------------+-------+ | cat_id | category_name | parent_cat_id | parent_category_name | depth | +--------+---------------+---------------+----------------------+-------+ | 1 | Location | NULL | NULL | 0 | | 3 | USA | 1 | Location | 1 | | 4 | Illinois | 3 | USA | 2 | | 5 | Chicago | 3 | USA | 2 | +--------+---------------+---------------+----------------------+-------+ 4 rows in set (0.00 sec) $sql = sprintf("call category_hier(%d)", $id); 

Spero che questo ti aiuti 🙂

Sceneggiatura completa

Struttura della tabella di prova:

 drop table if exists categories; create table categories ( cat_id smallint unsigned not null auto_increment primary key, name varchar(255) not null, parent_cat_id smallint unsigned null, key (parent_cat_id) ) engine = innodb; 

Dati di test:

 insert into categories (name, parent_cat_id) values ('Location',null), ('USA',1), ('Illinois',2), ('Chicago',2), ('Color',null), ('Black',3), ('Red',3); 

Procedura:

 drop procedure if exists category_hier; delimiter # create procedure category_hier ( in p_cat_id smallint unsigned ) begin declare v_done tinyint unsigned default 0; declare v_depth smallint unsigned default 0; create temporary table hier( parent_cat_id smallint unsigned, cat_id smallint unsigned, depth smallint unsigned default 0 )engine = memory; insert into hier select parent_cat_id, cat_id, v_depth from categories where cat_id = p_cat_id; /* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */ create temporary table tmp engine=memory select * from hier; while not v_done do if exists( select 1 from categories p inner join hier on p.parent_cat_id = hier.cat_id and hier.depth = v_depth) then insert into hier select p.parent_cat_id, p.cat_id, v_depth + 1 from categories p inner join tmp on p.parent_cat_id = tmp.cat_id and tmp.depth = v_depth; set v_depth = v_depth + 1; truncate table tmp; insert into tmp select * from hier where depth = v_depth; else set v_done = 1; end if; end while; select p.cat_id, p.name as category_name, b.cat_id as parent_cat_id, b.name as parent_category_name, hier.depth from hier inner join categories p on hier.cat_id = p.cat_id left outer join categories b on hier.parent_cat_id = b.cat_id order by hier.depth, hier.cat_id; drop temporary table if exists hier; drop temporary table if exists tmp; end # 

Esecuzioni di prova:

 delimiter ; call category_hier(1); call category_hier(2); 

Alcuni test delle prestazioni utilizzando il geoplanet di Yahoo inseriscono i dati

 drop table if exists geoplanet_places; create table geoplanet_places ( woe_id int unsigned not null, iso_code varchar(3) not null, name varchar(255) not null, lang varchar(8) not null, place_type varchar(32) not null, parent_woe_id int unsigned not null, primary key (woe_id), key (parent_woe_id) ) engine=innodb; mysql> select count(*) from geoplanet_places; +----------+ | count(*) | +----------+ | 5653967 | +----------+ 

quindi sono 5,6 milioni di righe (posti) nella tabella, vediamo come viene gestita la procedura di implementazione / stored dell’elenco di adiacenza chiamata da php.

  1 records fetched with max depth 0 in 0.001921 secs 250 records fetched with max depth 1 in 0.004883 secs 515 records fetched with max depth 1 in 0.006552 secs 822 records fetched with max depth 1 in 0.009568 secs 918 records fetched with max depth 1 in 0.009689 secs 1346 records fetched with max depth 1 in 0.040453 secs 5901 records fetched with max depth 2 in 0.219246 secs 6817 records fetched with max depth 1 in 0.152841 secs 8621 records fetched with max depth 3 in 0.096665 secs 18098 records fetched with max depth 3 in 0.580223 secs 238007 records fetched with max depth 4 in 2.003213 secs 

Complessivamente sono piuttosto soddisfatto di quelle fredde runtime in quanto non comincerei nemmeno a pensare di restituire decine di migliaia di righe di dati al mio front-end, ma preferirei build l’albero richiamando in modo dinamico solo diversi livelli per chiamata. Oh, e nel caso in cui stavi pensando che innodb è più lento di myisam – l’implementazione myisam che ho provato è stata due volte più lenta in tutti i campi.

Altre cose qui: http://pastie.org/1672733

Spero che questo ti aiuti 🙂

Esistono due metodi comuni per archiviare i dati gerarchici in un RDBMS: elenchi di adiacenza (che si stanno utilizzando) e insiemi nidificati. C’è una buona opinione su queste alternative nella gestione dei dati gerarchici in MySQL . Puoi fare ciò che vuoi in una singola query con il modello di serie nidificato. Tuttavia, il modello di serie nidificato rende più lavoro l’aggiornamento della struttura gerarchica, pertanto è necessario considerare i compromessi in base ai requisiti operativi.

Non è ansible ottenere ciò utilizzando una singola query. Il tuo modello di dati gerarchici è inefficace in questo caso. Vi suggerisco di provare altri due modi di memorizzare i dati gerarchici in un database: il modello MPTT o il modello “lineage”. L’utilizzo di uno di questi modelli consente di eseguire la selezione desiderata in un’unica operazione.

Ecco un articolo con ulteriori dettagli: http://articles.sitepoint.com/article/hierarchical-data-database

Il modo lineare:

Sto usando una brutta funzione per creare un albero in un campo stringa semplice.

 / topic title /001 message 1 /002 message 2 /002/001 reply to message 2 /002/001/001/ reply to reply /003 message 3 etc... 

la tabella può essere utilizzata per selezionare tutte le righe nell’ordine dell’albero con una semplice query SQL:

select * from morum_messages where m_topic=1234 order by m_linear asc

INSERT è solo selezionare il genitore lineare (e figli) e calcolare la stringa secondo necessità.

 select M_LINEAR FROM forum_messages WHERE m_topic = 1234 and M_LINEAR LIKE '{0}/___' ORDER BY M_LINEAR DESC limit 0,1 /* {0} - m_linear of the parent message*/ 

DELETE è semplice come eliminare il messaggio o eliminare linearmente tutte le risposte di quella principale.