MySQL：根据父级ID获取全部子级
在数据库设计中，树形结构常常用于表示层级关系，比如分类、组织架构等。为了高效管理这种结构，如何通过父级ID获取所有的子级成为了一个重要问题。本文将介绍如何在MySQL中实现这个功能，并提供代码示例，通过实际案例帮助您更好地理解这一过程。
1. 数据库表设计
首先，我们需要设计一个存储层级关系的数据库表。例如，一个分类表，表结构如下：
CREATE TABLE categories (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
parent_id INT DEFAULT NULL,
FOREIGN KEY (parent_id) REFERENCES categories(id)
);
在这个表中：
id是分类的唯一标识符。name是分类的名称。parent_id是它的父级ID，如果没有则为NULL。
2. 插入示例数据
为了能够进行演示，我们来插入一些示例数据。假设我们有以下类别：
INSERT INTO categories (name, parent_id) VALUES
('Electronics', NULL),
('Laptops', 1),
('Desktops', 1),
('Smartphones', 1),
('Gaming Laptops', 2),
('Business Laptops', 2),
('Office Desktops', 3),
('Gaming Desktops', 3);
在这个数据中：
- "Electronics" 是根分类，没有父级。
- "Laptops", "Desktops", "Smartphones" 是 "Electronics" 的子分类。
- "Gaming Laptops", "Business Laptops" 是 "Laptops" 的子分类，以此类推。
3. 获取子级的 SQL 查询
要根据父级ID获取所有子级，我们可以使用递归查询。MySQL 8.0 及以上版本支持公用表表达式（CTE），我们可以利用它来实现。以下是获取所有子级的查询示例：
WITH RECURSIVE subcategories AS (
SELECT id, name, parent_id
FROM categories
WHERE parent_id = ? -- 替换为目标父级ID
UNION ALL
SELECT c.id, c.name, c.parent_id
FROM categories c
INNER JOIN subcategories s ON c.parent_id = s.id
)
SELECT * FROM subcategories;
在这个查询中：
WITH RECURSIVE定义了一个递归CTE，首先选择指定parent_id的分类，然后通过自联接获取所有子分类。- 请注意，
?在这里代表你需要传入的父级ID，比如如果你想获取 ID 为 1 的子类，则 __将其替换为 1__。
4. 代码示例
下面是一个简单的 PHP 代码，演示如何使用上述查询获取并输出子级分类：
<?php
$pdo = new PDO('mysql:host=localhost;dbname=test', 'username', 'password');
$parentId = 1; // 目标父级ID
$sql = "
WITH RECURSIVE subcategories AS (
SELECT id, name, parent_id
FROM categories
WHERE parent_id = :parent_id
UNION ALL
SELECT c.id, c.name, c.parent_id
FROM categories c
INNER JOIN subcategories s ON c.parent_id = s.id
)
SELECT * FROM subcategories;
";
$stmt = $pdo->prepare($sql);
$stmt->execute(['parent_id' => $parentId]);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($results as $row) {
echo "ID: {$row['id']}, Name: {$row['name']}, Parent ID: {$row['parent_id']}\n";
}
?>
该代码首先连接到数据库，然后执行此前的 SQL 查询，以获取指定父级ID的所有子级，并将结果输出。
5. 状态图
为了帮助您理解这一过程，以下是一个状态图，表示不同状态之间的转换以及它们的逻辑关系：
stateDiagram
[*] --> 选择根分类
选择根分类 --> 查询子类
查询子类 --> 递归获取
递归获取 --> [*]
结论
通过本文介绍的方法，您可以轻松地根据父级ID获取所有子级分类，同时也学习了如何使用递归查询和公用表表达式（CTE）来处理层级数据。这个方法不仅适用于分类树形结构，也可以广泛应用于组织架构、评论系统等各种树状结构的实现。
如果您正在处理 mysql 中的层级数据，这种查询是一个不可或缺的工具。希望您能在实际项目中灵活运用这些概念，更加高效地管理层级数据。
