How Does Operator Precedence Affect MySQL's `OR` and `AND` Logic in Queries?

MySQL OR/AND Precedence

In MySQL, the order of operations for logical operators, such as OR and AND, determines the interpretation of complex queries. This article explains the precedence rules and how they impact query results.

Explanation

The MySQL documentation provides a comprehensive list of operator precedence, as follows:

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

According to this precedence hierarchy, the following query 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%"

Interpretation

The above query seeks rows where either:

• display equals 1
• display equals 2 and content contains "hello world"
• tags contain "hello world"
• title contains "hello world"

The WHERE clause is evaluated in the following order:

1. display is checked first.
2. If display is not 1, then the second subquery is evaluated.
3. Within the subquery, display is checked for equality with 2. If true, content is checked for "hello world."
4. Finally, the remaining clauses for tags and title are evaluated, with an OR operator linking them.

Precedence Issues and Solutions

To ensure unambiguous interpretation, it is recommended to use parentheses explicitly. For example:

SELECT *
FROM tablename
WHERE
    ((display = 1) OR (display = 2)) AND
    ((content LIKE "%hello world%") OR (tags LIKE "%hello world%") OR (title LIKE "%hello world%"))

This query ensures that rows are fetched if they meet any of the following conditions:

• display equals 1 or 2
• content, tags, or title contain "hello world"

The above is the detailed content of How Does Operator Precedence Affect MySQL's `OR` and `AND` Logic in Queries?. For more information, please follow other related articles on the PHP Chinese website!