mysql递归函数with recursive的用法举例

当我们需要处理一些具有层级结构的数据时，递归函数是非常有用的工具。MySQL提供了一种叫做with recursive的语法，用于创建递归函数。本攻略将详细讲解with recursive的用法，并提供两个示例说明。

什么是with recursive

with recursive语法通过使用with关键词和recursive关键词来定义递归函数。其中，with指定初始查询，recursive定义递归关系，并提供递归查询语句。

with recursive的语法

下面是with recursive的语法：

with recursive cte_name (column1, column2, ...) as (
  select initial_data
  union all
  select recursive_data from cte_name where condition
)
select * from cte_name;

其中，cte_name是递归函数的名称，column1, column2, ...指定查询结果的列名。在递归定义中，initial_data是递归的起始点，recursive_data是递归的目标点，condition用于定义递归结束的条件。

with recursive示例1

现在假设我们有一个包含员工和他们的经理的表格，并且该表格是由两个列manager和employee组成。manager列指向其管理者的ID，而employee列则指向员工的ID。此外，根据设定规则，应该将ID号为1的员工称为CEO。

接下来是一个示例表的示例：

create table employee (
  id int,
  name varchar(50),
  manager int
);

insert into employee values
  (1, 'Tom', null),
  (2, 'Jerry', 1),
  (3, 'Bob', 2),
  (4, 'Alice', 2),
  (5, 'David', 1),
  (6, 'Lucy', 5),
  (7, 'Kate', 6),
  (8, 'John', null);

现在，我们希望通过递归函数with recursive来查询员工及其经理的名称。下面是查询语句：

with recursive emp_hier (id, name, manager_id, manager_name, level) as (
  select e.id, e.name, e.manager, null, 0
  from employee e
  where e.id = 1

  union all

  select e.id, e.name, e.manager, eh.name, eh.level + 1
  from emp_hier eh
  join employee e on e.manager = eh.id
)
select id, name, manager_id, manager_name, level from emp_hier order by level, id;

这个查询语句中，我们首先在initial_query中查询出根节点，即CEO，然后在递归查询中执行递归查询，并在其中执行JOIN操作。emp_hier是递归表，他保留了递归的结果。我们可以看到最终查询结果是按照层次结构排列的，从CEO开始顺序列出了所有员工和他们的经理。

with recursive示例2

在本示例中，我们希望通过递归函数with recursive查询给定节点的所有子节点。前提是我们有一个名为category的表，其中包含一些分类信息，并且包含一个列parent_id，表示每个分类的父节点。下面是该表的示例：

create table category (
  id int,
  name varchar(50),
  parent_id int
);

insert into category values
  (1, 'Electronics', null),
  (2, 'Mobile Phones', 1),
  (3, 'Smartphones', 2),
  (4, 'TVs', 1),
  (5, 'LED TVs', 4),
  (6, 'OLED TVs', 4),
  (7, 'Accessories', 1),
  (8, 'Phone Cases', 7),
  (9, 'Headphones', 7),
  (10, 'Chargers', 7),
  (11, 'Computers', null),
  (12, 'Laptops', 11),
  (13, 'Desktops', 11),
  (14, 'Tablets', 11),
  (15, 'iPad', 14),
  (16, 'Galaxy', 14),
  (17, 'Kindle', 14);

现在假设我们要查询所有属于'Electronics'分类的子节点。下面是查询语句：

with recursive category_hierarchy as (
  select id, name, parent_id, 0 as level
  from category
  where name = 'Electronics'

  union all

  select c.id, c.name, c.parent_id, ch.level + 1
  from category c
  join category_hierarchy ch on c.parent_id = ch.id
)
select id, name, parent_id, level from category_hierarchy;

这个查询语句中，我们首先查询出根节点，即'Electronics'分类。然后在递归查询中执行递归查询，并在其中执行JOIN操作。category_hierarchy是递归表，他保留了递归的结果。我们可以看到最终查询结果是'Electronics'分类的所有子分类，按照层次结构排列的。