MySQL recursively queries the tree

In the process of database porting, the problem of recursive query to tree is encountered.

In SQL Server, it is easy to implement. Such as:

 with Tree  as (SELECTfrom asWHERE=@EnergyItemCodeUNIONall  SELECTfrom asINNERJOINon= Tree.f_energyitemcode)

and MySQL does not support with AS.

Implementation method:

Establish a stored procedure:

CREATEDefiner=' Root ' @ ' localhost 'PROCEDURE' Spg_getchildlst ' (inch' p_id 'varchar( -),inch' P_col_name 'varchar( -),inch' P_col_p_name 'varchar( -),inch' P_t_name 'varchar( -), out ' P_retval 'varchar( +))BEGIN  /** Recursive lookup dependents, input parameters for the specific parent ID, lookup ID column name, parent item ID column name, and table name.  Outputs a string of all IDs. **/    DECLAREStempVARCHAR( +); DECLAREStempchdVARCHAR( +); DeclareV_sqlvarchar( -);--SQL statements that need to be executed   SETStemp= '$'; SETStempchd=cast(p_id as CHAR); SetV_sql=Concat'SELECT Group_concat (', P_col_name,') into @sTempChd from', P_t_name,'where Find_in_set (', P_col_p_name,', @sTempChd) >0'); Set @v_sql=V_sql;--Note It is important to assign a string to a variable (you can not define it before, but start with @)          whileStempchd is  not NULL DoSETStemp=Concat (Stemp,',', STEMPCHD); SET @sTempChd=Stempchd; Preparestmt from @v_sql;--preprocessing requires the execution of dynamic SQL, where stmt is a variable         EXECUTEstmt--Execute SQL statement         deallocate Preparestmt--Release the preprocessing segment         SETStempchd=@sTempChd; --SELECT Group_concat (F_energyitemcode) to Stempchd from T_dt_energyitemdict where Find_in_set (F_parentitemcode, STEMPCHD) >0;      END  while; SETP_retval=stemp;END

Why should I build a stored procedure? Since many of our projects involve tree-like dependencies, it is abstracted that each table uses recursion to enter some field names and ID and table names.

The function does not support the execution of dynamic SQL.

Then in the actual project, call this stored procedure. 、

For example:

Call Spg_getchildlst (@EnergyItemCode,'F_energyitemcode','F_parentitemcode','t_dt_energyitemdict',@stmp); INSERT  intoTmpP01 (f_id, F_type)SELECTF_energyitemcode,0  from(SELECTF_energyitemcode fromt_dt_energyitemdictWHEREFind_in_set (F_energyitemcode,@stmp)          )  asT

Summary: The key point is that MySQL executes SQL dynamically and gets the return value in the form of the @ variable name.

Then there is the Find_in_set () function.

MySQL recursively queries the tree