mysql - 根据跨多行的约束选择行

Question

问题:

我正在使用闭包表来跟踪用户文件权限，并且在 JOIN 之后，这会导致跨多行的多个 read bool 值。如果所有连接的行都是可读的，我只想选择一行。使用 UNION 显然无法实现这一点。

详情:

文件夹表:

CREATE TABLE IF NOT EXISTS folders
(
   id                    INT NOT NULL AUTO_INCREMENT,
   path                  VARCHAR(500) NOT NULL,
   r                     BOOL NOT NULL DEFAULT FALSE,
   PRIMARY KEY ( id )
)engine=innodb;

文件表:

CREATE TABLE IF NOT EXISTS files
(
   id                    INT NOT NULL AUTO_INCREMENT,
   parent_folder_id      INT NOT NULL ,
   path                  VARCHAR(500) NOT NULL,
   r                     BOOL NOT NULL DEFAULT FALSE,
   FOREIGN KEY ( parent_folder_id ) REFERENCES folders ( id ),
   PRIMARY KEY ( id )
)engine=innodb;

文件夹-父文件夹关闭表:

CREATE TABLE IF NOT EXISTS parent_folders
(
   id                INT NOT NULL AUTO_INCREMENT,
   folder_id         INT NOT NULL,
   parent_folder_id  INT NOT NULL,
   FOREIGN KEY ( folder_id ) REFERENCES folders ( id ),
   FOREIGN KEY ( parent_folder_id ) REFERENCES folders ( id ),
   PRIMARY KEY ( id )
)engine=innodb;

现在，如果我想获取所有可读文件(暂时忽略我完全省略了用户)，我会这样开始

SELECT 
    F.id, F.path, F.r, P.parent_folder_id, D.path, D.r
FROM
    files AS F 
    LEFT JOIN parent_folders AS P 
        ON F.parent_folder_id = P.folder_id 
    LEFT JOIN folders AS D  
        ON P.parent_folder_id = D.id;

这将显示每个文件 ID、路径和读取权限的表格，可以从其每个父文件夹访问，就像这样

id   path                  r     id  path          r
......
0   /home/joe/foo/bar.txt  True  1   /home/joe/foo True
1   /home/joe/foo/bar.txt  True  2   /home/joe     True
1   /home/joe/foo/bar.txt  True  3   /home         True
1   /home/joe/foo/bar.txt  True  4   /             True
2   /home/jim/foo/bar.txt  True  5   /home/jim/foo True
2   /home/jim/foo/bar.txt  True  6   /home/jim     False
2   /home/jim/foo/bar.txt  True  7   /home         True
2   /home/jim/foo/bar.txt  True  8   /             True
....

在这种情况下，我想SELECT /home/joe/foo/bar.txt 因为指向它的每个父文件夹都是可读的，但我会不想 SELECT /home/jim/foo/bar.txt 因为其父文件夹之一不可读。

编辑:或者，我可以这样改写问题:“我可以 AND 跨多行的一列的值吗？”

Answer 1

可以使用非标准 SQL 来完成，但这取决于您的数据库供应商。例如，您可能希望在 Oracle 中使用 CONNECT BY 子句检查分层查询。 MySQL 可能有类似的东西。但是，出于三个原因，我建议不要使用这样的解决方案:

1. 供应商锁定。
2. 目前尚不清楚这些查询的效率如何，或者如何优化它们。
3. 如果您需要更多这样的规则(例如可继承的用户权限)，复杂性会很快增加。

相反，我会建议使用我在几个大中型项目中使用的以下方法:

1. 对于 r 字段使用三态 bool 值(TRUE、FALSE 和 NULL) ，其中 NULL 代表“继承”。

2. 为每个文件(可能还有每个文件夹)添加一个新字段 effective_r。这将包含应用所有继承规则的结果，并且只能是 TRUE 或 FALSE。当然，您必须在层次结构的每次更改时计算此字段，但这样做速度更快，因为更新不是那么频繁，并且当它们发生时，它们只会影响层次结构的一部分。

3. 定义自上而下的传播规则。在这种情况下很容易:

   parent effective_r       child r        child effective_r
   ---------------------    ------------   ---------------------
   <ROOT>                   NULL           TRUE
   <ROOT>                   TRUE           TRUE
   <ROOT>                   FALSE          FALSE
   TRUE                     NULL           TRUE
   FALSE                    NULL           FALSE
   TRUE|FALSE               TRUE           TRUE
   TRUE|FALSE               FALSE          FALSE
   

   用户权限的规则可能更加复杂和复杂。