Mysql8.0递归查询的简单用法示例

下面我将为大家详细介绍Mysql8.0递归查询的简单用法及示例。

什么是递归查询

递归查询是指在数据库中通过自身关联取得本身所需要的数据。通常我们会使用递归查询来查询有层级关系的数据，例如：树状结构等。

Mysql8.0递归查询的简单用法

在Mysql8.0之后，Mysql新增了WITH RECURSIVE关键字，可以很方便地进行递归查询。使用方式如下：

WITH RECURSIVE 递归查询语句

其中，“递归查询语句”指的是需要进行递归查询的SQL语句，WITH RECURSIVE关键字则是告诉Mysql这是一条递归查询语句。

示例1：查询树状结构的子级节点

我们假设有一个表示部门关系的表dept，其表结构及数据如下：

CREATE TABLE dept (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(100) DEFAULT NULL,
  parent_id int(11) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO dept (name,parent_id) VALUES ('总部',0),('财务部',1),('人事部',1),('市场部',1),('技术部',1),('人力资源部',3),('招聘部',3),('社保部',6),('公积金部',6);

我们现在需要查询出‘人事部’下面的所有部门，包括其子部门，可以使用如下递归查询语句：

WITH RECURSIVE cte (id,name,parent_id) AS (
  SELECT id,name,parent_id FROM dept WHERE name = '人事部' 
  UNION ALL
  SELECT d.id,d.name,d.parent_id FROM dept d JOIN cte ON d.parent_id = cte.id
)
SELECT * FROM cte;

我们在递归查询语句中定义了一个cte（Common Table Expression）公共表达式，并在其中设置了初始值为查询‘人事部’，接着我们在下方使用UNION ALL关键字加入子集查询，查询条件为子集的parent_id等于父集的id。

最后我们查询cte公共表达式即可获取到查询结果，查询结果如下：

+----+--------+-----------+
| id |  name  | parent_id |
+----+--------+-----------+
|  3 | 人事部 |         1 |
|  6 | 人力资源部 |       3 |
|  7 | 招聘部 |         6 |
+----+--------+-----------+

示例2：查询树状结构的所有父级节点

接下来我们再来看一个查询树状结构的所有父级节点的递归查询语句。假设我们有一个存储组织机构的表org，其表结构及数据如下：

CREATE TABLE org (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(100) DEFAULT NULL,
  parent_id int(11) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO org (name,parent_id) VALUES 
  ('A',0),('B',1),('C',2),('D',3),('E',4),
  ('F',0),('G',6),('H',7),('I',8),('J',9);

我们现在需要查询出‘E’所在的所有父级节点，可以使用如下递归查询语句：

WITH RECURSIVE cte AS (
  SELECT id,name,parent_id FROM org WHERE name = 'E'
  UNION ALL
  SELECT d.id,d.name,d.parent_id FROM org d JOIN cte ON d.id = cte.parent_id
)
SELECT * FROM cte;

我们同样在递归查询语句中定义了一个cte公共表达式，设置了初始值为查询E所在的部门，再通过UNION ALL关键字进行子集查询，查询条件为我们在公共表达式中查询到的parent_id等于当前查询结果的id。

最后我们查询cte公共表达式即可获取到查询结果，查询结果如下：

+----+------+------+-----------+
| id | name | parent_id | 
+----+------+------+-----------+
|  5 |  E   |         4 |
|  4 |  D   |         3 |
|  2 |  C   |         1 |
|  1 |  B   |         0 |
+----+------+------+-----------+

至此，Mysql8.0递归查询的简单用法及两个示例就介绍完毕了。