MySQL递归查询的3种实现方式实例

下面就来详细讲解“MySQL递归查询的3种实现方式实例”的完整攻略。

1. 什么是递归查询

递归查询（Recursive Query）是指在查询中包含了对查询结果的递归，也即递归查询是查询语句中包含有自己的查询语句，并且是在该查询语句的结果上进行的。

递归查询在实际应用中非常重要，可以用来处理一些层次结构数据，如组织结构、教育体系、目录树等等。相对于常规的SQL查询，递归查询具有更好的可扩展性和灵活性。

2. 递归查询实现方式

MySQL中可以通过3种方式实现递归查询：通用表达（CTE）、连接查询和存储过程。

2.1 通用表达式（CTE）实现递归查询

通用表达式（Common Table Expression，CTE）是MySQL5.7版本和之后版本新增的特性，可以方便地实现递归查询。

以下是一个递归查询组织结构树的示例：

WITH RECURSIVE
    org_tree(id, org_name, parent_id) AS (
        SELECT id, org_name, parent_id
        FROM org_structure
        WHERE parent_id IS NULL
        UNION ALL
        SELECT o.id, o.org_name, o.parent_id
        FROM org_structure o
        JOIN org_tree t ON o.parent_id = t.id
    )
SELECT *
FROM org_tree;

上述查询语句中，包含名为org_tree的CTE表，其中定义了包括id、org_name和parent_id在内的3个字段（也即查询结果列）。在第一个SELECT语句中，查询了顶级节点（parent_id为NULL）的行，这些行将组成递归基础；第二个SELECT语句中，通过JOIN操作将组织结构表中从属于顶级节点的所有节点加入到结果中，完成递归操作。

2.2 连接查询实现递归查询

在MySQL5.6之前的版本中没有CTE特性，但可以使用连接查询的方式实现递归查询，如下所示：

SELECT
    t1.id, t1.org_name, t1.parent_id,
    t2.id, t2.org_name, t2.parent_id,
    t3.id, t3.org_name, t3.parent_id,
FROM
    org_structure AS t1
    LEFT JOIN org_structure AS t2 ON t2.parent_id = t1.id
    LEFT JOIN org_structure AS t3 ON t3.parent_id = t2.id
WHERE t1.parent_id IS NULL

在这个查询中，通过LEFT JOIN（左连接）操作将组织结构表和自身连接三次，依次找到父节点、子节点和孙子节点。这种方式的缺点是如果递归的层数过多，需要连接的表也会过多，性能较差。

2.3 存储过程实现递归查询

通过MySQL存储过程的方式也可以实现递归查询：

DELIMITER ||
CREATE PROCEDURE get_org_tree(IN node_id INT)
 BEGIN
  DROP TABLE IF EXISTS temp_table;
  CREATE TEMPORARY TABLE temp_table(id INT, org_name VARCHAR(255), parent_id INT);
  INSERT INTO temp_table (id, org_name, parent_id)
   SELECT id, org_name, parent_id
   FROM org_structure
   WHERE parent_id = node_id;
  SELECT id, org_name FROM temp_table;
  SET @child_rows = (SELECT COUNT(*) FROM temp_table);
  IF @child_rows = 0 THEN
   LEAVE LOOP;
  END IF;
  SET @i = 0;
  WHILE @i < @child_rows DO
   SET @i = @i + 1;
   SET @child_id = (SELECT id FROM temp_table ORDER BY id LIMIT @i - 1, 1);
   CALL get_org_tree(@child_id);
  END WHILE;
END ||
DELIMITER ;

上述存储过程调用的第一个参数为起始节点的ID，存储过程会返回该节点及其下面的所有节点信息。存储过程的主体代码实现了每次找到一个节点后递归查询该节点的子节点，直到查询不到子节点为止。

3. 总结

递归查询在处理层次结构数据等场景时，可以极大地提高代码的可扩展性和灵活性。MySQL中可以使用通用表达式（CTE）、连接查询和存储过程三种方式来实现递归查询。其中，CTE是MySQL5.7之后新增的特性，使用起来非常方便，而连接查询和存储过程方式适合于5.7之前的版本或要求较高性能的场景。