MySQL WHERE LIKE AND OR

MySQL WHERE LIKE AND OR

提问by TV-C-15

I have a database app I'm working on for car products.

我有一个用于汽车产品的数据库应用程序。

I want the returned products results to match the conditions of:

我希望返回的产品结果符合以下条件：

• The 'MAKE' MUST match
• The 'CATEGORY' MUST match
• But when it gets to the 'OPTIONS', It can be either or all. I want it to return products that have either one or all of the options selected

• 'MAKE' 必须匹配
• 'CATEGORY' 必须匹配
• 但是当它进入“选项”时，它可以是其中之一，也可以是全部。我希望它返回选择了一个或所有选项的产品

I am using:

我在用：

SELECT *
FROM `PRODUCTS`
WHERE `MAKE` LIKE '%CHEV%'
AND `CATEGORY` LIKE '%BUMPERS%'
AND `OPTIONS` LIKE '%TOW HOOKS%'
OR `OPTIONS` LIKE '%LIGHT MOUNT HOLES%'

When I use this it works but it is producing results from different MAKES as well. So it is returning products from lets say FORD as well... I don't want that.

当我使用它时，它可以工作，但它也会产生来自不同 MAKES 的结果。所以它也从福特那里退回产品......我不想要那个。

I want the MAKE and CATEGORY to be strict (they must match) but the OPTIONS can be one or both - or maybe even three or four different options - but at least one option must match.

我希望 MAKE 和 CATEGORY 严格（它们必须匹配），但 OPTIONS 可以是一个或两个 - 甚至可能是三四个不同的选项 - 但至少一个选项必须匹配。

The OPTIONSfiled is a string separated by pipe characters i.e. LIGHT MOUNT HOLES|TOW HOOKS|FOUR DOOR, and I don't want to change this.

该OPTIONS文件是一个由管道字符 ie 分隔的字符串LIGHT MOUNT HOLES|TOW HOOKS|FOUR DOOR，我不想改变它。

If I use the above query with just one OPTION in the search:

如果我在搜索中仅使用一个 OPTION 的上述查询：

 AND `OPTIONS` LIKE '%TOW HOOKS%'" or just "AND `OPTIONS` LIKE '%LIGHT MOUNT HOLES%'

... it works perfectly. However, if I try to search for multiple OPTIONS, it starts bringing back products from different MAKES.

...它完美地工作。但是，如果我尝试搜索多个 OPTIONS，它会开始带回来自不同 MAKES 的产品。

I have also tried:

我也试过：

SELECT *
FROM `PRODUCTS`
WHERE `MAKE` LIKE '%CHEV%'
AND `CATEGORY` LIKE '%BUMPERS%'
OR `OPTIONS` LIKE '%TOW HOOKS%'
OR `OPTIONS` LIKE '%LIGHT MOUNT HOLES%'

And have the same problem of multiple MAKES showing up.

并且有多个 MAKES 出现的相同问题。

回答by Isaac

SELECT *
FROM `PRODUCTS`
WHERE `MAKE` LIKE '%CHEV%'
AND `CATEGORY` LIKE '%BUMPERS%'
AND (`OPTIONS` LIKE '%TOW HOOKS%'
    OR `OPTIONS` LIKE '%LIGHT MOUNT HOLES%'
    OR ...)

This will match at least one option.

这将匹配至少一个选项。

If you also want to return cars with NO options matched use:

如果您还想返回没有匹配选项的汽车，请使用：

SELECT *
FROM `PRODUCTS`
WHERE `MAKE` LIKE '%CHEV%'
AND `CATEGORY` LIKE '%BUMPERS%'

Edit

编辑

Explanation: the difference between this and what you have is that all the LIKE clauses are grouped together. Which means that they are in essence just one condition next to the make and the category. the thing you need to get your head around is that logical AND/OR are not the same as we use them in English. In your query if a car had options like '%LIGHT MOUNT HOLES%' then the condition is met.

解释：这和你所拥有的不同之处在于所有 LIKE 子句都组合在一起。这意味着它们本质上只是品牌和类别旁边的一个条件。您需要弄清楚的是，逻辑 AND/OR 与我们在英语中使用的不同。在您的查询中，如果汽车具有“%LIGHT MOUNT HOLES%”等选项，则满足条件。

Let M (make), C (category), O1 (option1), O2 (option2) denote the 4 clauses you have in your query, and R be the result of evaluating them. Then R = M AND C AND O1 OR O2. if O2 is true, then M, C and O1 are irrelevant. becuase by definition X = A OR B is true if either A or B is true. what you want is R = M AND C AND (O1 OR O2 OR ...). this way R is true only if all of M, C and at least one of the Os is true.

令 M (make), C (category), O1 (option1), O2 (option2) 表示您在查询中的 4 个子句，而 R 是评估它们的结果。然后 R = M AND C AND O1 OR O2。如果 O2 为真，则 M、C 和 O1 无关紧要。因为根据定义，如果 A 或 B 为真，则 X = A OR B 为真。你想要的是 R = M AND C AND (O1 OR O2 OR ...)。这样，仅当所有 M、C 和至少一个 O 为真时，R 才为真。

回答by Niet the Dark Absol

What you are doing is comparable to having:

你正在做的事情相当于：

3 * 4 + 5

and expecting 27. But because of the order of precedence of operators, the answer is 17.

并期待27。但由于运算符的优先顺序，答案是17。

You can "fix" this and get 27like so:

你可以“修复”这个并得到27这样的：

3 * (4 + 5)

The exact same applies to many programming languages, and MySQL is no exception.

这同样适用于许多编程语言，MySQL 也不例外。

Put your ORoptions in parentheses so they get treated as a group.

将您的OR选项放在括号中，以便将它们视为一个组。

回答by malexmave

My educated guess would be that it is the fault of the OR, because it evaluates like (this AND that AND that AND that) OR something. You could confirm that by checking if all returning different makes contain the criteria from the OR.

我有根据的猜测是它的错OR，因为它评估为(this AND that AND that AND that) OR something. 您可以通过检查所有返回的不同品牌是否包含来自OR.

As for solving it: You can use brackets: this AND that AND (that OR something_else).

至于解决它：您可以使用括号：this AND that AND (that OR something_else)。