How Does MySQL Evaluate `OR` and `AND` Operators in `WHERE` Clauses?

MySQL OR/AND Precedence

MySQL's logical operators have specific precedence rules that dictate how multiple conditions are evaluated in a query. Understanding these rules is crucial for crafting efficient and accurate queries.

Regarding your query, you want to retrieve rows where display is 1 or 2 and where any of content, tags, or title contains "hello world." The precedence rules for MySQL operators are:

(highest 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), :=
(lowest precedence)

With these rules in mind, let's analyze your query:

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

According to the precedence rules, the logical operators are evaluated within parentheses:

• Inner parentheses: (display = 1) or (display = 2) ensures that rows with display 1 or 2 will be retrieved.
• Outer parentheses: ((display = 1) or (display = 2)) and (content like "%hello world%") or (tags like "%hello world%") or (title like "%hello world%") defines the conditions for matching "hello world."

Due to the precedence of OR over AND, the query will be interpreted as follows:

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

Therefore, the query will retrieve rows where:

• display is 1 or 2 and
• any of content, tags, or title contains "hello world."

To avoid confusion, it's recommended to use parentheses to explicitly indicate the desired evaluation order. For instance, the following query will retrieve rows where either display is 1 or 2, or where any of content, tags, or title contains "hello world":

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

The above is the detailed content of How Does MySQL Evaluate `OR` and `AND` Operators in `WHERE` Clauses?. For more information, please follow other related articles on the PHP Chinese website!