MySQL 从自引用表中获取分层数据

在数据库设计中，有时候需要将数据组织成树形结构，如组织结构、商品分类等。在MySQL中，可以使用自引用表实现树形结构的存储和查询。本文将介绍如何从自引用表中获取分层数据。

阅读更多：MySQL 教程

什么是自引用表

自引用表是指一个表中的某些列与表中的其他行相关联。通常来说这些与其他行相关联的列会有一个名字，比如parent_id、ancestor_id等等。这样的表往往用于存储树形结构的数据。

以下是一个示例表，表示商品分类的树形结构：

CREATE TABLE `category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `parent_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `parent_id` (`parent_id`),
  CONSTRAINT `category_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `category` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

在这个表中，每行代表一个商品分类，其中parent_id表示父级分类的id。

如何查询自引用表的分层数据

方法一：使用递归查询

递归查询是一种常见的方法，用于从自引用表中获取分层数据。通过将自已连续连接多次，以达到查询全部节点的目的。

以下是使用递归查询方式，获取所有商品分类的各级父级分类和子级分类的结果：

WITH RECURSIVE cte (id, name, parent_id, level, path) AS (
    SELECT id, name, parent_id, 0, CAST(id AS CHAR(200))
    FROM category
    WHERE parent_id IS NULL
    UNION ALL
    SELECT t.id, t.name, t.parent_id, cte.level + 1, CONCAT(cte.path, ',', t.id)
    FROM cte
    JOIN category t ON t.parent_id = cte.id
)
SELECT *
FROM cte;

这段代码中使用了WITH RECURSIVE关键字，它可以方便定义递归查询所需的临时表。cte是一个临时表，它代表了查询树结构的每一层。这个查询从parent_id为NULL的根节点开始，从子节点到父节点递归查询到最深结点。

其中path字段用于存储每个节点的id，以字符串形式表示完整路径。

方法二：使用储存过程

我们也可以通过储存过程来查询自引用表的分层数据。

以下是获取某个节点的所有子孙节点的储存过程：

DELIMITER CREATE PROCEDURE `get_all_descendants`(IN `node_id` INT)
BEGIN
    DECLARE s CHAR(255);
    DECLARE copied INT;
    SET s = CAST(node_id AS CHAR);
    CREATE TEMPORARY TABLE _temp_table (
        id INT PRIMARY KEY,
        name VARCHAR(255),
        parent_id INT,
        level INT,
        path VARCHAR(255)
    ) ENGINE=Memory;
    INSERT INTO _temp_table SELECT id, name, parent_id, 0, s FROM category WHERE id = node_id;
    SET copied = ROW_COUNT();
    WHILE copied>0 DO
        INSERT INTO _temp_table
        SELECT c.id, c.name, c.parent_id, _temp_table.level + 1, CONCAT(_temp_table.path, ',', c.id)
        FROM category AS c
        JOIN _temp_table ON c.parent_id = _temp_table.id
        WHERE FIND_IN_SET(c.id, _temp_table.path) = 0;
        SET copied = ROW_COUNT();
    END WHILE;
    SELECT * FROM _temp_table;
    DROP TABLE _temp_table;
END
DELIMITER ;