MySQL多OR与AND组合条件失效：Like/Not Like混用异常

问题根源 #

MySQL中AND运算符优先级高于OR。你的第二个查询里，所有source的OR条件未用括号包裹，导致只有最后一个OR条件（source like '%May Day Tour%'）会和后续的AND过滤规则绑定，前面的所有OR条件完全不受source not like '%tree%'以及item_category相关限制的约束，所以不符合要求的行依然会被查询出来。

修正后的查询 #

将所有source的OR条件用括号包裹，让它们作为一个整体，再与后面的AND条件组合，就能实现预期的过滤效果：

SELECT *
FROM animal_crossing_new_horizons.item
WHERE
(
`source` like '%nook miles%' or
`source` like '%nook shopping%' or
`source` like '%saharah%' or
`source` like '%kicks%' or
`source` like '%nook''s cranny%' or
`source` like '%able sisters%' or
`source` like '%crafting%' or
`source` like '%k.k. concert%' or
`source` like '%redd%' or
`source` like '%flick%' or
`source` like '%c.j.%' or
`source` like '%celeste%' or
`source` like '%hpp apparel shop%' or
`source` like '%assessing fossils%' or
`source` like '%cooking%' or
`source` like '%high friendship%' or
`source` like '%dig spot%' or
`source` like '%label%' or
`source` like '%pavé%' or
`source` like '%gulliver%' or
`source` like '%gullivarrr%' or
`source` like '%pascal%' or
`source` like '%brewster%' or
`source` like '%zipper%' or
`source` like '%cyrus%' or
`source` like '%harvey%' or
`source` like '%rover%' or
`source` like '%jack%' or
`source` like '%franklin%' or
`source` like '%jingle%' or
`source` like '%blathers%' or
`source` like '%nooklink%' or
`source` like '%donating%' or
`source` like '%birthday%' or
`source` like '%isabelle%' or
`source` like '%lottie%' or
`source` like '%Starting items%' or
`source` like '%Dodo Airlines%' or
`source` like '%Group Stretching%' or
`source` like '%Nintendo%' or
`source` like '%Wilbur%' or
`source` like '%Kapp''n%' or
`source` like '%HHA%' or
`source` like '%Check Toy Day stockings the day after Toy Day%' or
`source` like '%Luna%' or
`source` like '%Katrina''s Cleansing Service%' or
`source` like '%Mom%' or
`source` like '%Tom Nook%' or
`source` like '%Niko%' or
`source` like '%Recycle box%' or
`source` like '%All villagers%' or
`source` like '%Wardell%' or
`source` like '%Cyrus%' or
`source` like '%May Day Tour%'
) and
`source` not like '%tree%' and
`item_category` not like '%recipes%' and
`item_category` not like '%construction%' and
`item_category` not like '%reactions%' and
`item_category` not like '%achievements%'
ORDER BY item.name, item.variant_id, item.sort_order

验证逻辑 #

修正后，查询会先筛选出满足任意一个source条件的行，再对这些行应用source not like '%tree%'和item_category的过滤规则，最终得到符合要求的结果。

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