MySQL递归实现简明教程（mysql中写递归）

MySQL递归实现：简明教程

递归是一种强大的编程技巧，能够遍历树形数据结构、解决拓扑排序等问题。在MySQL中，也可以借助递归来解决一些查询问题，本文将介绍MySQL的递归实现方法。

简单情况下的递归查询

我们来看一个最简单的递归查询，即查询1~n之间的所有整数的和。这个算法可以用递归实现：

DELIMITER //
CREATE FUNCTION sum(n INT)
RETURNS INT DETERMINISTIC
BEGIN
  IF n=1 THEN
    RETURN 1;
  ELSE
    RETURN n + sum(n-1);
  END IF;
END//

DELIMITER ;

接下来，我们可以使用该函数来查询1~100之间的所有整数的和:

SELECT sum(100) AS sum_of_integers;

这样就可以得到结果:

sum_of_integers
----------------
5050

尽管这个例子很简单，但它很好地阐述了递归的思想。递归函数会将问题分为相同的、较小的子问题，并在每个递归步骤中处理子问题，直到达到最底层，并将结果“传情”回来反向处理这些结果，直到达到原始问题。

MySQL的递归查询

MySQL只自带了两种类型的递归查询：递归公共表表达式和递归存储过程。下面我们将详细介绍这两种实现递归查询的方法。

递归公共表表达式

递归公共表表达式（CTE）是一个能够引用自身的表的一种查询工具，它的基本形式是：

WITH RECURSIVE  () AS (
  
  UNION [ALL]
  
)
SELECT  FROM ;

上面的CTE定义了一个名称为“的递归公共表表达式，该表表达式包含以下三个元素：

1. “是递归表中的列名列表。如果有多个列，则它们被用逗号分隔。

2. “是递归表的初始查询，它是一个常规的SELECT语句，而不是递归查询。这个查询结果是递归表中的初始行集。

3. “是递归查询，它引用递归表自身，并使用UNION（或UNION ALL）连接递归结果集，这个查询可能包含一个或多个关键字。

使用递归公共表表达式的关键是在递归查询中构造子集，并将其添加到递归结果中。这样，递归公共表表达式就能够递归地执行。

让我们看一个例子。假设我们有如下的产品目录结构：

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  parent_id INT DEFAULT NULL
);
INSERT INTO products VALUES (1, 'Electronic', NULL);
INSERT INTO products VALUES (2, 'TV', 1);
INSERT INTO products VALUES (3, 'Radio', 1);
INSERT INTO products VALUES (4, 'Laptop', 1);
INSERT INTO products VALUES (5, 'Computer', 4);
INSERT INTO products VALUES (6, 'Mac', 5);
INSERT INTO products VALUES (7, 'ThinkPad', 5);

现在，我们要递归查询“Electronic”分类下的所有子分类（即递归查询），可以用递归公共表表达式这样实现：

WITH RECURSIVE cte_products (id, name, parent_id) AS (
  SELECT id, name, parent_id FROM products
  WHERE id = 1   -- 初始查询为“Electronic”分类
  UNION ALL
  SELECT products.id, products.name, products.parent_id
  FROM products
  INNER JOIN cte_products ON products.parent_id = cte_products.id
) SELECT * FROM cte_products;

其中，初始查询是“Electronic”分类的id=1。递归查询部分通过连接产品和CTE产品（子类）表的父子关系查询以获取它们的子产品。

结果如下所示：

  id  |     name      | parent_id 
------+---------------+-----------
 1    | Electronic    | 
 2    | TV            | 1
 3    | Radio         | 1
 4    | Laptop        | 1
 5    | Computer      | 4
 6    | Mac           | 5
 7    | ThinkPad      | 5

递归存储过程

MySQL的递归存储过程是用于处理更复杂的递归查询的高级技术。存储过程是一组预编译SQL语句，这些语句可以接受输入参数并返回输出结果。

递归存储过程的思路类似于递归公共表表达式，区别在于：递归存储过程适合于递归查询需要缓存结果的情况；而递归公共表表达式适合于从已有的表定义构建树形结构的情况。

下面我们来看一个例子，假设我们有如下表：

CREATE TABLE files (id INT, parent_id INT, name VARCHAR(50));

该表存储了一个文件系统，记录了每个文件的ID、父ID和名称。

我们的任务是编写一个存储过程，该存储过程将递归查询与该表相关的文件路径，从文件的根开始，直到文件查找完毕。具体过程如下：

1. 存储过程接受输入参数`file_id`，表示要查询的文件的ID。

2. 存储过程查找该ID所在文件的信息，并返回到调用方。

3. 存储过程重复步骤2，直到到达描述根目录的记录。

4. 存储过程将结果逆序排列并将其作为输出结果返回给调用方。

安装好范例表后，下面是该过程的完整代码：

DELIMITER //
CREATE PROCEDURE get_path(IN file_id INT, OUT path VARCHAR(256))
BEGIN
  SELECT name INTO path FROM files WHERE id = file_id;
  SELECT parent_id INTO file_id FROM files WHERE id = file_id;
  WHILE file_id IS NOT NULL DO
    SELECT CONCAT(name, '/', path) INTO path FROM files WHERE id = file_id;
    SELECT parent_id INTO file_id FROM files WHERE id = file_id;
  END WHILE;
END//
DELIMITER ;

该代码将`files`表的根目录设置为parent_id为NULL的记录。

现在，我们可以使用该存储过程来查询ID为4的文件的路径:

CALL get_path(4, @path);
SELECT @path;

结果如下所示：

@path
-------
/home/user3/documents/invoice.txt

结论

递归是一种强大的查询技术。MySQL支持两种类型的递归查询：递归公共表表达式和递归存储过程。递归公共表表达式适合于从已有的表定义构建树形结构的情况，而递归存储过程适合于递归查询需要缓存结果的情况。在实际开发中，我们可以根据具体情况选择不同的递归查询方式来实现自己的业务需求。