MySQL tree structure recursive query

Oracle has been used before, and for tree queries you can use the start with ... connect by

Select *  from  with id='130000'by= Prior parent_id;

Yes, that's what Oracle supports.

Now the company is using MySQL, for this query method can only be achieved through the SQL statement

Languages are interlinked, not to mention SQL.

MySQL does not have its own syntax support, but you can create a function to implement a recursive query.

As shown in ...

Directly on the SQL statement

Create Table' nodelist ' (' ID ')int( One), ' Nodecontent 'varchar( -), ' PID 'int( One)); Insert  into' NodeList ' (' id ', ' nodecontent ', ' pid ')Values('1','a',NULL);Insert  into' NodeList ' (' id ', ' nodecontent ', ' pid ')Values('2','b','1');Insert  into' NodeList ' (' id ', ' nodecontent ', ' pid ')Values('3','C','1');Insert  into' NodeList ' (' id ', ' nodecontent ', ' pid ')Values('4','D','2');Insert  into' NodeList ' (' id ', ' nodecontent ', ' pid ')Values('5','e','3');Insert  into' NodeList ' (' id ', ' nodecontent ', ' pid ')Values('6','F','3');Insert  into' NodeList ' (' id ', ' nodecontent ', ' pid ')Values('7','g','5');Insert  into' NodeList ' (' id ', ' nodecontent ', ' pid ')Values('8','h','7');Insert  into' NodeList ' (' id ', ' nodecontent ', ' pid ')Values('9','I','8');Insert  into' NodeList ' (' id ', ' nodecontent ', ' pid ')Values('Ten','J','8');

Then create a function

DROP FUNCTION IF EXISTS' Getchild ' $$CREATEDefiner=' Root ' @ ' localhost 'FUNCTION' Getchild ' (rootidINT)RETURNS VARCHAR( +) CHARSET UTF8BEGIN        DECLAREPtempVARCHAR( +); DECLARECtempVARCHAR( +); SETPtemp= '#'; SETCtemp=CAST(Rootid as CHAR);  whileCtemp is  not NULL DoSETPtemp=CONCAT (Ptemp,',', ctemp); SELECTGroup_concat (ID) intoCtemp fromnodelistWHEREFind_in_set (pid,ctemp)>0; END  while; RETURNptemp; END$ $DELIMITER;

OK, the query can be done by using a function as a query condition.

SELECT *  from WHERE Find_in_set (ID, Getchild (3))

Reference HTTPS://WWW.JIANSHU.COM/P/F99665266BB1

The built-in function used inside the HTTPS://BAIJIAHAO.BAIDU.COM/S?ID=1595349117525189591&WFR=SPIDER&FOR=PC

As long as you can think of, there is a corresponding solution, fortunately, you should step on some pits others realize to you fill out.

MySQL tree structure recursive query