MySQL实现递归查询的4中方案，与Oracel中CONNECT BY 和 START WITH子句实现效果一致

• 后端

这里写自定义目录标题

  • 最近的工作中遇到，需要将Oracel库转到Mysql库，有些语法不同，需要重写对应的SQL语句，这次遇到的是start with 递归查询的修改
    • 第一种方案，MySQL8.0以上版本直接使用WITH RECURSIVE语句
    • 第二种方案，存储过程，8.0以上和以下都可以支持。
    • 第三种嵌套查询
    • 第四种自连接查询
    • 第五种使用自连接和变量查询
    • 第六种使用游标
    • Oracle中的递归查询，使用 CONNECT BY 和 START WITH 子句来实现递归查询

最近的工作中遇到，需要将Oracel库转到Mysql库，有些语法不同，需要重写对应的SQL语句，这次遇到的是start with 递归查询的修改

Oracel中有Start 这个语法支持，但是MySQL 8.0版本以上有WITH RECURSIVE语法支持；8.0以下没有特定的语法支持，只能是通过存储过程，函数，SQL语句曲线救国实现ORACEL中同样的效果。

第一种方案，MySQL8.0以上版本直接使用WITH RECURSIVE语句

WITH RECURSIVE cte_name (column_name, ...) AS (
    SELECT initial_query
    UNION [ALL]
    SELECT recursive_query FROM cte_name
)
SELECT * FROM cte_name;

*上面的代码中，cte_name是递归查询的名称，column_name是列名，initial_query是初始查询语句，recursive_query是递归查询语句。WITH RECURSIVE语句中的UNION ALL是用于连接初始查询和递归查询的。*结合下面的案例可以更好地理解：

WITH RECURSIVE org_hierarchy(id, name, parent_id, level) AS (
    SELECT id, name, parent_id, 1
    FROM departments
    WHERE parent_id IS NULL
    UNION ALL
    SELECT d.id, d.name, d.parent_id, oh.level + 1
    FROM departments d
    JOIN org_hierarchy oh ON oh.id = d.parent_id
)
SELECT * FROM org_hierarchy ORDER BY level, id;

上面的这段SQL主要实现的功能是，初始查询语句是从顶层部门开始查询，即parent_id为NULL的部门，递归查询语句是查询与上一级部门有关联的下一级部门，同时需要把查询结果的level加1。

##其次 使用存储过程

第二种方案，存储过程，8.0以上和以下都可以支持。

DELIMITER //

CREATE PROCEDURE org_hierarchy(IN parent_id INT, IN level INT)
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE cur_id INT;
DECLARE cur_name VARCHAR(255);
DECLARE cur_level INT;
DECLARE cur_parent_id INT;
DECLARE cur_dept CURSOR FOR SELECT id, name, parent_id FROM departments WHERE parent_id = parent_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur_dept;

dept_loop: LOOP
    FETCH cur_dept INTO cur_id, cur_name, cur_parent_id;
    IF done THEN
        LEAVE dept_loop;
    END IF;

    INSERT INTO org_hierarchy(id, name, parent_id, level) VALUES(cur_id, cur_name, cur_parent_id, level);

    CALL org_hierarchy(cur_id, level + 1);
END LOOP;

CLOSE cur_dept;
END//

DELIMITER ;

在存储过程中，首先声明了一些变量，包括done（表示循环是否完成）、cur_id、cur_name、cur_level、cur_parent_id等。然后使用CURSOR语句声明了一个游标，用于查询当前部门的下一级部门。使用FETCH语句获取游标的结果集，如果没有更多的结果，则设置done变量为TRUE，退出循环。在循环中，将当前部门的信息插入到org_hierarchy表中，并调用存储过程本身，递归查询下一级部门。

DELIMITER //

CREATE PROCEDURE find_children (IN parent_id INT)
BEGIN
    DECLARE child_id INT;
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur CURSOR FOR SELECT id FROM departments WHERE parent_id = parent_id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    DECLARE children CURSOR FOR SELECT id, name FROM departments WHERE parent_id = parent_id;
    
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_children (
        id INT,
        name VARCHAR(255)
    );
    
    OPEN cur;
    FETCH cur INTO child_id;
    
    WHILE NOT done DO
        CALL find_children(child_id);
        OPEN children;
        LOOP
            FETCH children INTO child_id, name;
            IF done THEN
                LEAVE LOOP;
            END IF;
            INSERT INTO temp_children (id, name) VALUES (child_id, name);
        END LOOP;
        CLOSE children;
        FETCH cur INTO child_id;
    END WHILE;
    CLOSE cur;
    
    SELECT * FROM temp_children WHERE 1;
    
    DROP TEMPORARY TABLE IF EXISTS temp_children;
END //


CALL find_children(1);

以上是使用存储过程实现SQL递归查询的示例。虽然使用存储过程可以实现复杂的递归查询逻辑，但是需要编写较为复杂的存储过程，维护和调试也相对麻烦。因此，建议使用WITH RECURSIVE语句实现SQL递归查询，如果查询过程非常复杂，则可以考虑使用存储过程。

第三种嵌套查询

CREATE TABLE departments (
    id INT,
    name VARCHAR(255),
    parent_id INT
);


SELECT *
FROM departments
WHERE parent_id IN (
    SELECT id FROM departments WHERE parent_id = 1
    UNION
    SELECT parent_id FROM departments WHERE parent_id IS NOT NULL AND parent_id != id
);

