时间戳

首页 > 解决方案 > MySQL、MariaDB：如何创建子父分层递归查询？

mysql - MySQL、MariaDB：如何创建子父分层递归查询？

问题描述

我有以下 MySQL 查询：

SELECT * FROM (SELECT * FROM paper ORDER BY id DESC) paper_sorted,
        (SELECT @pv := 26) initialisation
        WHERE find_in_set(id, @pv)
        AND length(@pv := concat(@pv, ',', last_id))
        ORDER BY @pv ASC LIMIT 0,15

http://sqlfiddle.com/#!9/9e2df/24

这在 MySQL 中运行良好；但是，我正在使用 MariaDB 数据库，但此查询不起作用。无论如何要重写它以使其适用于 MySQL 和 MariaDB？

数据库架构：

CREATE TABLE paper (
    id INT,
    last_id INT
);

INSERT INTO paper VALUES (19, 0);
INSERT INTO paper VALUES (20, 19);
INSERT INTO paper VALUES (21, 20);
INSERT INTO paper VALUES (22, 21);
INSERT INTO paper VALUES (23, 19);
INSERT INTO paper VALUES (24, 23);
INSERT INTO paper VALUES (25, 23);
INSERT INTO paper VALUES (26, 24);

MariaDB 诉 10.3.18；MySQL v. 5.7.27

此 SQL 查询应获取所有父 ID。在示例查询中，@pv变量为 26，因此得到 26->24->23->19。

但是，这不适用于 MariaDB。它只返回一行，在 MariaDB 中为 26。如何重写此查询以同时适用于 MariaDB 和 MySQL？

标签： mysqlsqlmariadb

解决方案

您应该能够将查询重写为Recursive CTE。这些已添加到 MariaDB 10.2 中，并允许使用 SQL 查询分层数据结构。

这些方面的东西应该起作用：

WITH RECURSIVE p AS (
    SELECT * FROM paper WHERE id = 26
    UNION
    SELECT c.* FROM paper AS c, p WHERE p.last_id = c.id
) SELECT * FROM p ORDER BY id ASC LIMIT 15;

编辑：如果您需要 SQL 向后兼容旧版本，您可以使用存储过程来实现相同的结果。

DELIMITER // ;

CREATE OR REPLACE PROCEDURE p1(IN id_in INT)
BEGIN
    DECLARE i INT DEFAULT id_in;
    CREATE OR REPLACE TEMPORARY TABLE results LIKE paper;

    WHILE i IS NOT NULL DO
          INSERT INTO results SELECT p.id, p.last_id FROM paper AS p WHERE p.id = i;
          SET i = (SELECT r.last_id FROM results AS r WHERE r.id = i LIMIT 1);
    END WHILE;

    SELECT * FROM results;
    DROP TEMPORARY TABLE results;
END;
//

DELIMITER ; //

推荐阅读