Mysql or/and precedence?

by

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

More Related Contents:

  1. MySQL query / clause execution order
  2. Why the order of evaluation for expressions involving user variables is undefined?
  3. How do you set a default value for a MySQL Datetime column?
  4. Compare two MySQL databases [closed]
  5. MySQL – Trigger for updating same table after insert
  6. MySQL date format DD/MM/YYYY select query?
  7. SQL split comma separated row [duplicate]
  8. Does MySQL ignore null values on unique constraints?
  9. MySQL Foreign Key Error 1005 errno 150 primary key as foreign key
  10. What does the KEY keyword mean?
  11. MySQL – Subtracting value from previous row, group by
  12. How to select from MySQL where Table name is Variable
  13. How to store a datetime in MySQL with timezone info
  14. MySQL utf8mb4, Errors when saving Emojis
  15. How to store unicode in MySQL?
  16. mysql after insert trigger which updates another table’s column
  17. Error Code: 1406. Data too long for column – MySQL
  18. Search for string within text column in MySQL
  19. Error renaming a column in MySQL
  20. Mysql database sync between two databases
  21. Is there a simple way to convert MySQL data into Title Case?
  22. How to pass parameters to mysql query callback in nodejs
  23. Clear MySQL query cache without restarting server
  24. Mysql select recursive get all child with multiple level
  25. Does mysql have the equivalent of Oracle’s “analytic functions”?
  26. MySQL Error: Incorrect usage of UPDATE and LIMIT
  27. Mysql insert into 2 tables
  28. How do you stop MySQL on a Mac OS install?
  29. Replace null with 0 in MySQL
  30. Count duplicates records in Mysql table?

Leave a Comment