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 COLLATE NOT ! ^ XOR - (unary minus) ~ (unary bit negation) * / % + - << >> & | < <= = <=> != <> >= > IN IS LIKE REGEXP RLIKE BETWEEN CASE WHEN THEN ELSE AND && OR || :=
The unary operators (unary minus, unary bit negation, 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.
-2+31 -(2+3)
Grouping Operators
Parentheses can be used to group parts of an expression. They override the default operator precedence that determines the order in which terms of an expression are evaluated (see "Operator Precedence" earlier in this appendix). Parentheses can also be used simply for visual clarity, to make an expression more readable.
1 + 2 * 3 / 4
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.
+
Addition; 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.
-
Subtraction; 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
*
Multiplication; evaluates to the product of the operands.
2 * 3
6
2.3 * -4.5 -10.3
/
Division; 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 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
Arithmetic for the +, -, and * operators 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:
Other than for the <=> operator, comparisons involving NULL values evaluate as NULL. (<=> is like =, except that the value of the expression NULL <=> NULL is true.)
If both operands are strings, they are compared lexically as strings. Binary strings are compared on a byte-by-byte basis using the numeric value of each byte. Comparisons for non-binary strings are performed character by character, using the collating sequence of the character set in which the strings are expressed. If the strings have different character sets (as is possible as of MySQL 4.1), the comparison might not yield meaningful results. A comparison between a binary and a non-binary string is treated as a comparison of binary strings.
If both operands are integers, they are compared numerically as integers.
As of MySQL 3.23.22, hexadecimal constants are compared as numbers. Before that, hex 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
The first comparison involves two integers, which are compared numerically. The second comparison involves two strings, which are compared lexically. 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 for purposes of the comparison.
The current character set determines the comparison value of characters that are similar but differ in accent or diacritical marks.
<=>
The 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; otherwise, evaluates to 0.
'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; otherwise, evaluates to 0.
'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; otherwise, evaluates to 0.
PI() > 3
1
'abc' > 'a' 1
SIN(0) > COS(0) 0
expr BETWEEN min AND max expr NOT BETWEEN min AND max
BETWEEN evaluates to 1 if expr lies within the range of values spanned by min and max (inclusive); otherwise, it evaluates to 0. For NOT BETWEEN, the opposite is true. 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 the two expressions may not be equivalent. BETWEEN is evaluated using comparisons determined depending on the type of expr:
If expr is a string, the operands are compared lexically as strings. The comparisons are case sensitive or not, depending on 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
CASE expr WHEN expr1 THEN result1 ... [ ELSE default ] END CASE WHEN expr1 THEN result1 ... [ ELSE default ] END
The first form of CASE compares the initial expression expr to the expression following each WHEN. For the first one that is equal, the corresponding THEN value becomes the result. This is useful for comparing a given value to a set of values.
CASE 0 WHEN 1 THEN 'T' WHEN 0 THEN 'F' END
'F'
CASE 'F' WHEN 'T' THEN 1 WHEN 'F' THEN 0 END 0
The second form of CASE evaluates WHEN expressions until one is found that is true (not zero and not NULL). The corresponding THEN value becomes the result. This is useful for performing non-equality tests or testing arbitrary conditions.
CASE WHEN 1=0 THEN 'absurd' WHEN 1=1 THEN 'obvious' END 'obvious'
If no WHEN expression matches, the ELSE value is the result. If there is no ELSE clause, CASE evaluates to NULL.
CASE 0 WHEN 1 THEN 'true' ELSE 'false' END
'false'
CASE 0 WHEN 1 THEN 'true' END NULL
CASE WHEN 1=0 THEN 'true' ELSE 'false' END 'false'
CASE WHEN 1/0 THEN 'true' END NULL
The type of the value following the first THEN determines the type of the entire CASE expression.
CASE 1 WHEN 0 THEN 0 ELSE 1 END
1
CASE 1 WHEN 0 THEN '0' ELSE 1 END '1'
CASE was introduced in MySQL 3.23.3.
expr IN (value1, value2,...) expr NOT IN (value1,value2,...)
IN() evaluates to 1 if expr is one of the values in the list; otherwise, it evaluates to 0. 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') 1
expr IS NULL expr IS NOT NULL
IS NULL evaluates to 1 if the value of expr is NULL; otherwise it evaluates to 0. 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. (As of MySQL 3.23, you can also use <=> to test for equality with NULL.)
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 shown because NOT binds more tightly than IS (see the "Operator Precedence" section earlier in this appendix).
Bit Operators
This section describes operators that perform bitwise calculations. 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 AND (intersection) of the operands.
1 & 1
1
1 & 2 0
7 & 5 5
|
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 XOR (exclusive-Or) of the operands.
1 ^ 1
0
1 ^ 0 1
255 ^ 127 128
This operator was introduced in MySQL 4.0.2. Prior to that, you can produce the same result for two values m and n using the following expression:
(m & (~n)) | ((~m) & n)
<<
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 0.
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 0.
16 >> 3
2
16 >> 4 1
16 >> 5 0
>> was introduced in MySQL 3.22.2.
~
Performs bitwise negation of the following expression. That is, all 0 bits become 1 and vice versa.
~0
-1
~(-1) 0
~~(-1) -1
~ was introduced in MySQL 3.23.5.
Logical Operators
Logical operators (also known as boolean operators, after the mathematician George Boole, who formalized their use) test the truth or falseness 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
AND or &&
Logical AND; evaluates to 1 if both operands are true (not 0 and not NULL); 0 otherwise.
0 AND 0
0
0 AND 3 0
4 AND 2 1
The behavior of AND is version-dependent with respect to NULL operands (that is, operands of unknown value). As of MySQL 3.23.9, AND evaluates to 0 if the result can be known to be false, NULL if the result cannot be determined.
1 AND NULL
NULL
0 AND NULL 0
NULL AND NULL NULL
Prior to MySQL 3.23.9, AND evaluates to 0 with NULL operands. In effect, NULL is treated as 0.
1 AND NULL
0
0 AND NULL 0
NULL AND NULL 0
OR or ||
Logical OR; evaluates to 1 if either operand is true (not zero and not NULL); 0 otherwise.
0 OR 0
0
0 OR 3 1
4 OR 2 1
The behavior of OR is version-dependent with respect to NULL operands (that is, operands of unknown value). As of MySQL 3.23.9, OR evaluates to 1 if the result can be known to be true; NULL if the result cannot be determined.
1 OR NULL
1
0 OR NULL NULL
NULL OR NULL NULL
Prior to MySQL 3.23.9, OR evaluates to 1 if the result can be known to be true; 0 otherwise. In effect, NULL is treated as 0.
1 OR NULL
1
0 OR NULL 0
NULL OR NULL 0
XOR
Logical exclusive-XOR; evaluates to 1 if exactly one operand is true (not zero and not NULL), and 0 otherwise. Evaluates to NULL if either operand is NULL.
0 XOR 0
0
0 XOR 9 1
7 XOR 0 1
5 XOR 2 0
XOR was introduced in MySQL 4.0.2. Prior to that, you can produce the same result for two values m and n using the following expression:
(m AND (NOT n)) OR ((NOT m) AND n)
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. (You can start the server in ANSI mode with the --ansi option if you want || to be treated as the string concatenation operator.)
Cast Operators
Cast operators convert values from one type to another.
_charset str
To treat a string constant or column value as though it has a given character set, precede it with a _charset operator, where charset is the name of a character set supported by the server. For example, the following expressions treat the string 'abc' as having a character set of latin1_de, utf8, or ucs2:
_latin1_de 'abc' _utf8 'abc' _ucs2 'abc'
The _charset operator was introduced in MySQL 4.1.0.
BINARY str
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
'2' < BINARY 12 0
In the last example, BINARY causes a number-to-string conversion. The comparison is then performed lexically because both operands are strings.
BINARY was introduced in MySQL 3.23.0.
str COLLATE charset
The COLLATE operator causes the given string str to be compared using the collating order for the character set charset. This affects operations such as comparisons, sorting, grouping, and DISTINCT.
SELECT ... WHERE col_name COLLATE utf8 > 'M'; SELECT MAX(col_name COLLATE greek) FROM ... ; SELECT ... GROUP BY col_name COLLATE latin1; SELECT ... ORDER BY col_name COLLATE czech; SELECT DISTINCT col_name COLLATE latin1_de FROM ...;
The COLLATE operator was introduced in MySQL 4.1.0.
Pattern-Matching Operators
MySQL provides SQL pattern matching using LIKE and 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. The same is true for regular expression pattern matches, with the exception that regular expressions are always case sensitive prior to MySQL 3.23.4. SQL pattern matching succeeds only if the pattern matches the entire string to be matched. 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 SQL pattern match and evaluates to 1 if the pattern string pat matches the entire string expression str. If the pattern does not match, LIKE 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:
'%' matches any sequence of characters (including an empty string) other than NULL.
'_' (underscore) matches any single character.
Patterns can 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:
'100% pure' LIKE '100^%' ESCAPE '^'
0
'100% pure' LIKE '100^% pure' ESCAPE '^' 1
str REGEXP pat str NOT REGEXP pat
REGEXP performs a regular expression pattern match. It evaluates to 1 if the pattern string pat matches the string expression str; 0 otherwise. NOT REGEXP is the opposite of REGEXP so these two expressions are equivalent:
str NOT REGEXP pat NOT (str REGEXP pat)
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.
Table C.1. Regular Expression Elements
Element Meaning ^ Match the beginning of the string $ Match the end of the string . Match any single character, including newline [...] Match any character appearing between the brackets [^...] Match any character not appearing between the brackets e* Match zero or more instances of pattern element e e+ Match one or more instances of pattern element e e? Match zero or one instances of pattern element e e1|e2 Match pattern element e1 or e2 e{m} Match m instances of pattern element e e{m,} Match m or more instances of pattern element e e{,n} Match zero to n instances of pattern element e e{m,n} Match m to n instances of pattern element e (...) Group pattern elements into a single element Other Non-special characters match themselves The result of a regular expression match is NULL if either string is NULL.
A regular expression pattern need not match the entire string, it just needs to be found somewhere in the string.
'cats and dogs' REGEXP 'dogs'
1
'cats and dogs' REGEXP 'cats' 1
'cats and dogs' REGEXP 'c.*a.*d' 1
'cats and dogs' REGEXP 'o' 1
'cats and dogs' REGEXP 'x' 0
You can use '^' or '$' to force a 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
The [...] and [^...] constructs specify character classes. Within a class, a range of characters can 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.
'bin' REGEXP '^b[aeiou]n$'
1
'bxn' REGEXP '^b[aeiou]n$' 0
'oboeist' REGEXP '^ob[aeiou]+st$' 1
'wolf359' REGEXP '[a-z]+[0-9]+' 1
'wolf359' REGEXP '[0-9a-z]+' 1
'wolf359' REGEXP '[0-9]+[a-z]+' 0
To indicate a literal ']' within a class, it must be the first character of the class. To indicate a literal '-', it must be the first or last character of the class. To indicate a literal '^', it must not be the first character after the '['.
Several special POSIX character class constructions having to do with collating sequences and equivalence classes are available as well, as shown in Table C.2.
Table C.2. Regular Expression POSIX Character Classes
Element Meaning [:alnum:] Alphabetic and numeric characters [:alpha:] Alphabetic characters [:blank:] Whitespace (space or tab characters) [:cntrl:] Control characters [:digit:] Decimal digits (0-9) [:graph:] Graphic (non-blank) characters [:lower:] Lowercase alphabetic characters [:print:] Graphic or space characters [:punct:] Punctuation characters [:space:] Space, tab, newline, or carriage return [:upper:] Uppercase alphabetic characters [:xdigit:] Hexadecimal digits (0-9, a-f, A-F) The POSIX constructors are used within character classes:
'abc' REGEXP '[[:space:]]'
0
'a c' REGEXP '[[:space:]]' 1
'abc' REGEXP '[[:digit:][:punct:]]' 0
'a0c' REGEXP '[[:digit:][:punct:]]' 1
'a,c' REGEXP '[[:digit:][:punct:]]' 1
MySQL uses syntax similar to C for escape sequences within regular expression strings. For example, \n, \t, 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.
str RLIKE pat str NOT RLIKE pat
RLIKE and NOT RLIKE are synonyms for REGEXP and NOT REGEXP.
