mysql - SQL陈述式为“存在具有特定值A和B的行或根本不存在具有值A的行”

我遇到了在应用程序中为更改的功能创建（MY）sql语句的问题。它是关于实现权限模型的。

现在桌子看起来像这样

照片（id：int，位置：varchar）
相册（ID：int，名称：varchar）
album2photo（相册：int，照片：int）
album2permission（专辑：int，谁：varchar）

因此，基本上照片属于相册，权限基于相册。用于获取用户stefan有权访问的所有照片的sql语句如下所示：

SELECT photo.* FROM photo
JOIN album2photo ON album2photo.photo=photo.id
JOIN album2permission ON album2permission.album=album2photo.album
WHERE album2permission.who IN ('stefan')

现在，我想实现另一种限制。我想阻止用户查看相册中的特定照片。为此，我考虑过创建另一个表

photo2permission（照片，谁）

现在的工作方式是，用户可以访问相册的所有照片，但具有特定照片权限的照片除外，并且他不属于该照片允许的那些用户。让我给你举个例子：

有一个包含照片（10、11、12）的相册（1），其权限如下所示：

album2permission：（1，stefan）；（1，卡特琳）
photo2permission：（11，katrin）

因此，stefan和katrin应该可以看到以下照片：

stefan：10、12
katrin：10、11、12

两者都可以查看相册1的照片，因为两者都在album2permission中有一个条目，但是stefan被禁止查看照片11，因为对于该照片有特定的权限，并且stefan不属于该照片允许的用户列表。

我的问题现在是相应的sql语句，尤其是条件“对于照片OR人确实属于允许的用户没有任何限制”。显然可行的是：

SELECT photo.* FROM photo
JOIN album2photo ON album2photo.photo=photo.id
JOIN album2permission ON album2permission.album=album2photo.album
WHERE album2permission.who IN ('stefan')
AND (
    NOT EXISTS (SELECT 1 FROM photo2permission WHERE photo2permission.photo=photo.id)
OR
    EXISTS (SELECT 1 FROM photo2permissionWHERE photo2permission.photo=photo.id AND photo2permission.who='stefan')
)

但这使我感到非常优雅和低效。任何想法都受到高度赞赏。

谢谢，
斯特凡

最佳答案

问题是您的数据设计，它固有地需要一个复杂的查询来检查。这个设计也有其他问题：

应仅通过查看该用户的记录来确定该用户的权限设置。在此设计中，有必要检查所有其他用户的记录以确定用户的许可。添加或更改用户可能会影响其他用户。在上面的示例中，如果要从数据库中删除用户Katrin，该怎么办？删除Katrin的所有记录将突然更改Stefan的权限。现在他们可以访问照片11了。
无法表达没有用户可以访问特定照片的想法。这似乎是一个重大问题。

我将通过更改数据设计来解决此问题。

有多种选择，但是您可能已经做过的最简单的选择就是使photo2permission为负数。换句话说，此处的输入将意味着用户被阻止查看照片。

然后您的查询就是这个（为了清楚起见，我将其重命名为blockedphotos）：

SELECT photo.* FROM photo
JOIN album2photo ON album2photo.photo=photo.id
JOIN album2permission ON album2permission.album=album2photo.album
WHERE album2permission.who IN ('stefan')
AND NOT EXISTS (
   SELECT 1 FROM blockedphotos WHERE
      blockedphotos.photo=photo.id and
      blockedphotos.who='stefan'
)

左联接将是另一种选择，在许多情况下更有效：

SELECT photo.* FROM photo
JOIN album2photo ON album2photo.photo=photo.id
JOIN album2permission ON album2permission.album=album2photo.album
LEFT JOIN blockedphotos ON
      blockedphotos.photo=photo.id and
      blockedphotos.who='stefan'
WHERE album2permission.who IN ('stefan')
AND blockedphotos.photo is null