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 ;