MySQL 查询传递不需要的数据

Question

我正在尝试使用以下查询搜索我的数据库:

SELECT jobunique, jobtypeunique, joblat, joblng FROM job WHERE

        jobdeleted = 0

        AND

        jobpayed = 1

        AND

        (LOWER(jobtitle) LIKE '%taxi%'
        OR LOWER(jobmaincategory) LIKE '%taxi%'
        OR LOWER(jobsubcategory) LIKE '%taxi%'
        OR LOWER(jobdescription) LIKE '%taxi%'
        OR LOWER(jobcountry) LIKE '%taxi%')

        AND

        (
            jobbusiness = 1 AND jobstartdate <= CURDATE() AND jobenddate >= CURDATE()
        ) OR
        ( 
            jobbusiness = 0 AND jobfixedstartdate = 0 AND jobfixedenddate = 0 AND jobdateadded >= (CURDATE()-INTERVAL 60 DAY) 
        ) OR
        (
            jobbusiness = 0 AND jobfixedstartdate = 1 AND jobfixedenddate = 0 AND jobstartdate >= CURDATE()
        ) OR
        (
            jobbusiness = 0 AND jobfixedstartdate = 1 AND jobfixedenddate = 1 AND jobenddate <= CURDATE()
        )

但无论其中一个字段是否有 “taxi”，我都会收到 jobbusiness = 0 的所有行。有人可以向我解释为什么吗？谢谢

Answer 1

您需要在最后一组 OR 条件周围加上一组额外的括号，否则最后 3 个条件只会独立于其余条件进行 ORed。请注意，您不需要围绕 AND 条件使用 ()，因为 AND 无论如何都优先于 OR。将 WHERE 条件的最后一部分更改为此应该可以解决问题:

AND (
        jobbusiness = 1 AND jobstartdate <= CURDATE() AND jobenddate >= CURDATE()
    OR
        jobbusiness = 0 AND jobfixedstartdate = 0 AND jobfixedenddate = 0 AND jobdateadded >= (CURDATE()-INTERVAL 60 DAY) 
    OR
        jobbusiness = 0 AND jobfixedstartdate = 1 AND jobfixedenddate = 0 AND jobstartdate >= CURDATE()
    OR
        jobbusiness = 0 AND jobfixedstartdate = 1 AND jobfixedenddate = 1 AND jobenddate <= CURDATE()
)