Mysql or/and precedence?
Solution 1
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%")
)
Solution 2
Run this query:
select 1 or 1 and 0
If it comes out as 1
, then that means the precedence is:
select 1 or (1 and 0)
if it comes out 0
, then the precedence is:
select (1 or 1) and 0
Spoiler: it comes out 1
That is to say, AND
s are evaluated before OR
s, or as I like to say, ANDs are stickier.
Solution 3
You need to use brackets for your multiple OR
conditions. And for display = 1 OR display = 2
you can use display IN(1,2)
. Try this:
SELECT * FROM tableName
WHERE display IN (1,2)
AND (content LIKE "%hello world%"
OR tags LIKE "%hello world%"
OR title LIKE "%hello world%")
For more info look at MySQL: Operator Precedence
Related videos on Youtube
Hailwood
I could tell you all about me... but I'd prefer to let my work do the talking for me!
Updated on September 01, 2020Comments
-
Hailwood over 3 years
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.
-
zerkms over 11 years
-
Salman A over 11 yearsThese things are taught during your early C/C++ classes.
-
Hailwood over 11 yearsThen I guess I should sign up for an entire C/C++ course just to learn the operator precedence :/
-
-
Hailwood over 11 yearsPlease see my addition to my question. it's in relation to not having brackets how does it play out?