« The kind of emerge report I like to see | Main | Creating Time-Sensitive Sample Data »

December 5, 2005

MySQL Operator Precedence

Now that we're all going crazy with MySQL 5 and programming logic in the database it's probably worth a quick review of operator precedence in MySQL. I've listed the operator precedence highest to lowest (the MySQL docs do the opposite). Operators on the same line have the same precedence:

BINARY, COLLATE
!
- (unary minus), ~ (unary bit inversion)
^
*, /, DIV, %, MOD
-, +
<<, >>
&
|
=, <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
BETWEEN, CASE, WHEN, THEN, ELSE
NOT
&&, AND
||, OR, XOR
:=

And just for kicks I'll throw in the Perl operator precedence to see the precedence of the operators I'm most familiar with (never hurts to review):

terms and list operators (leftward)
->
++, --
**
!, ~, \, unary +, unary -
=~, !~
*, /, %, x
+, -, .
<<, >>
named unary operators
<, >, <=, >=, lt, gt, le, ge
==, !=, <=>, eq, ne, cmp
&
|, ^
&&
||
..,  ...
?:
=, +=, -=, *=
,, =>
list operators (rightward)
not
and
or, xor

Might as well put pointers to operator precedence for Python, Ruby, PHP and C.

During my career I haven't done much of what I would consider calculation-heavy programming. Even so, I think it's always good to be sure even during a simple calculation that the evaluation happens in the expected order. To be explicit, and perhaps more readable, use of () to denote precedence is probably a good idea in anything more than the blindingly simple.

Posted by mike at December 5, 2005 10:40 PM