ACL设计，查询语句求优化

Question

数据库版本 MYSQL 5.1.41大致上是组有对应的权限，用户如果指明是否有权限的情况下，则按照组的权限
如果A组对模块A有访问权限，但指明A组下的用户A对模块A没有访问权限，则用户A无法访问模块A
如果对模块A有(没有)访问权限，则默认对模块A下的子模块也有(没有)访问权限，除非指明对哪个子模块没有(有)访问权限虽然查询语句可以满足要求，但总觉得还可以优化...求优化，或指导下思路...建表语句
CREATE TABLE IF NOT EXISTS `module` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `url` varchar(100) NOT NULL,
  `root` int(4) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `url` (`url`),
  KEY `root` (`root`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;CREATE TABLE IF NOT EXISTS `role` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;CREATE TABLE IF NOT EXISTS `user` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `rid` int(4) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `rid` (`rid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;CREATE TABLE IF NOT EXISTS `acl` (
  `type` tinyint(1) NOT NULL DEFAULT 0 COMMENT '0:role,1:user',
  `id` int(4) NOT NULL,
  `mid` int(4) NOT NULL,
  `acl` tinyint(1) NOT NULL DEFAULT 0 COMMENT '0:deny,1:allow',
  PRIMARY KEY (`type`, `id`, `mid`),
  KEY `mid` (`mid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
测试数据
INSERT INTO `module`(`id`,`url`,`root`) VALUES
(1,'welcome/index',1),
(2,'welcome/abc',1),
(3,'welcome/def',1),
(4,'welcome/ghi',1),
(5,'test/index',5),
(6,'test/jkl',5),
(7,'test/mno',5);INSERT INTO `role`(`name`) VALUES
('administrator'),
('user');INSERT INTO `user`(`name`,`rid`) VALUES
('admin',1),
('testtest',2);INSERT INTO `acl`(`type`,`id`,`mid`,`acl`) VALUES
(0,1,1,1),
(0,1,5,1),
(0,2,1,1),
(0,2,5,1),
(1,2,6,0),
(1,2,3,0);
外键约束
ALTER TABLE `module`
  ADD CONSTRAINT `module_ibfk_1` FOREIGN KEY (`root`) REFERENCES `module` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;ALTER TABLE `user`
  ADD CONSTRAINT `user_ibfk_1` FOREIGN KEY (`rid`) REFERENCES `role` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;ALTER TABLE `acl`
  ADD CONSTRAINT `acl_ibfk_1` FOREIGN KEY (`mid`) REFERENCES `module` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
查询语句
SELECT IF( `U`.`acl` IS NOT NULL , `U`.`acl` , `R`.`acl` ) AS `acl` 
FROM (  SELECT COALESCE( (
    IF( COUNT( `T`.`acl` ) > 0, `T`.`acl` , (      SELECT `acl` 
      FROM `acl` 
      INNER JOIN `module` ON `acl`.`mid` = `module`.`id` 
      WHERE `acl`.`type` = 0
      AND `acl`.`id` = 2
      AND `module`.`url` = 'test/jkl'
    ) ) ) , 0
  ) AS `acl` 
  FROM (    SELECT `acl` 
    FROM `acl` 
    INNER JOIN `module` ON `acl`.`mid` = `module`.`id` 
    WHERE `acl`.`type` = 0
    AND `acl`.`id` = 2
    AND `module`.`url` = 'test/index'
  ) AS `T` 
) AS `R` , (  SELECT IF( COUNT( `T`.`acl` ) >0, `T`.`acl` , (    SELECT `acl` 
    FROM `acl` 
    INNER JOIN `module` ON `acl`.`mid` = `module`.`id` 
    WHERE `acl`.`type` = 1
    AND `acl`.`id` = 2
    AND `module`.`url` = 'test/jkl'
    ) ) AS `acl` 
  FROM (    SELECT `acl` 
    FROM `acl` 
    INNER JOIN `module` ON `acl`.`mid` = `module`.`id` 
    WHERE `acl`.`type` = 1
    AND `acl`.`id` = 2
    AND `module`.`url` = 'test/index'
  ) AS `T` 
) AS `U`;

Answer 1

你可以将
 SELECT `acl` 
    FROM `acl` 
    INNER JOIN `module` ON `acl`.`mid` = `module`.`id` 
    WHERE `acl`.`type` = 1
    AND `acl`.`id` = 2
    AND `module`.`url` = 'test/index'
生成VIEW，再调用，代码太长了

Answer 2

VIEW不能传递参数吧...acl.type、acl.id和module.url是变量哦...

Answer 3

VIEW 本身不会提高效率，只是会让你的SQL语句显得短一些。建议你描述一下你想实现的功能，这样别人可以从整体考虑怎么优化，而不需要花时间去理解你的语句。

Answer 4

想实现的就是主楼说的啊...“用户A” 属于 “A组”
“A组” 对 “模块A” 有访问权限，则默认对 “模块A” 下的所有 “子模块” 都有访问权限
“用户A” 对“模块A” 下的 “模块AC”没有访问权限我的查询语句是首先
查询 “用户A” 是否对 “模块AC” 有访问权限
如果查询结果为NULL(没有明确定义 “用户A” 对 “模块AC” 的访问权限)，则继续查询，否则返回查询结果前面的查询返回NULL的话，再查询 “A组” 是否对 “模块AC” 有访问权限
对返回结果的操作同上前面对 “模块AC” 的查询结果为NULL的话，再查询 “用户A” 是否对 “模块A” 有访问权限
对返回结果的操作同上前面的查询返回NULL的话，再查询 “A组” 是否对 “模块AC” 有访问权限
如果还是返回NULL，则最终返回0，否则返回相应的结果(0或1)

Answer 5

上面倒数第2句写错了
应该是前面的查询返回NULL的话，再查询 “A组” 是否对 “模块A” 有访问权限
如果还是返回NULL，则最终返回0，否则返回相应的结果(0或1)