MYSQL uses functions to query all child nodes

prerequisite: MySQL function find_in_set (str,strlist), cast (value as type)

Find_in_set (str,strlist): If the string str is a string list of n substrings consisting of strlist, the range of return values is 1 to n.

If STR is not strlist or strlist is an empty string, the return value is 0. If either parameter is NULL, the return value is null. This function will not work correctly when the first parameter contains a comma (', ').

difference between ①find_in_set (str,strlist) and in (): When the value of Strlist in Find_in_set (str,strlist) is constant, its effect is equal to in ();

Eg:select * from T_areainfo where Find_in_set (name, ' China, Huabei, Southern region ') = = select * from T_areainfo where name in (' China ', ' Huabei area ', ' Southern Region ')

The ②find_ind_set(str,strlist) differs from thelike: It is a broad fuzzy match, there is no delimiter in the string, Find_in_set is exact match, the field value is in English "," Delimited, find_in_ The result of a set query is less than the result of a like query.

eg

Select *  from where  like ' %xx Area% '

　　　　　　

Select *  from where Find_in_set ('xx District ', T. ' name ')                                select * from T_areainfo t where Find_in_set (' Beijing xx District 4 ' , T. ' Name ')

Two,cast (value as type): User data type conversion

Eg:cast (' 1 ' as int) converts the value ' 1 ' of the char type to 1 of the int type;

Back to the point: Use the MySQL function to find all child nodes

DROP FUNCTION IF EXISTSQuerychildrenareainfo; CREATE FUNCTION' Querychildrenareainfo ' (areaidINT)//Create a function querychildrenareainfo (areaid int) parameter int type
RETURNS VARCHAR(4000)//define return value type varchar (4000)Begin//function start  DECLAREstempVARCHAR(4000); To define a varchar type parameterDECLAREStempchdVARCHAR(4000); //define a varchar type parameter SETStemp= '$'; Assigning values to StmpSETStempchd= cast(Areaid as CHAR); Converts a parameter of type int in a function to a char-type assignment to Stempchd
 whileStempchd is  not NULLDo //loop bodySETStemp=CONCAT (Stemp,',', STEMPCHD); Stitching StempSELECTgroup_concat (ID) intoStempchd fromT_areainfoWHEREFind_in_set (ParentID, Stempchd)> 0;//According to the parent node, the ID of all child nodes under the parent node is queried, and multi-level queries are supportedEND  while;RETURNstemp;END;

　　Call Mode:

Select *  from where Find_in_set (Id,querychildrenareainfo (4))

　　

MYSQL uses functions to query all child nodes