1 用户表
CREATE TABLE `statistics_users` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `user_name` varchar(30) DEFAULT NULL, `user_password` varchar(100) DEFAULT NULL, `user_ip` varchar(50) DEFAULT NULL, `user_mobile` varchar(11) DEFAULT NULL, `token` int(50) DEFAULT NULL, `real_name` varchar(50) DEFAULT NULL, `is_del` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否删除 0 正常;1 已删除;', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=317 DEFAULT CHARSET=utf8 COMMENT='统计系统会员表'
2 角色表
CREATE TABLE `statistics_role` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `role_name` varchar(50) DEFAULT NULL COMMENT '角色名称', `is_del` tinyint(1) DEFAULT NULL COMMENT '是否删除 0 正常;1 已删除', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
3 菜单表
CREATE TABLE `statistics_menu` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `menu_action` varchar(100) DEFAULT NULL COMMENT '角色名', `is_del` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否删除', `fid` int(8) unsigned NOT NULL DEFAULT '0' COMMENT '父分类', `order` int(8) unsigned NOT NULL DEFAULT '0' COMMENT '排序', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
4 菜单角色表
CREATE TABLE `statistics_role_menu` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `role_id` int(11) DEFAULT NULL COMMENT '角色id', `menu_id` int(11) DEFAULT NULL COMMENT '菜单id', `is_del` tinyint(1) DEFAULT '0' COMMENT '是否删除 1 已删除', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
5 用户角色表
CREATE TABLE `statistics_user_role` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `user_id` int(11) unsigned NOT NULL COMMENT '会员id', `role_id` int(11) DEFAULT NULL COMMENT '角色id', `is_del` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否删除', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='用户和角色的对应表'
根据uid查权限
SELECT sm.menu_action FROM `statistics_users` AS su LEFT JOIN `statistics_user_role` AS sur ON su.id = sur.user_id LEFT JOIN `statistics_role_menu` AS stm ON stm.role_id = sur.role_id LEFT JOIN `statistics_menu` AS sm ON sm.id = stm.menu_id LEFT JOIN `statistics_role` AS sr ON sr.id = sur.role_id WHERE su.id = 316 AND sur.is_del = 0 AND su.is_del = 0 AND stm.is_del = 0 AND sm.is_del = 0 AND sr.`is_del` = 0