从JSON数据构建MySQL层级结构：PHP实现教程
在现代Web开发中，层级数据的存储和查询是非常常见的需求。比如组织架构、分类目录、评论回复等场景都涉及到树形结构的数据。本文将介绍如何使用PHP将从JSON获取的数据构建并存储到MySQL数据库中，形成层级结构。
1. 数据结构设计
首先我们需要设计一个合适的数据库表结构来存储层级数据。这里我们使用邻接表模型，它是最简单直观的层级数据存储方式。
1.1 数据库表设计
创建一个名为categories的表：
CREATE TABLE `categories` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `parent_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `parent_id` (`parent_id`), CONSTRAINT `categories_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
表结构说明：
id: 主键，自增name: 分类名称parent_id: 父级ID，指向自身的id字段，顶级节点的parent_id为NULL
2. JSON数据示例
假设我们有如下的JSON数据，表示一个分类层级结构：
{
"name": "电子产品",
"children": [
{
"name": "手机",
"children": [
{"name": "苹果"},
{"name": "三星"},
{"name": "华为"}
]
},
{
"name": "电脑",
"children": [
{"name": "笔记本"},
{"name": "台式机"}
]
}
]
}3. PHP实现代码
下面我们编写PHP代码来实现从JSON数据解析并存储到MySQL数据库的功能。
3.1 数据库连接配置
connect_error) {
die("连接失败: " . $conn->connect_error);
}
?>3.2 JSON数据解析与存储
prepare("INSERT INTO categories (name, parent_id) VALUES (?, ?)");
$stmt->bind_param("si", $node['name'], $parentId);
$stmt->execute();
// 获取刚插入的节点ID
$currentId = $stmt->insert_id;
$stmt->close();
// 如果有子节点，递归处理
if (isset($node['children']) && is_array($node['children'])) {
foreach ($node['children'] as $child) {
processNode($child, $currentId, $conn);
}
}
return $currentId;
}
// 示例JSON数据
$jsonData = '{
"name": "电子产品",
"children": [
{
"name": "手机",
"children": [
{"name": "苹果"},
{"name": "三星"},
{"name": "华为"}
]
},
{
"name": "电脑",
"children": [
{"name": "笔记本"},
{"name": "台式机"}
]
}
]
}';
// 解析JSON
$data = json_decode($jsonData, true);
// 处理根节点
if ($data) {
processNode($data, null, $conn);
echo "数据导入成功！";
} else {
echo "JSON解析失败！";
}
// 关闭连接
$conn->close();
?>4. 查询层级数据
存储数据后，我们可能需要以层级结构查询数据。以下是几种常见的查询方法。
4.1 递归查询所有子节点
prepare("SELECT id, name FROM categories WHERE parent_id = ?");
$stmt->bind_param("i", $parentId);
$stmt->execute();
$stmt->bind_result($id, $name);
while ($stmt->fetch()) {
$result[] = [
'id' => $id,
'name' => $name,
'children' => []
];
getAllChildren($id, $conn, $result[count($result)-1]['children']);
}
$stmt->close();
return $result;
}
// 使用示例：获取"电子产品"的所有子节点
$rootId = 1; // 假设"电子产品"的ID是1
$categoriesTree = getAllChildren($rootId, $conn);
echo json_encode($categoriesTree, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE);
?>4.2 使用闭包表优化查询
对于大型层级结构，邻接表模型的查询效率可能不够高。这时可以考虑使用闭包表来优化。
创建闭包表：
CREATE TABLE `category_closure` ( `ancestor` int(11) NOT NULL, `descendant` int(11) NOT NULL, `depth` int(11) NOT NULL, PRIMARY KEY (`ancestor`,`descendant`), KEY `descendant` (`descendant`), CONSTRAINT `category_closure_ibfk_1` FOREIGN KEY (`ancestor`) REFERENCES `categories` (`id`), CONSTRAINT `category_closure_ibfk_2` FOREIGN KEY (`descendant`) REFERENCES `categories` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
插入数据时同时维护闭包表：
prepare("INSERT INTO categories (name, parent_id) VALUES (?, ?)");
$stmt->bind_param("si", $node['name'], $parentId);
$stmt->execute();
$currentId = $stmt->insert_id;
$stmt->close();
// 维护闭包表
if ($parentId === null) {
// 根节点，自身到自身的闭包
$stmt = $conn->prepare("INSERT INTO category_closure (ancestor, descendant, depth) VALUES (?, ?, ?)");
$stmt->bind_param("iii", $currentId, $currentId, 0);
$stmt->execute();
$stmt->close();
} else {
// 非根节点，复制父节点的所有祖先闭包关系，并增加自身
$stmt = $conn->prepare("
INSERT INTO category_closure (ancestor, descendant, depth)
SELECT ancestor, ?, depth+1 FROM category_closure WHERE descendant = ?
UNION ALL
SELECT ?, ?, 0
");
$stmt->bind_param("iiiii", $currentId, $parentId, $currentId, $currentId);
$stmt->execute();
$stmt->close();
}
// 处理子节点
if (isset($node['children']) && is_array($node['children'])) {
foreach ($node['children'] as $child) {
insertWithClosure($child, $currentId, $conn);
}
}
return $currentId;
}
?>5. 完整示例代码
下面是一个完整的示例，包含了数据库连接、JSON解析、数据存储和层级查询：
conn = new mysqli($servername, $username, $password, $dbname);
if ($this->conn->connect_error) {
die("连接失败: " . $this->conn->connect_error);
}
}
public function importFromJson($jsonData) {
$data = json_decode($jsonData, true);
if (!$data) {
return false;
}
// 清空现有数据
$this->conn->query("TRUNCATE TABLE categories");
$this->conn->query("TRUNCATE TABLE category_closure");
// 导入新数据
$this->processNode($data);
return true;
}
private function processNode($node, $parentId = null) {
// 插入节点
$stmt = $this->conn->prepare("INSERT INTO categories (name, parent_id) VALUES (?, ?)");
$stmt->bind_param("si", $node['name'], $parentId);
$stmt->execute();
$currentId = $stmt->insert_id;
$stmt->close();
// 维护闭包表
if ($parentId === null) {
$stmt = $this->conn->prepare("INSERT INTO category_closure (ancestor, descendant, depth) VALUES (?, ?, ?)");
$stmt->bind_param("iii", $currentId, $currentId, 0);
$stmt->execute();
$stmt->close();
} else {
$stmt = $this->conn->prepare("
INSERT INTO category_closure (ancestor, descendant, depth)
SELECT ancestor, ?, depth+1 FROM category_closure WHERE descendant = ?
UNION ALL
SELECT ?, ?, 0
");
$stmt->bind_param("iiiii", $currentId, $parentId, $currentId, $currentId);
$stmt->execute();
$stmt->close();
}
// 处理子节点
if (isset($node['children']) && is_array($node['children'])) {
foreach ($node['children'] as $child) {
$this->processNode($child, $currentId);
}
}
}
public function getCategoryTree($rootId = null) {
$tree = [];
if ($rootId === null) {
// 获取所有根节点
$result = $this->conn->query("SELECT id, name FROM categories WHERE parent_id IS NULL");
} else {
// 获取指定节点的直接子节点
$stmt = $this->conn->prepare("SELECT id, name FROM categories WHERE parent_id = ?");
$stmt->bind_param("i", $rootId);
$stmt->execute();
$result = $stmt->get_result();
}
while ($row = $result->fetch_assoc()) {
$children = $this->getCategoryTree($row['id']);
$tree[] = [
'id' => $row['id'],
'name' => $row['name'],
'children' => $children
];
}
return $tree;
}
public function closeConnection() {
$this->conn->close();
}
}
// 使用示例
$categoryManager = new CategoryManager("localhost", "your_username", "your_password", "your_database");
// 导入JSON数据
$jsonData = '{
"name": "电子产品",
"children": [
{
"name": "手机",
"children": [
{"name": "苹果"},
{"name": "三星"},
{"name": "华为"}
]
},
{
"name": "电脑",
"children": [
{"name": "笔记本"},
{"name": "台式机"}
]
}
]
}';
if ($categoryManager->importFromJson($jsonData)) {
echo "数据导入成功！\n";
// 获取并打印层级结构
$tree = $categoryManager->getCategoryTree();
echo json_encode($tree, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE);
} else {
echo "数据导入失败！\n";
}
$categoryManager->closeConnection();
?>6. 总结
本文介绍了如何使用PHP将从JSON获取的数据构建并存储到MySQL数据库中形成层级结构。主要内容包括：
使用邻接表模型设计数据库表结构
解析JSON数据并递归存储到数据库
实现层级数据的查询功能
使用闭包表优化大型层级结构的查询性能
提供了一个完整的可复用PHP类来管理分类数据
这种方法适用于大多数层级数据场景，你可以根据实际需求进行调整和扩展。对于更复杂的层级操作，还可以考虑使用其他树形结构存储方案，如路径枚举、嵌套集等。