Geek Answers Handbook

Is MySQL the best way to process this hierarchical data?

This is a follow-up:
MySQL - Is it possible to get all the subitems in a hierarchy?

I have an arbitrary depth table with an adjacency table (I am at a point where I can convert it to a nested set model .

I read the MySQL data on how to use the nested set model, although it seemed to be becoming more and more complex and very difficult to perform basic functions such as insert, update and delete.

Another blog post shows how to use a trigger system with an adjacency list model to save an ancestor table that links each object to its ancestors.

Now I need to return a list of all the children of this node in order to change or delete them. This hierarchical structure will not change all the time after its creation, but there will be a massive number of hierarchical structures.

Three methods that I see:

  • Created a stored procedure that will execute a recursive query that returns all the children.

  • Convert to a nested set model that will require it to be complex and possibly create a stored procedure for adding, editing, and deleting in it.

  • Create the ancestor table described above for insert / delete triggers to process all data.

If there are other methods that I am not learning, let me know and I will update this list.

+5
5

Quassnoi : MySQL. :

  • .
  • - , .

:

MySQL , ( ).

MyISAM, GEOMETRY, , SPATIAL .

, , .

.

, , . - , .

MySQL, PostgreSQL, . PostgreSQL , , MySQL, . Quassnoi : PostgreSQL, .

, Oracle . Oracle CONNECT BY, . Quassnoi article : Oracle . , , :

SELECT *
FROM yourtable
START WITH id = 42
CONNECT BY parent = PRIOR id
+4

. . , . , , , / , , , , .

, :

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';

+-------------+----------------------+--------------+-------+
| lev1        | lev2                 | lev3         | lev4  |
+-------------+----------------------+--------------+-------+
| ELECTRONICS | TELEVISIONS          | TUBE         | NULL  |
| ELECTRONICS | TELEVISIONS          | LCD          | NULL  |
| ELECTRONICS | TELEVISIONS          | PLASMA       | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS  | FLASH |
| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS   | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL  |
+-------------+----------------------+--------------+-------+
6 rows in set (0.00 sec)

SQL , , ;)

, . ( ), , . , .

+2

, , MongoDB. .

+1

, . , - , .

(memcache, redis ..) , SQL id -> data, node. , node.

/ node, " " O(1).

, node node. , , ( / ) node . , .

, , node , O(log n).

, SQL WHERE id IN( id1, id2, .... ) , .

+1

- SQL- , , , , .. , db API , // , , . , , SELECT A FROM B.

, , MySQL, , , , MySQL / .

-1
source

More articles:


All Articles