关联菜单和权限2025.2.9

最新推荐文章于 2025-04-11 13:54:26 发布

zgscwxd

最新推荐文章于 2025-04-11 13:54:26 发布

阅读量208

点赞数 2

分类专栏： mysql 文章标签：数据库 sql

本文链接：https://blog.youkuaiyun.com/zgscwxd/article/details/145535457

版权

mysql 专栏收录该内容

2 篇文章

订阅专栏

--
-- 转存表中的数据 `ay_menu`
--

INSERT INTO `ay_menu` (`id`,`mcode`,`pcode`,`name`,`url`,`sorting`,`status`,`shortcut`,`ico`,`create_user`,`update_user`,`create_time`,`update_time`) VALUES
('4','M104','M101','系统角色','/admin/Role/index','903','1','1','fa-hand-stop-o','admin','admin','2018-04-30 14:54:43','2018-04-30 14:54:43'),
('5','M105','M101','系统用户','/admin/User/index','904','1','1','fa-users','admin','admin','2018-04-30 14:54:43','2018-04-30 14:54:51'),
('6','M106','M101','系统日志','/admin/Syslog/index','905','1','1','fa-history','admin','admin','2018-04-30 14:54:43','2018-04-30 14:55:00'),
('7','M107','M101','类型管理','/admin/Type/index','906','0','0','fa-tags','admin','admin','2018-04-30 14:54:43','2018-04-30 14:55:13'),
('8','M108','M101','数据库管理','/admin/Database/index','907','1','1','fa-database','admin','admin','2018-04-30 14:54:43','2018-04-30 14:55:24')
('1','M101','0','系统管理','/admin/M101/index','900','1','0','fa-cog','admin','admin','2018-04-30 14:52:57','2018-04-30 14:52:57'),
('2','M102','M101','数据区域','/admin/Area/index','901','1','1','fa-sitemap','admin','admin','2018-04-30 14:52:57','2018-04-30 14:54:23'),
('3','M103','M101','系统菜单','/admin/Menu/index','902','0','0','fa-bars','admin','admin','2018-04-30 14:52:57','2018-04-30 14:54:35'),


--
-- 表的结构 `ay_type`
--

DROP TABLE IF EXISTS `ay_type`;
CREATE TABLE `ay_type` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '类型编号',
  `tcode` varchar(20) NOT NULL COMMENT '类型编码',
  `name` varchar(30) NOT NULL COMMENT '类型名称',
  `item` varchar(30) NOT NULL COMMENT '类型项',
  `value` varchar(20) NOT NULL DEFAULT '0' COMMENT '类型值',
  `sorting` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '排序',
  `create_user` varchar(30) NOT NULL COMMENT '添加人员',
  `update_user` varchar(30) NOT NULL COMMENT '更新时间',
  `create_time` datetime NOT NULL COMMENT '添加时间',
  `update_time` datetime NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `ay_type_tcode` (`tcode`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

--
-- 转存表中的数据 `ay_type`
--

INSERT INTO `ay_type` (`id`,`tcode`,`name`,`item`,`value`,`sorting`,`create_user`,`update_user`,`create_time`,`update_time`) VALUES
('1','T101','菜单功能','新增','add','1','admin','admin','2017-04-27 07:28:34','2017-08-09 15:25:56'),
('2','T101','菜单功能','删除','del','2','admin','admin','2017-04-27 07:29:08','2017-08-09 15:23:34'),
('3','T101','菜单功能','修改','mod','3','admin','admin','2017-04-27 07:29:34','2017-08-09 15:23:32'),
('4','T101','菜单功能','导出','export','4','admin','admin','2017-04-27 07:30:42','2017-08-09 15:23:29'),
('5','T101','菜单功能','导入','import','5','admin','admin','2017-04-27 07:31:38','2017-08-09 15:23:27');

DROP TABLE IF EXISTS `ay_menu_action`;
CREATE TABLE `ay_menu_action` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `mcode` varchar(20) NOT NULL COMMENT '菜单编码',
  `action` varchar(20) NOT NULL COMMENT '类型编码',
  PRIMARY KEY (`id`),
  KEY `ay_menu_action_mcode` (`mcode`)
) ENGINE=MyISAM AUTO_INCREMENT=79 DEFAULT CHARSET=utf8;

关联菜单和权限：

INSERT INTO ay_menu_action (mcode, action)
SELECT r.id, p.id as 'pid'  FROM ay_menu r, ay_type p

-- WHERE r.name = '系统管理' AND p.value IN ('add', 'del', 'mod', 'export', 'import');
WHERE  p.value IN ('add', 'del', 'mod', 'export', 'import');