php - MySQL LIKE 语句返回奇数结果

Question

当我使用 AND 和 OR 对多列执行 LIKE MySQL 查询时，我在查询中的过滤器结果(例如按 Price 0 AND 50000 停止工作)。如果我从我的 LIKE 语句中删除 OR ，搜索就可以正常工作。如何使用 LIKE 语句正确搜索多个列？

这是一个忽略价格的无工作语句的例子

SELECT * 
  FROM table 
 WHERE Price BETWEEN 0 AND 500000 
   AND class_id = 1 
   AND TotalSqFt BETWEEN 0 AND 999999999 
   AND Acres BETWEEN 0 AND 999999999 
   AND InclusionsFeatures LIKE '%horse%' 
    OR Remarks LIKE '%horse%' 
ORDER 
    BY Price DESC

此语句工作正常，但现在我只能对一列执行 LIKE 查询。

SELECT * FROM table WHERE Price BETWEEN 0 AND 500000 AND class_id = 1 AND TotalSqFt BETWEEN 0 AND 999999999 AND Acres BETWEEN 0 AND 999999999 AND InclusionsFeatures LIKE '%horse%' ORDER BY Price DESC

Answer 1

在第一个语句中，当您在最后一个 AND 之后添加 OR 时，Mysql 会一直执行直到它运行 OR。如果 OR 条件通过，则 AND 之前的所有内容将被忽略。因此查询无法正确运行。

看看这个查询:

SELECT
    * 
FROM
TABLE 
WHERE
    Price BETWEEN 0 
    AND 500000 
    AND class_id = 1 
    AND TotalSqFt BETWEEN 0 
    AND 999999999 
    AND Acres BETWEEN 0 
    AND 999999999 
    AND (InclusionsFeatures LIKE '%horse%' OR Remarks LIKE '%horse%' ) 
ORDER BY
    Price DESC

通过用括号将您的“喜欢”分组，它会检查括号内的条件与所有其他带有 AND 的条件。所以查询返回数据匹配括号内的条件和所有其他条件。