Mysql 或/和优先级?

2021-11-20 00:00:00 operator-precedence mysql

我想知道 or/and 是如何工作的?

I am wondering how or/and works?

例如，如果我想获取 display = 1 的所有行

For example if I want to get all rows where display = 1

我只能做WHERE tablename.display = 1

如果我想要 display = 1 或 2 的所有行

and if I want all rows where display = 1 or 2

我可以做WHERE tablename.display = 1 or tablename.display = 2

但是，如果我想获取 display = 1 或 2 以及 任何 内容、标签或标题包含 hello world

But what if I want to get all rows where display = 1 or 2 and where any of the content, tags, or title contains hello world

这个逻辑将如何发挥作用?

How would the logic play out for that?

Select * from tablename 
where display = 1 or display = 2 and content like "%hello world%" or tags like "%hello world%" or title = "%hello world%"

这是我的猜测.但我可以通过多种方式阅读.

Would be my guess. but then I can read that in several ways.

是否读出为:

 (display = 1 or display = 2) and (content like "%hello world%" or tags like "%hello world%" or title = "%hello world%")

或作为

((display = 1 or display = 2) and (content like "%hello world%")) or (tags like "%hello world%" or title = "%hello world%")

等

推荐答案

MySQL 文档有一个 良好页面，其中包含有关哪些运算符优先的信息.

The MySQL documentation has a good page with information on which operators take precedence.

从那个页面，

12.3.1.运算符优先级

12.3.1. Operator Precedence

运算符优先级如下表所示，从最高优先级到最低优先级.运营商一起显示在一行中具有相同的优先级.

Operator precedences are shown in the following list, from highest precedence to the lowest. Operators that are shown together on a line have the same precedence.

INTERVAL
BINARY, COLLATE
!
- (unary minus), ~ (unary bit inversion)
^
*, /, DIV, %, MOD
-, +
<<, >>
&
|
= (comparison), <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
BETWEEN, CASE, WHEN, THEN, ELSE
NOT
&&, AND
XOR
||, OR
= (assignment), :=

所以你的原始查询

Select
    *
from tablename 
where
    display = 1
    or display = 2
    and content like "%hello world%"
    or tags like "%hello world%"
    or title = "%hello world%"

会被解释为

Select
    *
from tablename 
where 
    (display = 1)
    or (
        (display = 2)
        and (content like "%hello world%")
    )
    or (tags like "%hello world%")
    or (title = "%hello world%")

如有疑问，请使用括号来明确您的意图.尽管 MySQL 页面上的信息很有帮助，但如果再次访问该查询，则可能不会立即显而易见.

When in doubt, use parenthesis to make your intent clear. While the information on the MySQL page is helpful, it may not be immediately obvious if the query is ever revisited.

您可能会考虑以下内容.请注意，我已将 title = "%hello world%" 更改为 title like "%hello world%"，因为这更符合您描述的目标.

You might consider something like the following. Note that I've changed the title = "%hello world%" to title like "%hello world%", since that fits better with the goal you've described.

Select
    *
from tablename 
where
    (
        (display = 1)
        or (display = 2)
    ) and (
        (content like "%hello world%")
        or (tags like "%hello world%")
        or (title like "%hello world%")
    )

相关文章