菜单角色表设计教程

菜单角色表设计基础

菜单角色表常用于权限管理系统，用于控制不同角色对系统菜单的访问权限。典型设计包含三张核心表：菜单表、角色表和菜单角色关联表。

菜单表(menu)结构示例：

CREATE TABLE menu (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL COMMENT '菜单名称',
    parent_id INT DEFAULT 0 COMMENT '父菜单ID',
    path VARCHAR(100) COMMENT '路由路径',
    component VARCHAR(100) COMMENT '前端组件',
    icon VARCHAR(50) COMMENT '图标',
    sort INT DEFAULT 0 COMMENT '排序',
    visible TINYINT DEFAULT 1 COMMENT '是否可见(0隐藏1显示)'
);

角色表(role)结构示例：

CREATE TABLE role (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(30) NOT NULL COMMENT '角色名称',
    code VARCHAR(20) NOT NULL COMMENT '角色编码',
    status TINYINT DEFAULT 1 COMMENT '状态(0禁用1启用)'
);

关联表设计关键点

菜单与角色的多对多关系通过关联表实现。典型设计包含两个外键字段：

CREATE TABLE role_menu (
    role_id INT NOT NULL COMMENT '角色ID',
    menu_id INT NOT NULL COMMENT '菜单ID',
    PRIMARY KEY (role_id, menu_id),
    FOREIGN KEY (role_id) REFERENCES role(id),
    FOREIGN KEY (menu_id) REFERENCES menu(id)
);

树形菜单处理方案

对于多级菜单结构，常用两种实现方式：

方案一：使用parent_id字段

ALTER TABLE menu ADD COLUMN level INT COMMENT '菜单层级';
ALTER TABLE menu ADD COLUMN tree_path VARCHAR(255) COMMENT '树路径(如1,2,3)';

方案二：闭包表设计

CREATE TABLE menu_closure (
    ancestor INT NOT NULL COMMENT '祖先节点',
    descendant INT NOT NULL COMMENT '后代节点',
    depth INT NOT NULL COMMENT '深度',
    PRIMARY KEY (ancestor, descendant)
);

权限控制实现

前端可通过接口获取角色对应的菜单树：

// Spring Boot示例
@GetMapping("/menus")
public List<MenuVO> getMenusByRole(@RequestParam Integer roleId) {
    return menuService.getMenusByRoleId(roleId);
}

SQL查询示例：

SELECT m.* FROM menu m
JOIN role_menu rm ON m.id = rm.menu_id
WHERE rm.role_id = #{roleId} AND m.visible = 1
ORDER BY m.sort ASC;

性能优化建议

添加复合索引提升查询效率：

CREATE INDEX idx_role_menu ON role_menu(role_id, menu_id);
CREATE INDEX idx_menu_parent ON menu(parent_id, visible);

定期清理无效关联：

DELETE FROM role_menu 
WHERE menu_id NOT IN (SELECT id FROM menu)
OR role_id NOT IN (SELECT id FROM role);

数据一致性维护

使用事务保证操作原子性：

@Transactional
public void updateRoleMenus(Integer roleId, List<Integer> menuIds) {
    roleMenuMapper.deleteByRoleId(roleId);
    roleMenuMapper.batchInsert(roleId, menuIds);
}

通过触发器维护树形结构完整性：

DELIMITER //
CREATE TRIGGER before_menu_insert BEFORE INSERT ON menu
FOR EACH ROW
BEGIN
    IF NEW.parent_id > 0 THEN
        SET NEW.level = (SELECT level+1 FROM menu WHERE id = NEW.parent_id);
        SET NEW.tree_path = CONCAT(
            (SELECT tree_path FROM menu WHERE id = NEW.parent_id), 
            ',', NEW.id
        );
    ELSE
        SET NEW.level = 1;
        SET NEW.tree_path = CAST(NEW.id AS CHAR);
    END IF;
END//
DELIMITER ;