Create a tree structure in mysql

Creating a tree structure in mysql is not a new thing. There are many methods on the internet, and I have tried some of the best methods, which are not satisfactory. This is my last recommended method, for a moment, we will discuss it together. Of course, it's difficult to be as eye-catching. I directly paste the Code. If you have any questions, contact me. Mysql requires a tree structure. I think the table should have a writing segment. For example, the 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 the Stored Procedure genNode_menu: [SQL] BEGIN DECLARE Level int; Set Level = 0; update menu a inner join (SELECT id, Level, concat (',', ID, ',') scort FROM menu WHERE pid is null) B on. id = B. id www.2cto.com set. nlevel = B. level,. scort = B. scort; WHILE FOUND_ROWS ()> 0 do set Level = Level + 1; update menu a inner join (select id, Level, scort FROM menu WHERE nLevel = Level-1) B on. pid = B. id set. nlevel = B. level,. scort = concat (B. sCort,. ID, ','); end while; end insert data: [SQL] INSERT INTO menu VALUES ('1', null, 'menu 1', null, null ); insert into menu VALUES ('2', '1', 'menu 1-1 ', null, null); insert into menu VALUES ('3', null, 'menu 2 ', null, null); insert into menu VALUES ('4', '3', 'menu 2-1 ', null, null); insert into menu VALUES ('5 ', '4', 'menu 2-1-1 ', null, null); www.2cto.com executes the stored procedure: [SQL] call when using genNode_menu; let's take a look at what the menu is like:
Well, this effect can be queried as needed: for example, [SQL] select * from menu a where. scort not like '%, 1, %' order by. scort author lifaming15