直接JOIN权限表会导致数据丢失，因为INNER JOIN会剔除无权限记录，使用户基本信息消失；应改用EXISTS或LEFT JOIN+IS NOT NULL实现行级过滤，确保权限校验准确且安全。
为什么直接 JOIN 权限表会导致数据丢失？
因为 INNER JOIN 会剔除用户无权限的记录，而实际场景中往往需要「有权限才展示，没权限就过滤掉」——这看似合理，但容易忽略权限表本身存在多对一或空权限的情况。比如一个用户在 user_permissions 表里没有对应记录，INNER JOIN 后整行就消失了，连用户基本信息都没了。
真正该用的是 EXISTS 或带条件的 LEFT JOIN + WHERE IS NOT NULL，而不是无脑 INNER JOIN permissions。
- 权限校验本质是「行级过滤」，不是「关联补充字段」
- 若权限表含
resource_id和action，JOIN 条件必须同时匹配目标表主键和操作类型（如SELECT） - MySQL 8.0+ 可用
LATERAL实现更灵活的动态过滤，但兼容性差，慎用
如何用 EXISTS 替代 JOIN 实现安全过滤？
EXISTS 更贴近语义：只要子查询能查到一条匹配的权限记录，外层这行就保留。它天然避免了重复行、NULL 值干扰，也不依赖连接字段是否非空。
假设你查文章列表，只返回当前用户有 read 权限的文章：
SELECT a.id, a.title, a.content
FROM articles a
WHERE EXISTS (
SELECT 1 FROM user_permissions up
WHERE up.user_id = ?
AND up.resource_type = 'article'
AND up.resource_id = a.id
AND up.action = 'read'
)
-
?是当前用户 ID，务必参数化，防止 SQL 注入 - 子查询里不要写
SELECT *，用SELECT 1即可，性能更稳 - 确保
(user_id, resource_type, resource_id, action)有联合索引，否则慢得明显
什么时候必须用 LEFT JOIN + IS NOT NULL？
当你既要查主体数据，又需要把权限元信息（比如 permission_level 字段）一起带出来时，EXISTS 就不够用了——它只能判断真假，不能取值。
这时用 LEFT JOIN，再在 WHERE 过滤非空：
SELECT a.id, a.title, up.permission_level FROM articles a LEFT JOIN user_permissions up ON up.user_id = ? AND up.resource_type = 'article' AND up.resource_id = a.id AND up.action = 'read' WHERE up.id IS NOT NULL
- 必须把所有权限条件都写进
ON，不能挪到WHERE，否则会退化成INNER JOIN - 如果权限表允许同一资源有多条权限记录（如不同角色叠加），结果可能重复，需加
DISTINCT或聚合 - PostgreSQL 中可考虑用
LATERAL子查询替代，支持更复杂的权限逻辑（如调用函数判断）
动态权限常被忽略的三个坑
权限不是静态配置，真实系统里常混着角色继承、组织架构树、时间有效期等维度，硬写 SQL 容易漏判。
- 时间字段（如
valid_from/valid_to）不参与 JOIN 条件 → 导致过期权限仍生效 - 角色表未展开继承关系（比如「管理员」角色拥有「编辑」权限），直接查角色权限表会漏掉隐含权限
- 前端传来的
user_id是 session ID 或 token，后端没正确解析成真实用户标识，导致权限始终为空
最稳妥的做法是把权限判定逻辑下沉到数据库函数或视图里，SQL 层只调用 can_read_article(user_id, article_id) 这类封装好的接口，而不是每次手拼 JOIN 条件。
