MySQL PHP / MySQL构建树形菜单
在本文中,我们将介绍如何使用MySQL和PHP构建树形菜单,该菜单可用于网站导航、文件浏览和目录结构展示等。
阅读更多:MySQL 教程
数据库结构设计
MySQL表中存储树型数据是非常方便的,我们可以利用表的自连接(self join)来实现。接下来的例子中,我们将设计一个简单的目录结构,其中包含目录名字和父目录ID两个字段:
CREATE TABLE `tb_menu` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '目录ID',
`name` varchar(25) DEFAULT NULL COMMENT '目录名称',
`pid` int(10) unsigned DEFAULT NULL COMMENT '父目录ID',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='目录表';
我们现在创建了一个名为 tb_menu 的表。在这个表中,我们可以通过使用pid字段来链接每个项目到它的父项目中。如果一个项目没有父项目,其pid值应该为NULL。
构建目录结构
一旦我们有了一个能够处理数据的表,我们就可以使用MySQL 查询且根据目录结构构建树形菜单。我们将需要扫描目录表获取每个目录的信息,然后为每个目录创建一个适当的HTML链接。
在PHP文件中,我们可以定义一个构建树形菜单的函数,它可以接收一个顶部目录ID作为参数,然后递归地遍历整个树形结构,为每个目录生成一个链接和“子菜单”列表。
以下是构建树形菜单的PHP代码示例:
function buildMenu(parent_id = null) {output = '';
// 1. 执行数据库查询
sql = "SELECT * FROM `tb_menu` WHERE `pid` ". (is_null(parent_id) ? 'IS NULL' : "= {parent_id}");result = mysqli_query(conn,sql);
// 2. 遍历查询结果
if(mysqli_num_rows(result)>0) {output .= '<ul>';
while(row = mysqli_fetch_assoc(result)) {
output .= '<li><a href="'.row['url'].'">'.row['title'].'</a>'.buildMenu(row['id']).'</li>';
}
output .= '</ul>';
}
// 3. 返回生成的HTML
returnoutput;
}
这个函数可以确保针对树形结构的任何深度都可以工作。我们可以调用该函数,将顶层菜单ID传递给它,并将生成的HTML插入我们网站的页面中。
性能问题
如何支持大规模的树形结构数据呢?这里有两个问题,我们要解决:
- 生成大量的SQL查询的问题
- 递归生成菜单项的问题
解决方法:
- 避免在每次需要一个菜单时生成所有的菜单项,并缓存结果。
- 使用 MySQL 中的树的存储过程解决递归生成菜单项的访问问题。
我们需要在菜单表中添加depth(深度)字段并生成MySQL 存储过程。MySQL在5.0.3版本中增加了对树型结构的支持。MySQL 中树支持三种类型/模型:
- 树结构
- 修改后的前序遍历树结构
- 修改后的层次遍历树结构
以修改后的层次遍历树结构为例,不同于传统的通过 pid 父id 相互关联,实际上我们可以只需要一张表存储:
CREATE TABLE `tb_menu` (
`id` INT(11) NOT NULL AUTO_INCREMENT COMMENT'目录ID',
`name` VARCHAR(50) DEFAULT NULL COMMENT '目录名称',
`depth` INT(11) DEFAULT 0 COMMENT '层级',
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='目录表';
然后我们可以用存储过程来插入新节点到树中:
DROP PROCEDURE IF EXISTS `insert_node`;
DELIMITER //
CREATE PROCEDURE `insert_node`(IN `new_name` varchar(50), IN `parent_id` int(11))
BEGIN
DECLARE this_depth INTEGER;
IF parent_id IS NULL THEN
SET this_depth = 0;
ELSE
SELECT `depth` INTO this_depth FROM `tb_menu` WHERE `id` = parent_id;
SET this_depth = this_depth + 1;
END IF;
INSERT INTO `tb_menu` (`name`,`depth`) VALUES (new_name,this_depth);
SET @new_id = LAST_INSERT_ID();
IF parent_id IS NOT NULL THEN
UPDATE `tb_menu` SET `has_child` = 1 WHERE `id` = parent_id;
INSERT INTO `tree_edge` (`ancestor_id`,`descendant_id`,`length`) VALUES (parent_id,@new_id,1);
CALL update_lengths(@new_id);
END IF;
END//
DELIMITER ;
同样,我们还需要一个存储过程来查询任意子节点:
DROP PROCEDURE IF EXISTS `get_children`;
DELIMITER //
CREATE PROCEDURE `get_children`(IN `node_id` int(11), OUT `depth` int(11), OUT `child_id` int(11))
BEGIN
SELECT `depth` INTO depth FROM `tb_menu` WHERE `id` = node_id;
SELECT `descendant_id` INTO `child_id` FROM `tree_edge` WHERE `ancestor_id` = node_id AND `length` = 1;
END//
DELIMITER ;
这样,我们的树形菜单结构就可以非常高效地工作了。
总结
使用MySQL和PHP构建树形菜单是一个非常简单和有用的技巧,可以为许多不同的网站和应用程序提供文件浏览、目录导航和信息层次展示等功能。在本文中,我们介绍了如何设计树形菜单的数据库结构,并提供了一个递归函数,以及如何优化性能。希望这篇文章能够帮助你了解树形菜单的实现方式。