Manage hierarchical data with MySQL stored procedures

Below you will find all code to create the table and the stored procedures to manage hierarchical trees in MySQL.
The following stored procedures are provided:

tree_add_root()

tree_add_node(id,name,label,description)

tree_update_node(id,label,description)

tree_get_all(depth,indentstring)

tree_get_branch(id,depth,indentstring)

tree_get_branch_by_name(parentname,name,depth,indentstring)

tree_get_parents(id,indentstring)

tree_swap_leafs(id1,id2)

tree_del(id)

All code is documented and can be downloaded in a zip file.
Note that I do not describe the parameters for each procedure in this article. The parameters are described in the code.
Also, the procedures try to verify the input of the stored procedures and appropriate error messages will be returned if needed.

The method used to store a tree in the database:
Almost all applications need to store and manage hierarchical data in a database.
There are several ways of doing this. The ‘adjacency list model’ or the ‘recursion method’ is the most commonly used model.
A table created for this method will look something like this:

This is the most used method because it is very simple to understand the relations by looking at the data, very simple to insert the data and relative simple to retrieve it.
But retrieving the data can be slow and inefficient. You will need multiple joins or recursive functions in your application to retrieve the results you want.

Another way to store trees in your database is the Modified Preorder Tree Traversal method.
A table created for this method will look something like this:

The example data has two trees, one for the navigation in our website and another tree for products that we sell.tree_id : The id of the node, this is the primary keyname : name of the node, must be unique (case sensitive) in a branchlabel : the pretty name of the nodedescription : an optional long description of the nodelvl : the level of the node in the treeis_branch : if it is ‘1′ there is a sub-branch available. If it is ‘0′ then this node is a leaf in the tree.

Or get a branch by it’s name.
Because it is very likely that a tree has duplicate names in it, you need to provide the name of the parent and the name of the node that you want to retrieve. If this combination is used more then once in the tree, only one result is retrieved.
This procedure is not as efficient as the one above and in case of duplicate parent-child combinations the result can be unpredictable.
But it is much more verbose to ask for ‘Root’,'products’ then only for ‘id:3′.
Note that the names are case sensitive.
E.g.;

Add the root to the tree:
When you first set up the table you first need to add a root to the tree. You only need to do this once.
This will give you an error message if the table trees is not empty.mysql>call tree_add_root();

This has added the node ‘beds’ to the parent ‘Bedroom’. The new tree_id for this node is returned.
We left the ‘label’ parameter empty so the name will be used as the label too.

Note that in the parent ‘Bedroom’ there is already another node called ‘Beds’.
We are allowed to add this node name because the names of the nodes are case sensitive. Another ‘Beds’ would generate an error message.
Let’s see the result:

You write that source command overwrite procedure if it is already exist with same name, but MySQL doesn’t support atomic updates of stored procedures so it overwrite procedure. It is bug from MySQL end and yet not fixed. This is a long-standing bug, first filed in 2005 and still not fixed (http://bugs.mysql.com/bug.php?id=9588).

Your work is outstanding. I am using the trees procedures you published here and I’d like to ask you to post your code to Github. I think this would increase your standing and earning power, as well as ensure the code remains available.

If you have no time or interest in doing this – would you let me post it on your behalf?

[2] I found this code about a year ago and have been using it since. Other than adding a column to point each row to a reference in another table and a column to keep a “status” on each node, I haven’t made any changes to the source.

[3] One day, when I get around to it…I want to add a feature to change the way you count result nodes. Currently, the script counts not only childless nodes but also parent nodes. So if you are displaying inventory for a store, for example…you end up returning a number of total results for all nodes instead of only terminating child nodes.

I don’t want to use a scripting language to do this, for performance reasons. Yet I’m not strong enough in mysql to know how to approach this. Any ideas?

Hi. I like this example and it is very helped me and my group in our study. We are creating web application where we need to use hierarchy like this. We have Categories and related items table. But how can I count all the items in each category and subcategory? Just want to show the list like this:
Category Cars
Volvo(10)
Volvo Parts(5)
Volvo 325(5)
Opel(20)
VW(30)

Thanks for spotting the error!
I have updated the code and provided a new zip file (trees_2012-11-25) with your change.
If you have any additions to the code or want to share some new functions to the code, feel free to send them to me.

By the way there is a small mistake on tree_del (delete node procedure) that ends up leaving gaps in the series of lft and rht values. In the “update the parents” section (which actually also updates all neighbours to the right) the line should be :

SET v_node_tmp = ((v_node_rht – v_node_lft) + 1);

Right now it ends in “- 1″. Take a simple case of deleting a leaf: then v_node_tmp in the current formula would be 0, thus doing nothing… with the corrected line, the value is +2, which is what you want to subtract from all neighbors by (and from rht on parents).

Very good article, helped me learn procedure in MySQL. Works great. I added a few variants of the calls, like one for adding a node giving the NAME of the parent instead of the Id. Had to fix a problem arising from my having access only to version 5.1, all the SIGNAL statements failed. So I substituted code to emulate SIGNAL (from O’Reilly book MySQL Stored Procedure, Error Handling):