时间戳

首页 > 解决方案 > Mysql 5.7.30 中的递归搜索

mysql - Mysql 5.7.30 中的递归搜索

问题描述

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

考虑下表

PID 父母 姓名
1 无效的 父1动态
2 无效的 父母2
3 1 孩子1-P1
4 2 儿童1-P2
5 4 Child-c1p2-动态
6 无效的 家长3
7 无效的 家长4
8 7 Child-p4-动态

我使用了以下 Mysql 查询

SELECT c.* 
FROM db.tbl AS c  
JOIN ( SELECT DISTINCT IFNULL(c.parent, c.pid) AS id  
       FROM db.tbl c  
       WHERE 1=1 AND c.name LIKE '%dyna%' 
       ORDER BY c.pid ASC ) s ON s.id = c.pid 
WHERE parent IS NULL  
ORDER BY pid LIMIT 0, 15

使用这个查询我搜索文本'dyna'并获得带有 ids [1 & 7] 的结果，它搜索第一级，但我需要结果为 [1, 2 & 7] - 递归搜索

标签： mysqlrecursive-query

解决方案

在 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

小提琴

推荐阅读