SQL语句使用了嵌套查询，先查询parent_id为1的部门，然后查询parent_id等于该部门id的所有子部门，直到所有子部门都被查询完毕

第四种自连接查询

自连接查询可以通过在同一张表中使用别名来实现递归查询。例如，假设有一个包含组织机构和部门的表。

CREATE TABLE departments (
    id INT,
    name VARCHAR(255),
    parent_id INT
);


SELECT d2.*
FROM departments d1
INNER JOIN departments d2 ON d2.parent_id = d1.id
WHERE d1.parent_id IS NULL;

以上SQL语句使用了自连接查询，通过连接同一张表并使用别名来查询所有子部门。首先，查询parent_id为NULL的部门作为组织机构的起点；然后，使用INNER JOIN连接部门表并指定连接条件为d2.parent_id = d1.id，即连接当前部门和其子部门；最后，使用WHERE子句指定终止条件，即d1.parent_id IS NULL，即只查询组织机构下的部门，不包含其他层级的部门。

第五种使用自连接和变量查询

SELECT *
FROM (
  SELECT
    t1.id,
    t1.name,
    t1.parent_id,
    @pv := CONCAT_WS(',', t1.id, @pv) AS ancestors
  FROM departments t1
  JOIN (SELECT @pv := '4') tmp
  WHERE t1.id = @pv OR FIND_IN_SET(t1.parent_id, @pv)
) t2;

SQL语句使用了一个自连接和一个MySQL变量@pv，@pv用于保存当前节点的祖先节点ID，初始值设置为要查询的部门ID。SQL语句的执行过程如下：

对departments表进行自连接，获取当前节点以及其父节点的信息，并将当前节点的ID和父节点的ID拼接成一个以逗号分隔的字符串，保存到变量@pv中。

在自连接的结果中，查询当前节点的ID等于变量@pv的部门记录，或者当前节点的父节点ID在变量@pv字符串中出现的部门记录。

对查询结果中的每一条记录，都包含了当前节点以及其所有祖先节点的信息。

需要注意的是，这种方式也需要手动维护变量@pv的更新，而且在查询的时候需要使用FIND_IN_SET函数，效率可能不够高。因此，如果使用MySQL 8.0版本及以上的版本，建议使用WITH RECURSIVE语法来实现递归查询，更加直观和易于维护

第六种使用游标

SQL语句首先定义了一个变量@department_id，表示要查询的部门ID，然后使用临时表recursive_departments来存储递归查询的结果，包括部门的ID、名称、父节点的ID、层级、以及路径。SQL语句的执行过程如下：

创建临时表recursive_departments，将指定部门ID的记录插入到表中，作为初始的查询结果。

使用递归的方式，查询所有父节点ID等于当前查询结果中节点ID的部门记录，并将这些记录插入到recursive_departments表中。

在递归查询的过程中，使用UNION ALL将所有查询结果合并到recursive_departments表中，最终得到所有与指定部门ID有关的部门记录。

SET @department_id := 4;

DROP TEMPORARY TABLE IF EXISTS recursive_departments;
CREATE TEMPORARY TABLE recursive_departments (
  id INT NOT NULL PRIMARY KEY,
  name VARCHAR(255),
  parent_id INT,
  level INT,
  path VARCHAR(255)
);

INSERT INTO recursive_departments
SELECT
  id,
  name,
  parent_id,
  0 AS level,
  CAST(id AS CHAR(255)) AS path
FROM departments
WHERE id = @department_id
UNION ALL
SELECT
  t1.id,
  t1.name,
  t1.parent_id,
  t2.level + 1 AS level,
  CONCAT_WS(',', t1.id, t2.path) AS path
FROM departments t1
JOIN recursive_departments t2 ON t1.parent_id = t2.id;

SELECT * FROM recursive_departments;

Oracle中的递归查询，使用 CONNECT BY 和 START WITH 子句来实现递归查询

CREATE TABLE departments (
  id NUMBER PRIMARY KEY,
  name VARCHAR2(50),
  parent_id NUMBER
);

INSERT INTO departments VALUES (1, 'Department 1', NULL);
INSERT INTO departments VALUES (2, 'Department 2', 1);
INSERT INTO departments VALUES (3, 'Department 3', 2);
INSERT INTO departments VALUES (4, 'Department 4', 2);
INSERT INTO departments VALUES (5, 'Department 5', 1);
INSERT INTO departments VALUES (6, 'Department 6', 5);

要查询部门 4 的所有父节点，可以使用以下 SQL 语句

SELECT id, name, parent_id
FROM departments
START WITH id = 4
CONNECT BY PRIOR parent_id = id;

START WITH id = 4 表示从 ID 为 4 的部门开始查询，CONNECT BY PRIOR parent_id = id 表示按照 parent_id 和 id 字段建立连接关系，并进行递归查询。执行以上 SQL 语句，将返回以下结果

ID  NAME          PARENT_ID
--  ------------  ---------
4   Department 4  2
2   Department 2  1
1   Department 1  NULL

特别提醒：使用 CONNECT BY 和 START WITH 子句进行递归查询时，需要注意循环递归的情况，否则可能会导致死循环。如果数据中存在循环递归的情况，可以使用 CONNECT_BY_ISCYCLE 伪列来判断是否存在循环递归

本文来自网络，不代表协通编程立场，如若转载，请注明出处：https://net2asp.com/35de16fb08.html

赞 (0)
« 上一篇 1天前
下一篇 » 1天前

相关推荐