0

I want to copy a tree via a MySQL function. My table structure looks like this:

id|parent_id|name|position
1|0|rootnode|1
2|1|firstchild|1
3|1|secondchild|2
4|0|anotherroot|2
5|4|anotherchild|1

If I copy it it should look like this (remember: id is autoinc!) :

id|parent_id|name|position
6|0|rootnode|1
7|6|firstchild|1
8|6|secondchild|2
9|0|anotherroot|2
10|9|anotherchild|1

Is this possible? Are recursiv functions possible in MySQL? MySQL Version is 5.0.95

Best regards ...

fillibuster
  • 698
  • 4
  • 16
  • 33
  • Hope this helps http://stackoverflow.com/questions/15584013/how-to-find-the-hierarchy-path-for-a-tree-representation/15861254#15861254 – Meherzad Apr 29 '13 at 08:32
  • Hi, I want to copy the whole tree. My problem is the regeneartion of the autoincrement id's - so I lost the assignments. – fillibuster Apr 29 '13 at 08:38
  • 1
    Can you post some example... – Meherzad Apr 29 '13 at 08:39
  • 1
    Post your current code. – Jocelyn Apr 29 '13 at 08:42
  • I've edited my first post. I got no code example. If I use Delphi or PHP I manage this with a recursive function, but SQL - I don't know! – fillibuster Apr 29 '13 at 08:51
  • No, MySQL does not support recursive functions. It does support recursive *procedures*, up to a recursion depth of 255, but they are disabled by default. In general, MySQL is not well suited to this "adjacency list" model for hierarchical data; one is usually better to model the data using nested sets or a transitive closure table. – eggyal Apr 29 '13 at 08:53
  • You can create a stored procedure as per your needs. First you need to loop to get records for each branch and store records in your second table... – Meherzad Apr 29 '13 at 09:09

1 Answers1

0

Excerpt from the manual:

Stored functions cannot be recursive.

Recursion in stored procedures is permitted but disabled by default.

The link provided by Meherzad as a comment shows how to scan a tree in a procedure. This link will give you hints as to how to emulate a function call with IN and OUT procedure parameters.

Community
  • 1
  • 1
RandomSeed
  • 29,301
  • 6
  • 52
  • 87