我对以下带有一些逻辑门的MySQL查询有问题

Question

下面的查询给我带来了一些问题。下面的查询应该返回两条记录，但由于某种原因，它返回了四条，另外两条是customer_id不为"的记录；420〃；如有任何帮助，将不胜感激

SELECT * FROM search_record LEFT JOIN customers ON search_record.customer_id = customers.customer_id LEFT JOIN customer_company ON customers.company_id = customer_company.company_id 
WHERE (search_record.customer_id = '420')
AND (search_id like '%165%') 
OR (identity_code LIKE '%165%')
OR status LIKE '%165%'
OR comments LIKE '%165%'
OR county LIKE '%165%'
OR property_address1 LIKE '%165%'
OR property_address2 LIKE '%165%'
OR property_town LIKE '%165%'
OR postcode LIKE '%165%'
OR requester LIKE '%165%'
OR company_name LIKE '%165%'
OR customer_ref LIKE '%165%' 
ORDER BY search_id DESC

Answer 1

AND的优先级高于OR，因此您的任何OR。。。单独的条件将导致记录被选择。在你想要的条件周围加括号或者：

SELECT * FROM search_record LEFT JOIN customers ON search_record.customer_id = customers.customer_id LEFT JOIN customer_company ON customers.company_id = customer_company.company_id 
WHERE (search_record.customer_id = '420')
AND (
search_id like '%165%' 
OR identity_code LIKE '%165%'
OR status LIKE '%165%'
OR comments LIKE '%165%'
OR county LIKE '%165%'
OR property_address1 LIKE '%165%'
OR property_address2 LIKE '%165%'
OR property_town LIKE '%165%'
OR postcode LIKE '%165%'
OR requester LIKE '%165%'
OR company_name LIKE '%165%'
OR customer_ref LIKE '%165%' 
)
ORDER BY search_id DESC