MySQL多对多关联左右表可空时如何正确查询并创建视图

1. 视图SQL定义被MySQL自动改写的原因 #

• MySQL创建视图时不会原样存储用户提交的SQL文本，会经过内置优化器做语义等价转换：包括连接顺序重排、外连接方向转换、冗余/矛盾条件移除等操作，只要转换后的语句与原语句执行逻辑一致，最终存储的视图定义就可能和用户手写的写法存在明显差异。
• 你原始写法中的LEFT OUTER JOIN + RIGHT OUTER JOIN组合，被优化器统一转换为嵌套左连接的形式，属于MySQL外连接优化的常规操作，本身是正常行为。
• 这次改写后结果不符合预期的核心问题有两点：
  • 原查询的驱动表选择错误：从中间关联表scpsl_ban_link出发做连接，而该表仅存储了ID3、4的关联关系，永远无法查询到未在关联表中出现的ID1、2两条用户封禁记录，和连接顺序、连接方向写法无关。
  • 原查询的过滤条件逻辑矛盾：WHERE user_id_ban_id IS NULL or ip_ban_id IS NULL会把两边ID都非空的匹配记录（即你预期要返回的ID3、4两条关联记录）全部过滤掉，优化器在改写时直接移除了这个逻辑冲突的条件，进一步导致结果偏差。

2. 可返回预期结果的正确查询写法 #

你的需求本质是获取全量用户ID封禁、全量IP封禁的并集，通过scpsl_ban_link表做匹配，关联成功的记录拼接两侧字段，关联失败的记录对应侧字段显示NULL，属于全外连接场景。MySQL原生不支持FULL OUTER JOIN语法，可通过UNION ALL分两部分查询合并实现，适配你提供的最小复现场景的SQL如下：

SELECT
    u.id AS user_id_ban_id,
    i.id AS ip_ban_id,
    u.name AS user_id_ban_name,
    i.name AS ip_ban_name
FROM scpsl_user_id_bans u
LEFT JOIN scpsl_ban_link l 
    ON u.id = l.user_id_ban_id
LEFT JOIN scpsl_ip_bans i 
    ON l.ip_ban_id = i.id

UNION ALL

SELECT
    u.id AS user_id_ban_id,
    i.id AS ip_ban_id,
    u.name AS user_id_ban_name,
    i.name AS ip_ban_name
FROM scpsl_ip_bans i
LEFT JOIN scpsl_ban_link l 
    ON i.id = l.ip_ban_id
LEFT JOIN scpsl_user_id_bans u 
    ON l.user_id_ban_id = u.id
WHERE u.id IS NULL

ORDER BY user_id_ban_id DESC, ip_ban_id ASC;

查询逻辑说明 #

• 第一部分查询以用户封禁表为驱动表，左连关联表、IP封禁表，拿到所有用户封禁记录，以及匹配到的对应IP封禁数据。
• 第二部分查询以IP封禁表为驱动表，左连关联表、用户封禁表，通过WHERE u.id IS NULL过滤掉已经在第一部分出现过的关联匹配记录，仅保留未关联任何用户封禁的IP记录，避免结果重复。
• 执行结果与你给出的预期输出完全一致：ID1、2的用户封禁无关联IP，IP侧字段为NULL；ID3、4的两表记录关联成功，两侧字段都有值；ID5、6的IP封禁无关联用户，用户侧字段为NULL。
• 创建视图时直接使用该SQL即可，你实际业务中需要的user_id、封禁时间、操作人、解封语句等字段，按照相同逻辑补充到SELECT子句中即可。

内容的提问来源于stack exchange，提问作者PatPeter