MySQL的递归查询

MySQL在版本8.0.1及以上引入了递归查询的支持，通过WITH RECURSIVE语法实现。这允许你在查询中执行递归操作，非常适合处理树形结构数据

• 举个栗子假如我们现在有一张表如下，其中是具有父子级关系的，使用parent_id来记录其父级id.

CREATE TABLE `map` (
  `id` int NOT NULL COMMENT '主键',
  `name` varchar(255) DEFAULT NULL COMMENT '地名',
  `parent_id` int NOT NULL COMMENT '父级id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT INTO `test`.`map` (`id`, `name`, `paret_id`) VALUES (1, '广西', 0);
INSERT INTO `test`.`map` (`id`, `name`, `paret_id`) VALUES (2, '桂林市', 1);
INSERT INTO `test`.`map` (`id`, `name`, `paret_id`) VALUES (3, '临桂区', 2);
INSERT INTO `test`.`map` (`id`, `name`, `paret_id`) VALUES (4, '两江镇', 3);
INSERT INTO `test`.`map` (`id`, `name`, `paret_id`) VALUES (5, '五通镇', 3);
INSERT INTO `test`.`map` (`id`, `name`, `paret_id`) VALUES (6, '临桂镇', 3);
INSERT INTO `test`.`map` (`id`, `name`, `paret_id`) VALUES (7, '山口村', 4);
INSERT INTO `test`.`map` (`id`, `name`, `paret_id`) VALUES (8, '广东', 0);
INSERT INTO `test`.`map` (`id`, `name`, `paret_id`) VALUES (9, '深圳', 8);
INSERT INTO `test`.`map` (`id`, `name`, `paret_id`) VALUES (10, '宝安区', 9);
INSERT INTO `test`.`map` (`id`, `name`, `paret_id`) VALUES (11, '北京', 0);
INSERT INTO `test`.`map` (`id`, `name`, `paret_id`) VALUES (12, '房山区', 11);

• 现在我们要查询广西下的所有地区该如何写sql语句呢？递归查询是个很好的选择。可用肉眼观察我们想要的结果应该是 广西- 桂林市- 临桂区 -两江镇 -五通镇- 临桂镇 – 山口村

  理解递归查询可能需要从基础开始解释。递归是一种自我调用的方法，而在关系型数据库中，递归查询通常用于处理层次结构的数据，比如树形结构。

下面我们先来分析一下递归查询的语法：

WITH RECURSIVE cte_name (column_list) AS (
    -- Anchor member (non-recursive term)
    SELECT ...
    
    UNION ALL
    
    -- Recursive member
    SELECT ...
)
SELECT * FROM cte_name;

• • WITH RECURSIVE: 这是MySQL中引入递归查询的关键字。
  • cte_name: 这是递归公共表表达式（Common Table Expression，CTE）的名称。
  • column_list: 列出了递归查询中要使用的列。
  • Anchor member: 这是递归的起点，相当于递归的终止条件。在我们地图例子中，它可能是根节点，即parent_id = 0。
  • UNION ALL: 连接递归查询的两个部分。
  • Recursive member: 这是递归的部分，其中通过递归联接表自身来构建层次结构。

再把这个概念运用到我们的地图例子中：

WITH RECURSIVE MapCTE AS (
    -- Anchor member (non-recursive term)
    SELECT id, name, parent_id
    FROM map
    -- 递归终止条件，即查到 id=0 ，且parent_id= 0 的广西
    WHERE parent_id = 0
    AND id = 0 
    
    UNION ALL
    
    -- Recursive member
    SELECT m.id, m.name, m.parent_id
    FROM map m
    -- 自连接的条件
    JOIN MapCTE c ON m.parent_id = c.id 
)
SELECT * FROM MapCTE;

• MapCTE: 这是递归查询的名称。
• 在Anchor member部分，我们选择了根节点，即parent_id IS NULL的节点。
• 在Recursive member部分，我们选择了地图表（map）中的节点，同时通过JOIN操作将这些节点与前一次递归的结果(MapCTE)联接，以获取它们的子节点。

通过不断地递归，我们能够构建出包含整个树形结构的递归查询结果。最后，SELECT * FROM MapCTE;语句用于选择所有结果。

总的来说，递归查询就是通过自我联接表来处理层次结构的一种技术。这使得在单个查询中能够获取整个层次结构，而不是多次查询数据库。

• 最后看一下执行结果吧