Is there an alternative to MySQL in MySQL?

Question

If I use Oracle, there is a connect by keyword that you can use to create hierarchical queries. I am currently using MySQL in a project, and I would like to know if there is an alternative for connect by in MySQL?

I tried google but haven’t understood anything yet. What I'm trying to achieve is to get a tree from the database with a single query. There are two tables:

areas and area_to_parent_join . The latter contains two identifiers: area_id , and the second - parent_id . Thus, this is basically an independent union, and I can create graphs using this model. In fact, it is currently only used to create trees, but this may change in the future. But in any case, what I would like to have is just a spanning tree.

Edit: areas can have more than 1,000,000 records, which makes most of the space-related features impossible.

Answer 1

As said in the comments, there is no shortcut with mysql.

BUT!

If you have the opportunity to change the structure of the database, you can deploy the best design for processing tree hierarchies.

If you follow THIS tutorial from Bill Carwin ( HERE is the original answer that refers to the slideshow tutorial), you can find 4 methods used to model the hierarchical structure:

• List of indicators>
• Enumeration path
• Nested Kits
• Closing table

Now the best model possible is the fourth (I leave the reader with descriptions of the other 3 models), which basically needs 2 tables: one for the elements and one for the paths. In the path table (the closure table itself), you will store every path from each node to each descendant (and not just from direct children!).

He suggested that you also keep the path length for each row, as it simplifies the query for immediate children in the tree.

Even if this solution requires more space, it has better overall performance and is very easy to use: it does not rely on recursive queries at all. And it will provide referential integrity for the entire dataset!

For example, to get each child device node # 4:

 select a.* from nodes a join paths b on a.node_id = b.descendant where b.ancestor = 4 

Another example: get all node # 11 ancestors

 select a.* from nodes a join paths b on a.node_id = b.ancestor where b.descendant = 11 

need to remove subtree <node # 6

 delete from paths where descendant in (select descendant from paths where ancestor = 6) 

Answer 2

In my application, I rarely need to query the entire subtree. Thus, in order to get away from the big problem of o2, I use 3-way tables for finding links / closing the table - populating the table with a descendant is only for a child, parent and grandparents. Just admit that you get what you invested, i.e. Do not query for the whole tree without a stored procedure.