mysql 读写优先级,MySQL或/和优先级？

I am wondering how or/and works?

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

I can just do WHERE tablename.display = 1

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

I can just do WHERE tablename.display = 1 or tablename.display = 2

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.

Does it read out as:

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

or as

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

etc.

解决方案

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

From that page,

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), :=

So your original query

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 interpreted as

Select

*

from tablename

where

(display = 1)

or (

(display = 2)

and (content like "%hello world%")

)

or (tags like "%hello world%")

or (title = "%hello world%")

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.

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%")

)