为什么我的MySQL查询返回带有IN值的错误数据[重复]

Question

This question already has an answer here:

• AND OR causing more results to appear than should 4 answers

I am running a query in PHP and I am getting to many results. Here is the query:

mysql> SELECT `location` FROM `listings` WHERE `fulltext` LIKE '%a%' OR `title` LIKE
'%a%' AND location IN(33702,33709,33714,33716,33760,33762,33771,33773,33777,33781,33782)
ORDER BY `date` DESC \g

and this is the CMD result that I get, which matches exactly what I am getting in the PHP run:

+----------+
| location |
+----------+
|    78787 |
|    33782 |
|    33782 |
|    98102 |
|    33782 |
|    33782 |
|    33782 |
|    33782 |
|    33782 |
|    56564 |
|    44569 |
|    33782 |
|    33782 |
+----------+
13 rows in set (0.00 sec)

As you can see, I should not be getting 78787,98102,56564 or 44569. does anyone see what could be wrong with the query. Let me know if you need more info.

P.S. I originally had this set up to use RLIKE, and it will be again once I get this figured out.

</div>

Answer 1

It looks like the precedence of your OR is incorrect. Try:

SELECT `location` FROM `listings` WHERE (`fulltext` LIKE '%a%' OR `title` LIKE '%a%') AND location IN(33702,33709,33714,33716,33760,33762,33771,33773,33777,33781,33782) ORDER BY `date` DESC

The important thing here is to use brackets to separate your two basic conditions:

1. check title OR fulltext
2. check location