MySQL查询中最后两个OR条件未生效的技术求助

我的SQL查询整体可正常运行，但最后两个OR条件似乎被忽略，需要确保这些条件组合正常生效。

这两个未生效的条件为：

OR (v_extradataclients.`Disable ESET Check` = 1 AND     
    v_extradatacomputers.`Disable ESET Check` = 1 AND 
    v_extradataclients.`Disable SentinelOne Check` <> 1)
OR (v_extradataclients.`Disable ESET Check` = 1 AND 
    v_extradatacomputers.`Disable ESET Check` = 1 AND 
    v_extradatalocations.`Disable ESET Check` = 1  AND 
    v_extradatalocations.`Disable SentinelOne Check` = 0)

完整查询语句如下：

SELECT DISTINCT
    computers.ComputerID,
    computers.Name AS `Computer Name`,
    computers.Username AS Username,
    computers.LastContact,
    computers.VirusScanner,
    computers.OS,
    v_extradataclients.`Disable ESET Check` AS `ExtraDataClients Disable ESET Check`,
    v_extradatalocations.`Disable ESET Check` AS `ExtraDataLocations Disable ESET Check`,
    v_extradatacomputers.`Disable ESET Check` AS `ExtraDataComputers  Disable ESET Check`,
    v_extradatalocations.`Disable SentinelOne Check` AS `ExtraDataLocations  Disable SentinelOne Check`,
    v_extradataclients.`Disable SentinelOne Check` AS `ExtraDataClients  Disable SentinelOne Check`,
    v_extradatacomputers.`Disable SentinelOne Check` AS `ExtraDataComputers  Disable SentinelOne Check`
FROM computers 
    INNER JOIN v_extradatacomputers ON computers.ComputerID = v_extradatacomputers.computerid 
    INNER JOIN locations ON computers.LocationID = locations.LocationID
        AND computers.ClientID = locations.ClientID 
    INNER JOIN v_extradatalocations ON locations.LocationID = v_extradatalocations.locationid
        AND computers.LocationID = v_extradatalocations.locationid 
    INNER JOIN v_extradataclients ON computers.ClientID = v_extradataclients.clientid 
    INNER JOIN clients ON clients.ClientID = v_extradataclients.clientid
        AND computers.ClientID = clients.ClientID
WHERE
(
    (v_extradatalocations.`Disable ESET Check` = 1 AND 
    v_extradatalocations.`Disable SentinelOne Check` <> 1
    )
OR (v_extradataclients.`Disable ESET Check` = 1 AND 
    v_extradataclients.`Disable SentinelOne Check` <>  1
    )
OR (v_extradatacomputers.`Disable ESET Check` = 1 AND 
    v_extradatacomputers.`Disable SentinelOne Check` <>  1
    )
OR (v_extradataclients.`Disable ESET Check` = 1 AND 
    v_extradatacomputers.`Disable ESET Check` = 1 AND 
    v_extradataclients.`Disable SentinelOne Check` <> 1
    )
OR (v_extradataclients.`Disable ESET Check` = 1 AND 
    v_extradatacomputers.`Disable ESET Check` = 1 AND 
    v_extradatalocations.`Disable ESET Check` = 1  AND 
    v_extradatalocations.`Disable SentinelOne Check` = 0
    )
)
AND computers.LastContact > (NOW() - INTERVAL 15 MINUTE)
AND computers.VirusScanner NOT IN (312, 313)

原因分析 #

这两个条件被忽略的核心是逻辑优先级覆盖：前面的宽泛OR条件已经包含了后面组合条件的匹配场景，数据库会优先命中前面的条件，导致后面的组合规则永远不会被触发。

比如第四个条件（客户端+计算机的ESET禁用、客户端SentinelOne未禁用），已经被第二个条件（客户端ESET禁用、客户端SentinelOne未禁用）覆盖——只要第二个条件满足，无论计算机的ESET状态如何，数据都会被筛选出来，第四个条件失去生效机会。

第五个条件同理，若客户端ESET禁用且SentinelOne未禁用，会被第二个条件提前命中；若客户端SentinelOne已禁用，第五个条件里的位置SentinelOne未禁用也无法抵消冲突，导致这个条件永远无法触发。

解决方案 #

要让组合条件生效，需调整逻辑顺序，将更具体的组合条件放在最前面，让数据库优先匹配精确规则，再处理宽泛规则：

SELECT DISTINCT
    computers.ComputerID,
    computers.Name AS `Computer Name`,
    computers.Username AS Username,
    computers.LastContact,
    computers.VirusScanner,
    computers.OS,
    v_extradataclients.`Disable ESET Check` AS `ExtraDataClients Disable ESET Check`,
    v_extradatalocations.`Disable ESET Check` AS `ExtraDataLocations Disable ESET Check`,
    v_extradatacomputers.`Disable ESET Check` AS `ExtraDataComputers  Disable ESET Check`,
    v_extradatalocations.`Disable SentinelOne Check` AS `ExtraDataLocations  Disable SentinelOne Check`,
    v_extradataclients.`Disable SentinelOne Check` AS `ExtraDataClients  Disable SentinelOne Check`,
    v_extradatacomputers.`Disable SentinelOne Check` AS `ExtraDataComputers  Disable SentinelOne Check`
FROM computers 
    INNER JOIN v_extradatacomputers ON computers.ComputerID = v_extradatacomputers.computerid 
    INNER JOIN locations ON computers.LocationID = locations.LocationID
        AND computers.ClientID = locations.ClientID 
    INNER JOIN v_extradatalocations ON locations.LocationID = v_extradatalocations.locationid
        AND computers.LocationID = v_extradatalocations.locationid 
    INNER JOIN v_extradataclients ON computers.ClientID = v_extradataclients.clientid 
    INNER JOIN clients ON clients.ClientID = v_extradataclients.clientid
        AND computers.ClientID = clients.ClientID
WHERE
(
    -- 优先匹配最具体的组合条件
    (v_extradataclients.`Disable ESET Check` = 1 AND 
    v_extradatacomputers.`Disable ESET Check` = 1 AND 
    v_extradatalocations.`Disable ESET Check` = 1  AND 
    v_extradatalocations.`Disable SentinelOne Check` = 0
    )
OR (v_extradataclients.`Disable ESET Check` = 1 AND 
    v_extradatacomputers.`Disable ESET Check` = 1 AND 
    v_extradataclients.`Disable SentinelOne Check` <> 1
    )
-- 再匹配宽泛的单一维度条件
OR (v_extradatalocations.`Disable ESET Check` = 1 AND 
    v_extradatalocations.`Disable SentinelOne Check` <> 1
    )
OR (v_extradataclients.`Disable ESET Check` = 1 AND 
    v_extradataclients.`Disable SentinelOne Check` <>  1
    )
OR (v_extradatacomputers.`Disable ESET Check` = 1 AND 
    v_extradatacomputers.`Disable SentinelOne Check` <>  1
    )
)
AND computers.LastContact > (NOW() - INTERVAL 15 MINUTE)
AND computers.VirusScanner NOT IN (312, 313)

也可以通过逻辑排除优化宽泛条件，比如修改第二个条件，排除已被组合条件覆盖的场景：

OR (v_extradataclients.`Disable ESET Check` = 1 AND 
    v_extradataclients.`Disable SentinelOne Check` <>  1 AND
    -- 排除客户端+计算机同时禁用ESET的场景
    NOT (v_extradatacomputers.`Disable ESET Check` = 1)
    )

优先推荐调整条件顺序的方案，逻辑更清晰，性能更优。

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