Mysql 5.7.30的递归搜索

Question

我需要找到特定文本存在的父id列表，无论它可能是在父名称中还是在其子名称中。

考虑下面的表

<表类> pid 父 名称 tbody><<tr>1空Parent1dynamic2空Parent2 td> tr>31child1-P142Child1-P254Child-c1p2-dynamic6空Parent37空Parent487Child-p4-dynamic

在MySQL 8+中可能是

WITH RECURSIVE
cte AS ( SELECT pid, parent, name, pid rpid, pid rparent, name rname
FROM test 
WHERE parent IS NULL
UNION ALL
SELECT test.pid, test.parent, test.name, cte.pid, cte.rparent, CONCAT(cte.rname, CHAR(0), test.name)
FROM cte
JOIN test ON cte.pid = test.parent )
SELECT DISTINCT rparent pid
FROM cte
WHERE rname LIKE @pattern;

或

WITH RECURSIVE
cte AS ( SELECT pid, parent 
FROM test
WHERE name LIKE @pattern
UNION ALL
SELECT test.pid, test.parent
FROM cte
JOIN test ON cte.parent = test.pid )
SELECT DISTINCT pid
FROM cte
WHERE parent IS NULL

在MySQL 5+中使用存储过程:

CREATE PROCEDURE get_rows_like_pattern (IN pattern VARCHAR(255))
BEGIN
CREATE TABLE cte (pid INT PRIMARY KEY, parent INT)
SELECT pid, parent 
FROM test
WHERE name LIKE pattern;
WHILE ROW_COUNT() DO
INSERT IGNORE INTO cte
SELECT test.pid, test.parent
FROM cte
JOIN test ON cte.parent = test.pid;
END WHILE;
SELECT DISTINCT pid
FROM cte
WHERE parent IS NULL;
DROP TABLE cte;
END

小提琴