I have a parameters table as shown below:

I trying to write a query to get the list of parameters used in param0.

Expected output is:

Explanation:

param0 contains param1 so need to search in param1

param1 contains param2 and param3 so need to search in param2, param3

param2 contains param4 so need to search in param4

param3 and param4 doesn't contains any parameters so search will stop here.

db-fiddle

CodePudding user response：

CREATE PROCEDURE get_params_list (param VARCHAR(255))
BEGIN
CREATE TABLE params_list (param VARCHAR(255) PRIMARY KEY,
                          param_value VARCHAR(255)) ENGINE = Memory;
INSERT INTO params_list 
SELECT *
FROM parameters
WHERE parameters.param = param;
REPEAT
    INSERT IGNORE INTO params_list
    SELECT parameters.*
    FROM params_list
    JOIN parameters ON LOCATE(CONCAT('$(', parameters.param, ')'),
                              params_list.param_value);
UNTIL NOT ROW_COUNT() END REPEAT;
SELECT params_list.param 
FROM params_list
WHERE params_list.param <> param;
DROP TABLE params_list;
END

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=2f51944e354f92cfe7d428ce84f46184

CodePudding user response：

Without a stored procedure, and with a maximum level of 3:

SET @search = 'param';

CREATE TEMPORARY TABLE Ptemp
   SELECT PARAM, P
   FROM (
      SELECT 
         PARAM,
         PARAM_VALUE,
         x.x,
         substring_index(substring_index(replace(PARAM_VALUE,')','('),'(',x.x),'(',-1) as P
      FROM parameters 
      CROSS JOIN (select 1 as x union
            select 2 as x union
            select 3 as x union
            select 4 as x ) x 
     ) x 
   WHERE P LIKE 'param%' 
;

CREATE TEMPORARY TABLE Ptemp1 as SELECT * FROM Ptemp;
CREATE TEMPORARY TABLE Ptemp2 as SELECT * FROM Ptemp;
CREATE TEMPORARY TABLE Ptemp3 as SELECT * FROM Ptemp;
CREATE TEMPORARY TABLE Ptemp4 as SELECT * FROM Ptemp;
CREATE TEMPORARY TABLE Ptemp5 as SELECT * FROM Ptemp;

select P from Ptemp WHERE PARAM=@search
union all
select P from Ptemp1 WHERE PARAM IN (select P 
                                     from Ptemp2 
                                     WHERE PARAM=@search)
union all
select P from Ptemp3 WHERE PARAM IN (select P 
                                     from Ptemp4 
                                     WHERE PARAM IN (select P 
                                                     from Ptemp5 
                                                     WHERE PARAM=@search))

see: DBFIDDLE

or DB-FIDDLE

The (multiple) copies of the temp table are needed because MySQL will give this error when re-using a temp table:

Query Error: Error: ER_CANT_REOPEN_TABLE: Can't reopen table: 'Ptemp1'

Conclusion: Writing a stored procedure is the way to go, at least in MySQL before version 8.0.