【若依(ruoyi)】菜单操作SQL

前言

• ruoyi 4.6

查询

查询一级菜单

select * from `sys_menu` where parent_id=0;

查询父级菜单已被删除的菜单

select m.*
from `sys_menu` m  
left join `sys_menu` mp on m.parent_id=mp.menu_id
where m.parent_id>0 and mp.menu_id is null;

删除

删除某个菜单

delete from `sys_menu` where menu_name='资源仓库';

删除父级菜单已被删除的菜单/无效菜单

delete m
from `sys_menu` m  
left join `sys_menu` mp on m.parent_id=mp.menu_id
where m.parent_id>0 and mp.menu_id is null;

新增

新增一级菜单

INSERT INTO `sys_menu` (`menu_name`,`parent_id`,`order_num`,`path`,`component`,`query`,`is_frame`,`is_cache`,`menu_type`,`visible`,`status`,`perms`,`icon`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`)
VALUES ('CMS',0,1,'cms',NULL,NULL,1,0,'M','0','0',NULL,'input','admin','2023-06-24 10:00:00','',NULL,'');

新增二级菜单

SELECT @parentId:= LAST_INSERT_ID();

INSERT INTO `sys_menu` (`menu_name`,`parent_id`,`order_num`,`path`,`component`,`query`,`is_frame`,`is_cache`,`menu_type`,`visible`,`status`,`perms`,`icon`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`)
VALUES ('栏目管理',@parentId,20,'channel','cms/channel/index',NULL,1,1,'C','0','0','cms:category:list','nested','admin','2023-04-07 16:22:44','',NULL,'');

新增三级菜单

SELECT @parentId:= LAST_INSERT_ID();

INSERT INTO `sys_menu` (`menu_name`,`parent_id`,`order_num`,`path`,`component`,`query`,`is_frame`,`is_cache`,`menu_type`,`visible`,`status`,`perms`,`icon`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`)
VALUES ('栏目查询',@parentId,1,'#','',NULL,1,0,'F','0','0','cms:channel:query','#','admin','2023-06-24 10:00:00','',NULL,'');