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 differing precedence levels. 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
!
- (unary minus) ~ (unary bit negation)
^
* / DIV % MOD
+ -
<< >>
&
|
< <= = <=> <> != >= > IN IS LIKE REGEXP RLIKE
BETWEEN CASE WHEN THEN ELSE
NOT
AND &&
OR || XOR
:=
The unary operators (unary minus, unary bit negation, NOT, BINARY, and COLLATE) 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+3  1
-(2+3) -5
Some operator precedences vary depending on the server SQL mode or MySQL version:
If the PIPES_AS_CONCAT SQL mode is enabled, the || operator precedence is elevated to a level between ^ and the unary operators. Before MySQL 5.0.2, NOT has the same precedence as the ! operator. From 5.0.2 on, that original precedence can be obtained by enabling the HIGH_NOT_PRECEDENCE SQL mode.
Grouping Operators
(...) Parentheses can be used to group parts of an expression. They override the default operator precedence that otherwise determines the order in which terms of an expression are evaluated. (See "Operator Precedence.") Parentheses also may be used simply for visual clarity to make an expression more readable. Nested parenthesized expressions are evaluated from innermost to outermost.
1 + 2 * 3 / 4 2.50
(((1 + 2) * 3) / 4) 2.25
(expr[,expr]...)
ROW(expr[,expr]...)
These row constructors can be used to express a comparison between two tuples (sets) of values. The tuples to be compared must contain the same number of values. The two row constructors are equivalent. For example, if a subquery returns a row containing three values, you can compare the result to a given set of three values using either of the following constructs:
SELECT ... FROM t2 WHERE (0,1,2) = (SELECT col1, col2, col3 FROM ...);
SELECT ... FROM t2 WHERE ROW(0,1,2) = (SELECT col1, col2, col3 FROM ...);
Row constructors can be used in non-subquery contexts as well. The following statement is legal:
SELECT * FROM president
WHERE (first_name,last_name) = ('Abraham','Lincoln');
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
DIV Integer division; evaluates to the quotient of the operands with no fractional part. Division by zero produces a NULL result.
3 DIV 1 3
1 DIV 3 0
1 DIV 0 NULL
%, MOD The modulo operator; evaluates to the remainder of m divided by n.m % n and m MOD n are 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 floating-point values, modulo returns the exact remainder after division as of MySQL 4.1.7:
14.4 % 3.2 1.6
Before 4.1.7, modulo rounds the arguments to integers and returns an integer result:
14.4 % 3.2 2
Arithmetic for the +, -, and * operators is performed with BIGINT values (64-bit integers) if both arguments are integers. This means that 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 the / and % operators, 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 NULL <=> NULL is true.) If both operands are strings, they are compared lexically as strings. If both operands are integers, they are compared numerically as integers. As of MySQL 4.1.1, hexadecimal constants that are not compared to a number are compared as binary strings. (This differs from MySQL 4.0, which compares hexadecimal constants as numbers by default.) 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. Otherwise, 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 lexically. The third comparison involves a string and a number, so they are compared as floating-point values.
MySQL performs string comparisons as follows: 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, the comparison may result in an error or fail to yield meaningful results. A comparison between a binary and a non-binary string is treated as a comparison of binary strings.
= Evaluates to 1 if the operands are equal, 0 otherwise.
1 = 1 1
1 = 2 0
'abc' = 'abc' 1
'abc' = 'ABC' 1
'abc' = 'def' 0
'abc' = 0 1
'abc' is equal to both 'abc' and 'ABC' because string comparisons are not case sensitive for non-binary strings. '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. For non-binary strings, the character set collation of the operands determines the comparison value of characters that are similar but differ in lettercase or in accent or diacritical marks. String comparisons are not case sensitive unless the comparison involves a binary string or a non-binary string with a binary or case-sensitive collation. For example, a case-sensitive comparison is performed if you use the BINARY keyword or are comparing values from CHAR BINARY, VARCHAR BINARY, or BLOB columns.
'abc' = 'ABC' 1
BINARY 'abc' = 'ABC' 0
BINARY 'abc' = 'abc' 1
_latin1 'abc' COLLATE latin1_bin = 'ABC' 0
_latin1 'abc' COLLATE latin1_general_cs = 'ABC' 0
Trailing spaces are significant for binary string comparisons, but not for non-binary string comparisons:
BINARY 'a' = 'a ' 0
'a' = 'a ' 1
<=> 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. <>, != 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
expr NOT BETWEEN min AND max
BETWEEN evaluates to 1 if expr lies within the range of values spanned by min and max (inclusive), 0 otherwise. 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 according to the type of expr: If expr is a string, the operands are compared lexically as strings, using the rules given at the beginning of this section. 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 When the initial expression, expr, is present, CASE compares it 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
When the initial expression, expr, is not present, CASE evaluates WHEN expressions. For the first one 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'
Note that the CASE expression differs from the CASE statement described in "Stored Routine Syntax," in Appendix E, "SQL Syntax Reference."
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') 1
expr 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 the value of expr is NULL. You cannot use the regular equality and inequality comparison operators (=, <>, !=) for this purpose. (You also can 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 shows that NOT binds more tightly than IS, as well as that NOT NULL is NULL. (See "Operator Precedence.")
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.
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.
In MySQL, !, ||, and && indicate logical operations, as they do in C. Note in particular that || does not perform string concatenation as it does in standard SQL. Use the CONCAT() function instead to concatenate strings. If you want || to be treated as the string concatenation operator, you can enable the PIPES_AS_CONCAT SQL mode.
NOT, ! 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, && Logical AND; evaluates to 1 if both operands are true (not zero and not NULL), zero otherwise.
0 AND 0 0
0 AND 3 0
4 AND 2 1
With respect to NULL operands (that is, operands of unknown value), AND evaluates to 0 (false) 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
OR, || Logical OR; evaluates to 1 if either operand is true (not zero and not NULL), zero otherwise.
0 OR 0 0
0 OR 3 1
4 OR 2 1
With respect to NULL operands (that is, operands of unknown value), OR evaluates to 1 (true) 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
XOR Logical exclusive-OR; evaluates to 1 if exactly one operand is true (not zero and not NULL), and zero otherwise. Evaluates to NULL (unknown) if either operand is NULL.
0 XOR 0 0
0 XOR 9 1
7 XOR 0 1
5 XOR 2 0
Cast Operators
Cast operators convert values from one type to another.
_charset str The _charset operator is called an "introducer." It causes the following string constant or column value to be treated as though it has a given character set. charset must be 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 latin2, utf8, or ucs2:
_latin2 'abc'
_utf8 'abc'
_ucs2 'abc'
BINARY str BINARY causes the following operand to be treated as a binary string so that comparisons involving the string are performed byte by byte using the numeric value of each byte. 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 operands then are compared as binary strings. str COLLATE collation The COLLATE operator causes the given string str to be compared using the given collating order. The collation must be one of the legal collations for the character set of str. COLLATE affects operations such as comparisons, sorting, grouping, and DISTINCT.
SELECT ... WHERE utf8_str COLLATE utf8_icelandic_ci > 'M';
SELECT MAX(greek_str COLLATE greek_general_ci) FROM ... ;
SELECT ... GROUP BY latin1_str COLLATE latin1_german2_ci;
SELECT ... ORDER BY sjis_str COLLATE sjis_bin;
SELECT DISTINCT latin2_str COLLATE latin2_croatian_ci FROM ...;
Pattern-Matching Operators
MySQL provides SQL pattern matching using LIKE and regular expression pattern matching using REGEXP. Both types of pattern matches are not case sensitive unless the string to be matched or the pattern string are binary strings or a non-binary string with a binary or case-sensitive collation. 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.
Additional discussion and examples of pattern matching can be found in "Operator Types," in Chapter 3, "Working with Data in MySQL."
str LIKE pattern [ESCAPE 'c']
str NOT LIKE pattern [ESCAPE 'c']
LIKE performs an SQL pattern match and evaluates to 1 if the pattern string pattern 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 pattern [ESCAPE 'c']
NOT (str LIKE pattern [ESCAPE 'c'])
The result is NULL if either string is NULL. Two characters have special meaning in SQL patterns and serve as wildcards: 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 or a non-binary string with a binary or case-sensitive collation, the comparison is case sensitive:
'abc' LIKE 'ABC' 1
BINARY 'abc' LIKE 'ABC' 0
'abc' LIKE BINARY 'ABC' 0
'abc' LIKE 'ABC' COLLATE latin1_bin 0
'abc' COLLATE latin1_bin LIKE '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
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 pattern
str NOT REGEXP pattern
REGEXP performs a regular expression pattern match. It evaluates to 1 if the pattern string pattern matches the string expression str, 0 otherwise. NOT REGEXP is the opposite of REGEXP, so these two expressions are equivalent:
str NOT REGEXP pattern
NOT (str REGEXP pattern)
Case sensitivity of regular expression matching using REGEXP is determined by the strings being compared. Normally, comparisons are not case sensitive. If either string is a binary string or a non-binary string with a binary or (as of MySQL 4.1.8) case-sensitive collation, the comparison is case sensitive:
'abc' REGEXP 'ABC' 1
BINARY 'abc' REGEXP 'ABC' 0
'abc' REGEXP BINARY 'ABC' 0
'abc' REGEXP 'ABC' COLLATE latin1_bin 0
'abc' COLLATE latin1_bin REGEXP 'ABC' 0
REGEXP is not multi-byte safe and works only for single-byte character sets. 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 ElementsElement | 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 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.
'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 ClassesElement | 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
The special markers [[:<:]] and [[:>:]] match the beginning and end of word boundaries, respectively. A word character is considered to be any character in the alnum class or underscore. A word consists of one or more word characters not preceded by or followed by word characters.
'a few words' REGEXP '[[:<:]]few[[:>:]]' 1
'a few words' REGEXP '[[:<:]]fe[[:>:]]' 0
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 during query parsing; interpretation of the remaining escape sequence occurs during the pattern match operation.
str RLIKE pattern
str NOT RLIKE pattern
RLIKE and NOT RLIKE are synonyms for REGEXP and NOT REGEXP.
 |
