mysql 树形查询

MySQL树形查询简介

树形查询是数据库中一种常见的查询方式，主要用于处理具有层级关系的数据，在MySQL中，可以使用递归查询（Recursive Query）来实现树形查询，递归查询是一种在查询过程中引用自身的查询方法，可以用于解决具有层次结构的问题。

MySQL树形查询的实现方法

1、WITH RECURSIVE语句

WITH RECURSIVE语句是MySQL 8.0及以上版本支持的一种递归查询语法，可以用于实现树形查询，其基本语法如下：

WITH RECURSIVE cte_name (column_name1, column_name2, ...) AS (
   非递归部分，用于定义初始查询结果
   base_query
   UNION ALL
   递归部分，用于定义递归查询条件和结果
   recursive_query
)
SELECT * FROM cte_name;

2、使用JOIN语句

除了WITH RECURSIVE语句外，还可以使用JOIN语句实现树形查询，这种方法需要先创建一个临时表，然后将递归查询的结果插入到临时表中，具体步骤如下：

(1) 创建临时表：

CREATE TEMPORARY TABLE temp_table_name (
  id INT PRIMARY KEY,
  parent_id INT,
  ... 其他字段
);

(2) 插入数据：

INSERT INTO temp_table_name (id, parent_id, ...)
SELECT id, parent_id, ... FROM original_table;

(3) 执行递归查询：

SELECT t1.*, t2.*, ... 需要展示的字段
FROM original_table t1
LEFT JOIN temp_table_name t2 ON t1.id = t2.parent_id;

MySQL树形查询示例

假设有一个部门表（department），包含以下字段：id（部门ID）、name（部门名称）、parent_id（上级部门ID），现在需要查询所有部门的层级关系。

1、使用WITH RECURSIVE语句实现：

WITH RECURSIVE department_tree (id, name, parent_id, level) AS (
  初始查询，获取根节点部门信息
  SELECT id, name, parent_id, 1 as level FROM department WHERE parent_id IS NULL
  UNION ALL
  递归查询，获取子节点部门信息
  SELECT d.id, d.name, d.parent_id, dt.level + 1 as level FROM department d
  INNER JOIN department_tree dt ON d.parent_id = dt.id
)
SELECT * FROM department_tree;

2、使用JOIN语句实现：

(1) 创建临时表：

CREATE TEMPORARY TABLE temp_department (
  id INT PRIMARY KEY,
  parent_id INT,
  level INT,
);

(2) 插入数据：

INSERT INTO temp_department (id, parent_id, level)
SELECT id, parent_id, 1 as level FROM department;

(3) 执行递归查询：

SELECT d.*, t.level as level FROM department d
LEFT JOIN temp_department t ON d.id = t.parent_id;