MySQL 根据父ID查询子孙

在MySQL数据库中，我们经常需要根据某个父ID查询其所有子孙节点。本文将详细介绍如何使用MySQL查询子孙节点，并提供代码示例。

问题描述

假设我们有一个部门表departments，表结构如下：

字段名数据类型说明
idint部门ID
namevarchar部门名称
parent_idint父部门ID

我们需要根据某个父ID查询其所有子孙部门。

关系图

以下是departments表的关系图：

DEPT int id PK 部门ID string name 部门名称 int parent_id FK 父部门ID has

解决方案

1. 使用递归查询

MySQL 8.0及以上版本支持递归查询，我们可以使用公用表表达式（CTE）来实现。

WITH RECURSIVE cte (id, name, path) AS (
    SELECT id, name, CAST(name AS CHAR(255))
    FROM departments
    WHERE id = ? -- 父ID
    UNION ALL
    SELECT d.id, d.name, CONCAT(cte.path, ' > ', d.name)
    FROM cte
    JOIN departments d ON cte.id = d.parent_id
)
SELECT * FROM cte;
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
2. 使用存储过程

如果MySQL版本低于8.0，我们可以使用存储过程来实现递归查询。

DELIMITER $$

CREATE PROCEDURE GetDescendants(IN parent_id INT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur_id INT;
    DECLARE cur_name VARCHAR(255);
    DECLARE cur_path VARCHAR(255);

    CREATE TEMPORARY TABLE IF NOT EXISTS temp_table (
        id INT,
        name VARCHAR(255),
        path VARCHAR(255)
    );

    -- 初始化临时表
    INSERT INTO temp_table (id, name, path)
    SELECT id, name, name
    FROM departments
    WHERE id = parent_id;

    -- 递归查询子孙节点
    WHILE NOT done DO
        SET done = TRUE;
        INSERT INTO temp_table (id, name, path)
        SELECT d.id, d.name, CONCAT(temp_table.path, ' > ', d.name)
        FROM temp_table
        JOIN departments d ON temp_table.id = d.parent_id
        WHERE NOT EXISTS (
            SELECT 1
            FROM temp_table t
            WHERE t.id = d.id
        )
        ORDER BY d.name;
    END WHILE;

    -- 查询结果
    SELECT * FROM temp_table;

    -- 清理临时表
    DROP TEMPORARY TABLE IF EXISTS temp_table;
END$$

DELIMITER ;
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.
  • 31.
  • 32.
  • 33.
  • 34.
  • 35.
  • 36.
  • 37.
  • 38.
  • 39.
  • 40.
  • 41.
  • 42.
  • 43.
  • 44.
3. 使用递归查询的示例

假设我们需要查询ID为1的部门的所有子孙部门，可以执行以下查询：

CALL GetDescendants(1);
  • 1.

结论

本文介绍了如何使用MySQL查询子孙节点，提供了两种解决方案：使用递归查询和使用存储过程。递归查询适用于MySQL 8.0及以上版本，而存储过程适用于所有版本的MySQL。通过这些方法，我们可以轻松地查询出某个父ID的所有子孙节点。希望本文对您有所帮助。