MySQL实现上下级菜单（mysql上下级菜单）

MySQL实现上下级菜单

在Web开发中，上下级菜单是一种非常常见的导航方式，其功能不仅限于页面导航，还可以实现分类管理等多种应用。在实现上下级菜单时，MySQL数据库是一种非常好的选择。本文将介绍如何使用MySQL实现上下级菜单功能。

1. 数据库设计

设计一个数据库表可以实现上下级菜单功能。表中包含以下字段：

| 字段名 | 类型 | 说明 |

| ——— | ———– | —————- |

| id | int | 记录唯一标识符 |

| parent_id | int | 父级菜单id，顶级为0 |

| name | varchar(50) | 菜单名称 |

| url | varchar(200) | 菜单链接 |

| sort | int | 排序权重 |

通过设计该表结构，可以很方便的进行菜单的添加、修改和删除操作。在实际开发中，也可以根据需求增加其他字段。

2. 数据表结构创建

在MySQL中使用以下语句创建菜单表：

CREATE TABLE `menu` (

`id` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘记录唯一标识符’,

`parent_id` int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘父级菜单id，顶级为0’,

`name` varchar(50) NOT NULL COMMENT ‘菜单名称’,

`url` varchar(200) NOT NULL COMMENT ‘菜单链接’,

`sort` int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘排序权重’,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=’菜单表’;

3. 菜单数据添加

在数据表中添加数据时，可以根据需要以树形结构的方式添加，或者先添加顶级菜单，再添加子菜单的方式。例如，先添加顶级菜单“系统管理”，在添加子菜单“用户管理”和“权限管理”，添加菜单的SQL语句如下：

INSERT INTO `menu` (`id`, `parent_id`, `name`, `url`, `sort`) VALUES

(1, 0, ‘系统管理’, ”, 1),

(2, 1, ‘用户管理’, ‘/admin/user’, 1),

(3, 1, ‘权限管理’, ‘/admin/permission’, 2);

4. 菜单数据查询

查询上下级菜单可以使用MySQL中的递归查询。先查询顶级菜单，再查询子菜单，直到查询到最底层的叶子菜单。实现递归查询可以使用MySQL中的WITH RECURSIVE语句。一个简单的递归查询示例如下：

WITH RECURSIVE cte AS (

SELECT * FROM `menu` WHERE parent_id = 0

UNION ALL

SELECT c.* FROM `menu` c JOIN cte p ON c.parent_id = p.id

)

SELECT * FROM cte ORDER BY `sort` ASC;

5. 菜单数据更新和删除

在更新和删除菜单数据时，需要注意如果要删除一个父菜单，需要将其下所有子菜单一起删除，可以通过触发器实现级联删除。在删除前先查询该菜单下所有的子菜单，然后使用DELETE语句将其删除，一般情况下使用软删除即可，同时在应用代码中判断即可。如果需要支持物理删除，则可以使用如下触发器：

DROP TRIGGER IF EXISTS `menu_delete`;

DELIMITER //

CREATE TRIGGER `menu_delete` BEFORE DELETE ON `menu` FOR EACH ROW

BEGIN

DECLARE _id INT(11);

— 查询所有子菜单

DECLARE _sub_menu CURSOR FOR

SELECT id FROM `menu` WHERE `menu`.`parent_id` = OLD.`id`;

OPEN _sub_menu;

LOOP

FETCH _sub_menu INTO _id;

IF _id IS NULL THEN

LEAVE LOOP;

END IF;

— 递归删除子菜单

DELETE FROM `menu` WHERE `menu`.`id` = _id;

END LOOP;

CLOSE _sub_menu;

END //

DELIMITER ;

使用DELETE语句删除菜单时，如果需要级联删除子菜单，可以直接使用DELETE语句进行删除，例如：

DELETE FROM `menu` WHERE `menu`.`id` = 1;

6. 案例应用

通过以上步骤，我们已经可以实现上下级菜单功能了。在实际应用中，可以根据需求对菜单进行添加、修改、删除等操作。下面是使用PHP实现的一个上下级菜单案例：

// 数据库连接信息，在此省略

// 查询所有菜单，并以树形结构输出

$sql = “WITH RECURSIVE cte AS (

SELECT * FROM `menu` WHERE parent_id = 0

UNION ALL

SELECT c.* FROM `menu` c JOIN cte p ON c.parent_id = p.id

)

SELECT * FROM cte ORDER BY `sort` ASC”;

$res = $conn->query($sql);

$menus = array();

if ($res && $res->num_rows > 0) {

while ($row = $res->fetch_assoc()) {

$menus[$row[‘id’]] = array(

‘id’ => $row[‘id’],

‘parent_id’ => $row[‘parent_id’],

‘name’ => $row[‘name’],

‘url’ => $row[‘url’],

‘sort’ => $row[‘sort’],

‘children’ => array()

);

}

// 构建树形结构

foreach ($menus as $menu) {

if ($menu[‘parent_id’] > 0) {

$parents = array();

$pid = $menu[‘parent_id’];

while ($pid > 0) {

$parent = isset($menus[$pid]) ? $menus[$pid] : null;

if (!$parent) {

break;

}

$parents[] = $parent;

$pid = $parent[‘parent_id’];

}

$parents = array_reverse($parents);

$node = $parents[0];

for ($i = 1; $i

$node = isset($node[‘children’][$parents[$i][‘id’]]) ? $node[‘children’][$parents[$i][‘id’]] : null;

if (!$node) {

break;

}

}

$node[‘children’][$menu[‘id’]] = $menu;

}

}

// 输出树形菜单

foreach ($menus as $menu) {

if ($menu[‘parent_id’] == 0) {

echo “

“;

}

}

}

// 渲染子菜单

function render_menu($menus) {

foreach ($menus as $menu) {

echo “

“;

}

}

?>

通过上述代码，我们可以将MySQL中查询到的菜单数据根据层级关系生成树形结构的HTML，实现上下级菜单导航功能。

小结

本文介绍了如何使用MySQL实现上下级菜单功能，涉及到了数据库表结构设计、数据添加、查询、更新和删除等方面。在实际应用中可以结合PHP等语言进行开发，实现各种功能需求。同时，本文提供了一个使用PHP实现的上下级菜单案例，供开发者参考。