The 4 schemes for MySQL to implement recursive query are consistent with the implementation of the CONNECT BY and START WITH clauses in Oracel

Write custom directory title here

In my recent work, I need to transfer the Oracel library to the Mysql library. Some syntax is different, and the corresponding SQL statement needs to be rewritten. This time I encountered the modification of start with recursive query.
- The first solution, MySQL 8.0 or later, directly uses the WITH RECURSIVE statement
- The second solution, stored procedure, can be supported both above and below 8.0.
- The third nested query
- The fourth self-join query
- The fifth uses self-join and variable query
- The sixth way to use cursors
- Recursive query in Oracle, use CONNECT BY and START WITH clause to implement recursive query

In my recent work, I need to transfer the Oracel library to the Mysql library. Some syntax is different, and the corresponding SQL statement needs to be rewritten. This time I encountered a modification of start with recursive query

Oracle has the syntax support of Start, but MySQL version 8.0 and above has WITH RECURSIVE syntax support; below 8.0, there is no specific syntax support, and the same effect in ORACEL can only be achieved through stored procedures, functions, and SQL statement curve saving.

The first solution, MySQL8.0 and above, directly use the WITH RECURSIVE statement

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

*In the above code, cte_name is the name of the recursive query, column_name is the column name, initial_query is the initial query statement, and recursive_query is the recursive query statement. The UNION ALL in the WITH RECURSIVE statement is used to connect the initial query and the recursive query. *It can be better understood by combining the following cases:

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;

The main function of the above paragraph of SQL is that the initial query statement is to query from the top-level department, that is, the department whose parent_id is NULL, and the recursive query statement is to query the next-level department related to the upper-level department, and at the same time Need to add 1 to the level of the query result.
##Secondly use stored procedures

The second solution, stored procedure, can be supported both above and below 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;

In the stored procedure, some variables are first declared, including done (indicating whether the cycle is completed), cur_id, cur_name, cur_level, cur_parent_id, etc. Then use the CURSOR statement to declare a cursor to query the next-level department of the current department. Use the FETCH statement to get the cursor’s result set, and if there are no more results, set the done variable to TRUE and exit the loop. In the loop, the information of the current department is inserted into the org_hierarchy table, and the stored procedure itself is called to recursively query the next level of department.

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);

The above is an example of using stored procedures to implement SQL recursive queries. Although complex recursive query logic can be implemented using stored procedures, it is necessary to write more complex stored procedures, and maintenance and debugging are relatively troublesome. Therefore, it is recommended to use the WITH RECURSIVE statement to implement SQL recursive queries. If the query process is very complicated, you can consider using stored procedures.

The third nested query

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
);

The SQL statement uses a nested query, first query the department whose parent_id is 1, and then query all sub-departments whose parent_id is equal to the department id, until all sub-departments are queried

The fourth self-join query

Self-join queries can be implemented recursively by using aliases within the same table. For example, suppose you have a table that contains organizations and departments.

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;

The above SQL statement uses a self-join query to query all sub-departments by joining the same table and using an alias. First, query the department whose parent_id is NULL as the starting point of the organization; then, use INNER JOIN to connect the department table and specify the connection condition as d2.parent_id = d1.id, that is, connect the current department and its sub-departments; finally, use the WHERE clause Specify the termination condition, that is, d1.parent_id IS NULL, that is, only the departments under the organization are queried, and departments at other levels are not included.

The fifth way is to use self-join and variable query

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;

The SQL statement uses a self-join and a MySQL variable @pv, @pv is used to save the ancestor node ID of the current node, and the initial value is set to the department ID to be queried. The execution process of the SQL statement is as follows:
Self-join the departments table to obtain the information of the current node and its parent node, and concatenate the ID of the current node and the ID of the parent node into a string separated by commas, and save it in the variable @pv.
In the self-join result, query the department record whose ID of the current node is equal to the variable @pv, or the department record whose parent node ID of the current node appears in the variable @pv string.
For each record in the query result, it contains the information of the current node and all its ancestor nodes.
It should be noted that this method also needs to manually maintain the update of the variable @pv, and needs to use the FIND_IN_SET function when querying, which may not be efficient enough. Therefore, if you use MySQL 8.0 and above, it is recommended to use the WITH RECURSIVE syntax to implement recursive queries, which is more intuitive and easy to maintain

The sixth way to use the cursor

The SQL statement first defines a variable @department_id, indicating the ID of the department to be queried, and then uses the temporary table recursive_departments to store the results of the recursive query, including the ID, name, ID, level, and path of the parent node of the department. The execution process of the SQL statement is as follows:
Create a temporary table recursive_departments, and insert the record of the specified department ID into the table as the initial query result.
Use the recursive method to query all department records whose parent node ID is equal to the node ID in the current query result, and insert these records into the recursive_departments table.
In the process of recursive query, use UNION ALL to merge all query results into the recursive_departments table, and finally get all department records related to the specified department 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;

Recursive query in Oracle, use CONNECT BY and START WITH clauses to implement recursive query

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);

To query all parent nodes of department 4, you can use the following SQL statement

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

START WITH id = 4 means to query from the department whose ID is 4, CONNECT BY PRIOR parent_id = id means to establish a connection relationship according to the parent_id and id fields, and perform recursive query. Executing the above SQL statement will return the following results

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

Special reminder: When using CONNECT BY and START WITH clauses for recursive queries, you need to pay attention to the situation of circular recursion, otherwise it may lead to an infinite loop. If there is cyclic recursion in the data, you can use the CONNECT_BY_ISCYCLE pseudo-column to determine whether there is cyclic recursion