How to build a tree structure in mysql

This article mainly explains "how to build a tree structure in mysql". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to build a tree structure in mysql".

Establish a tree structure in

This is not a new thing, there are many methods on the Internet, I have also tried one of the good methods, all unsatisfactory, this is my last recommended method, share, we discuss, of course, as difficult as high skill eyes, hehe. I'll just post the code, and if you have any questions, please contact me.

There should be a tree structure in mysql. I think the table should be like a write field:

Example menu table menu:

Www.2cto.com

[sql]

Create table menu

(

Id int not null auto_increment

Pid int

Name varchar (100) not null

Nlevel int

Scort varchar (8000)

Primary key (id)

)

Type = InnoDB

Alter table menu add constraint FK_Reference_67 foreign key (pid)

References menu (id) on delete restrict on update restrict

Create a stored procedure genNode_menu:

[sql]

BEGIN

DECLARE Level int

Set Level=0

Update menu an inner join (SELECT id,Level,concat (',', ID,',') scort FROM menu WHERE pid is null) b on a.id=b.id www.2cto.com

Set a.nlevelgramb.levelrecovera.scortsancb.scort

WHILE FOUND_ROWS () > 0 DO

SET Level=Level+1

Update menu an inner join (

SELECT ID,Level,scort FROM menu

WHERE nLevel=Level-1) b on a.pid=b.id

Set a.nlevelgramb.levelgramma a.scortwriting concat (b.sCortMagnea.IDrectemparentiary')

END WHILE

END

Insert data:

[sql]

INSERT INTO menu VALUES ('1customers, null,' menu 1items, null, null)

INSERT INTO menu VALUES ('2customers,' 1customers, 'menus 1-1miles, null, null)

INSERT INTO menu VALUES ('3percent, null,' menu 2percent, null, null)

INSERT INTO menu VALUES ('4miles,' 3seconds, 'menus 2-1minutes, null, null)

INSERT INTO menu VALUES ('5x, '4pm,' menu 2-1-1, null, null)

Www.2cto.com

Execute the stored procedure:

[sql]

Call registered genNode_menu

Let's take a look at what menu is like:

Good. That's the effect.

Now you can query as much as you want:

For example:

[sql]

Select * from menu a where a.scort not like'%, 1m% 'order by a.scort

Thank you for your reading, the above is the content of "how to build a tree structure in mysql". After the study of this article, I believe you have a deeper understanding of how to establish a tree structure in mysql. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!