Operators
Operators are used to combine terms in expressions to perform arithmetic, compare values, perform bitwise or logical operations, and match patterns.
Operator Precedence
Operators have varying levels of precedence. The levels are shown in the following list, from highest to lowest. Operators on the same line have the same precedence. Operators at a given precedence level are evaluated left to right. Operators at a higher precedence level are evaluated before operators at a lower precedence level.
BINARY NOT ! - (unary minus) * / % + - << >> & | < <= = <=> != <> >= > IN IS LIKE REGEXP RLIKE BETWEEN AND && OR ||
The unary operators (unary minus, NOT, and BINARY) bind more tightly than the binary operators. That is, they group with the immediately following term in an expression, not with the rest of the expression as a whole.
Grouping Operators
Parentheses—( and )—may be used to group parts of an expression to override the default operator precedence that determines the order in which terms of an expression are evaluated (see "Operator Precedence"). Parentheses may also be used simply for visual clarity, to make an expression more readable.
1 + 2 * 3 / 4 → 2.50 (((1 + 2) * 3) / 4) → 2.25
Arithmetic Operators
These operators perform standard arithmetic. The arithmetic operators work on numbers, not strings (although strings that look like numbers are converted automatically to the corresponding numeric value). Arithmetic involving NULL values produces a NULL result.
+
Evaluates to the sum of the arguments.
2 + 2 → 4 3.2 + 4.7 → 7.9 "43bc" + "21d" → 64 "abc" + "def" → 0
The final example in this listing shows that '+' does not serve as the string concatenation operator the way it does in some languages. Instead, the strings are converted to numbers before the arithmetic operation takes place. Strings that don't look like numbers are converted to 0. Use the CONCAT() function to concatenate strings.
-
Evaluates to the difference of the operands when used between two terms of an expression. Evaluates to the negative of the operand when used in front of a single term (that is, it flips the sign of the term).
10 - 7 → 3 -(10 - 7) → -3
*
Evaluates to the product of the operands.
2 * 3 → 6 2.3 * -4.5 → -10.3
/
Evaluates to the quotient of the operands. Division by zero produces a NULL result.
3 / 1 → 3.00 1 / 3 → 0.33 1 / 0 → NULL
%
The modulo operator; evaluates to the remainder of m divided by n. m % n is the same as MOD( m , n ). As with division, the modulo operator with a divisor of zero returns NULL.
12 % 4 → 0 12 % 5 → 2 12 % 0 → NULL
For '+', '-', and ' * ', arithmetic is performed with BIGINT values (64-bit integers) if both arguments are integers. This means expressions involving large values might exceed the range of 64-bit integer calculations, with unpredictable results:
999999999999999999 * 999999999999999999 → -7527149226598858751 99999999999 * 99999999999 * 99999999999 → -1504485813132150785 18014398509481984 * 18014398509481984 → 0
For '/', and '%', BIGINT values are used only when the division is performed in a context where the result is converted to an integer.
Comparison Operators
Comparison operators return 1 if the comparison is true and 0 if the comparison is false. You can compare numbers or strings. Operands are converted as necessary according to the following rules:
With the exception of the '<=>' operator, comparisons involving NULL values evaluate as NULL. ('<=>' is like '=', except that NULL <=> NULL is true.)
If both operands are strings, they are compared lexicographically as strings. String comparisons are performed using the character set in force on the server.
If both operands are integers, they are compared numerically as integers.
Hexadecimal constants that are not compared to a number are compared as binary strings.
If either operand is a TIMESTAMP or DATETIME value and the other is a constant, the operands are compared as TIMESTAMP values. This is done to make comparisons work better for ODBC applications.
If none of the preceding rules apply, the operands are compared numerically as floating-point values. Note that this includes the case of comparing a string and a number. The string is converted to a number, which results in a value of 0 if the string doesn't look like a number. For example, " 14.3 " converts to 14.3, but " L4.3 " converts to 0.
The following comparisons illustrate these rules:
2 < 12 → 1 "2" < "12" → 0 "2" < 12 → 1
The first comparison involves two integers, which are compared numerically. The second comparison involves two strings, which are compared lexicographically. The third comparison involves a string and a number, so they are compared as floating-point values.
String comparisons are not case sensitive unless the comparison involves a binary string. Thus, a case-sensitive comparison is performed if you use the BINARY keyword or are comparing values from CHAR BINARY, VARCHAR BINARY, or BLOB columns.
=
Evaluates to 1 if the operands are equal, 0 otherwise.
1 = 1 → 1 1 = 2 → 0 "abc" = "abc" → 1 "abc" = "def" → 0 "abc" = "ABC" → 1 BINARY "abc" = "ABC" → 0 BINARY "abc" = "abc" → 1 "abc" = 0 → 1
" abc " is equal to both " abc " and " ABC " because string comparisons are not case sensitive by default. String comparisons can be made case sensitive by using the BINARY operator. " abc " is equal to 0 because it's converted to a number in accordance to the comparison rules. Because " abc " doesn't look like a number, it's converted to 0.
<=>
NULL-safe equality operator; it's similar to '=', except that it evaluates to 1 when the operands are equal, even when they are NULL.
1 <=> 1 → 1 1 <=> 2 → 0 NULL <=> NULL → 1 NULL = NULL → NULL
The final two examples show how '=' and '<=>' handle NULL comparisons differently.
'<=>' was introduced in MySQL 3.23.0.
!= or <>
Evaluates to 1 if the operands are unequal, 0 otherwise.
3.4 != 3.4 → 0 "abc" <> "ABC" → 0 BINARY "abc" <> "ABC" → 1 "abc" != "def" → 1
<
Evaluates to 1 if the left operand is less than the right operand, 0 otherwise.
3 < 10 → 1 105.4 < 10e+1 → 0 "abc" < "ABC" → 0 "abc" < "def" → 1
<=
Evaluates to 1 if the left operand is less than or equal to the right operand, 0 otherwise.
"abc" <= "a" → 0 "a"<= "abc" → 1 13.5 <= 14 → 1 (3 * 4) - (6 * 2) <= 0 → 1
>=
Evaluates to 1 if the left operand is greater than or equal to the right operand, 0 otherwise.
"abc" >= "a" → 1 "a" >= "abc" → 0 13.5 >= 14 → 0 (3 * 4) - (6 * 2) >= 0 → 1
>
Evaluates to 1 if the left operand is greater than the right operand, 0 otherwise.
PI() > 3 → 1 "abc" > "a" → 1 SIN(0) > COS(0) → 0
expr BETWEEN min AND max
Evaluates to 1 if min is less than or equal to expr and max is greater than or equal to expr . If the operands expr , min , and max are all of the same type, these expressions are equivalent:
expr BETWEEN min AND max (min <= expr AND expr <= max)
If the operands are not of the same type, type conversion occurs and those two expressions may not be equivalent. BETWEEN is evaluated using comparisons determined according to the type of expr :
If expr is a string, the operands are compared lexicographically as strings. The comparisons are case sensitive or not, according to whether or not expr is a binary string.
If expr is an integer, the operands are compared numerically as integers.
If neither of the preceding rules is true, the operands are compared numerically as floating-point numbers.
"def" BETWEEN "abc" and "ghi" → 1 "def" BETWEEN "abc" and "def" → 1 13.3 BETWEEN 10 and 20 → 1 13.3 BETWEEN 10 and 13 → 0 2 BETWEEN 2 and 2 → 1 "B" BETWEEN "A" and "a" → 0 BINARY "B" BETWEEN "A" and "a" → 1
expr IN (value1,value2,…)
expr NOT IN (value1,value2,…)
IN evaluates to 1 if expr is one of the values in the list, 0 otherwise. For NOT IN, the opposite is true. The following expressions are equivalent:
expr NOT IN (value1,value2,…) NOT (expr IN (value1,value2,…))
If all values in the list are constants, MySQL sorts them and evaluates the IN test using a binary search, which is very fast.
3 IN (1,2,3,4,5) → 1 "d" IN ("a","b","c","d","e") → 1 "f" IN ("a","b","c","d","e") → 0 3 NOT IN (1,2,3,4,5) → 0 "d" NOT IN ("a","b","c","d","e") → 0 "f" NOT IN ("a","b","c","d","e") → 1expr IS NULL
expr IS NOT NULL
IS NULL evaluates to 1 if the value of expr is NULL, 0 otherwise. IS NOT NULL is the opposite. The following expressions are equivalent:
expr IS NOT NULL NOT (expr IS NULL)
IS NULL and IS NOT NULL should be used to determine whether or not the value of expr is NULL. You cannot use the regular comparison operators '=' and '!=' for this purpose.
NULL IS NULL → 1 0 IS NULL → 0 NULL IS NOT NULL → 0 0 IS NOT NULL → 1 NOT (0 IS NULL) → 1 NOT (NULL IS NULL) → 0 NOT NULL IS NULL → 1
The last example returns the result that it does because NOT binds more tightly than IS (see "Operator Precedence").
Bit Operators
Bit operations are performed using BIGINT values (64-bit integers), which limits the maximum range of the operations. Bit operations involving NULL values produce a NULL result.
|
Evaluates to the bitwise OR (union) of the operands.
1 | 1 → 1 1 | 2 → 3 1 | 2 | 4 | 8 → 15 1 | 2 | 4 | 8 | 15 → 15
&
Evaluates to the bitwise AND (intersection) of the operands.
1 &1 → 1 1 &2 → 0 7 &5 → 5
<<
Shifts the leftmost operand left the number of bit positions indicated by the right operand. Shifting by a negative amount results in a value of zero.
1 << 2 → 4 2 << 2 → 8 1 << 62 → 4611686018427387904 1 << 63 → -9223372036854775808 1 << 64 → 0
The last two examples demonstrate the limits of 64-bit calculations.
'<<' was introduced in MySQL 3.22.2.
>>
Shifts the leftmost operand right the number of bit positions indicated by the right operand. Shifting by a negative amount results in a value of zero.
16 >> 3 → 2 16 >> 4 → 1 16 >> 5 → 0
Logical Operators
Logical operators (also known as boolean operators, after the mathematician George Boole, who formalized their use) test the truth or falsity of expressions. All logical operations return 1 for true and 0 for false. Logical operators interpret non-zero operands as true and operands of 0 as false. NULL values are handled as indicated in the operator descriptions.
Logical operators expect operands to be numbers, so string operands are converted to numbers before the operator is evaluated.
NOT or !
Logical negation; evaluates to 1 if the following operand is false and 0 if the operand is true, except that NOT NULL is NULL.
NOT 0 → 1 NOT 1 → 0 NOT NULL → NULL NOT 3 → 0 NOT NOT 1 → 1 NOT "1" → 0 NOT "0" → 1 NOT " " → 1 NOT "abc" → 1
OR or ||
Logical OR; evaluates to 1 if either operand is true (not zero or NULL), zero otherwise.
0 OR 0 → 0 0 OR 3 → 1 4 OR 2 → 1 1 OR NULL → 1
AND or &&
Logical AND; evaluates to 1 if both operands are true (not zero or NULL), zero otherwise.
0 AND 0 → 0 0 AND 3 → 0 4 AND 2 → 1 1 AND NULL → 0
In MySQL, '!', '||', and '&&' indicate logical operations, as they do in C. Note in particular that '||' does not perform string concatenation as it does in some versions of SQL. Use the CONCAT() function instead to concatenate strings.
Cast Operators
BINARY causes the following operand to be treated as a binary string so that comparisons involving the string are case sensitive. If the following operand is a number, it is converted to string form:
"abc" = "ABC" → 1 "abc" = BINARY "ABC" → 0 BINARY "abc" = "ABC" → 0 "2" < 12 → 1 "12" < BINARY 12 → 0
BINARY causes a number-to-string conversion; the comparison is then performed lexicographically because both operands are strings.
BINARY was introduced in MySQL 3.23.0.
Pattern-Matching Operators
MySQL provides SQL pattern matching using LIKE and extended regular expression pattern matching using REGEXP. SQL pattern matches are not case sensitive unless the string to be matched or the pattern string are binary strings. Extended regular expression pattern matches are always case sensitive.
SQL pattern matching succeeds only if the pattern matches the entire string to be matched. Extended regular expression pattern matching succeeds if the pattern is found anywhere in the string.
str LIKE pat [ESCAPE ' c ']
str NOT LIKE pat [ESCAPE ' c ']
LIKE performs a simple SQL pattern match and evaluates to 1 if the pattern string pat matches the entire string expression str . If it does not match, it evaluates to 0. For NOT LIKE, the opposite is true. These two expressions are equivalent:
str NOT LIKE pat [ESCAPE 'c'] NOT (str LIKE pat [ESCAPE 'c'])
The result is NULL if either string is NULL.
Two characters have special meaning in SQL patterns and serve as wildcards, as indicated here:
Character Meaning % Match any sequence of characters other than NULL (including an empty string) _ Match any single character Patterns may contain either or both wildcard characters:
"catnip" LIKE "cat%" → 1 "dogwood" LIKE "%wood" → 1 "bird" LIKE "____" → 1 "bird" LIKE "___" → 0 "dogwood" LIKE "%wo__" → 1
Case sensitivity of SQL pattern matching using LIKE is determined by the strings being compared. Normally, comparisons are not case sensitive. If either string is a binary string, the comparison is case sensitive:
"abc" LIKE "ABC" → 1 BINARY "abc" LIKE "ABC" → 0 "abc" LIKE BINARY "ABC" → 0
Because '%' matches any sequence of characters, it even matches no characters:
"" LIKE "%" → 1 "cat" LIKE "cat%" → 1
In MySQL, you can use LIKE with numeric expressions:
50 + 50 LIKE "1%" → 1 200 LIKE "2__" → 1
To match a wildcard character literally, turn off its special meaning in the pattern string by preceding it with the escape character, '':
"100% pure" LIKE "100%" → 1 "100% pure" LIKE "100\%" → 0 "100% pure" LIKE "100\% pure" → 1
If you want to use an escape character other than '', specify it using an ESCAPE clause:
str REGEXP pat
str NOT REGEXP pat
REGEXP performs an extended regular expression pattern match. Extended regular expressions are similar to the patterns used by the UNIX utilities grep and sed. The pattern sequences you can use are shown in Table C.1.
REGEXP evaluates to 1 if the pattern string pat matches the string expression str , 0 otherwise. NOT REGEXP is the opposite. These two expressions are equivalent:
str NOT REGEXP pat NOT (str REGEXP pat)
The result is NULL if either string is NULL.
The pattern need not match the entire string, it just needs to be found somewhere in the string.
The […] and [^…] constructs specify character classes. Within a class, a range of characters may be indicated using a dash between the two endpoint characters of the range. For example, [a-z] matches any lowercase letter, and [0-9] matches any digit. For you to be able to indicate a literal ']' within a class, it must be the first character of the class. In order for you to indicate a literal '-', it must be the first or last character of the class. And for you to indicate a literal '^', it must not be the first character after the '['.
Several other more complicated special constructions having to do with collating sequences and equivalence classes may be used within a character lass. See the MySQL Reference Manual for more information.
An extended regular expression match succeeds if the pattern is found anywhere in the string being matched, but you can use '^' and '$' to force the pattern to match only at the beginning or end of the string.
"abcde" REGEXP "b" → 1 "abcde" REGEXP "^b" → 0 "abcde" REGEXP "b$" → 0 "abcde" REGEXP "^a" → 1 "abcde" REGEXP "e$" → 1 "abcde" REGEXP "^a.*e$" → 1
Extended regular expression pattern matches are case sensitive:
str RLIKE pat str NOT RLIKE pat
MySQL uses syntax similar to C for escape sequences within strings. For example, ' ', ' ', and '\' are interpreted as newline, tab, and backslash. To specify such characters in a pattern, double the backslashes ('\n', '\t', and '\\'). One backslash is stripped off when the query is parsed, and the remaining escape sequence is interpreted when the pattern match is performed.