MySQL uses some standard SQL operators and some non-standard operators. They can be used to write expressions which involve constant values, variables, values contained in fields and / or other expressions.
Comparison operators
editEquality
editIf you want to check if 2 values are equal, you must use the = operator:
SELECT True = True -- returns 1
SELECT True = False -- returns 0
If you want to check if 2 values are different, you can use the <> or != operators, which have the same meaning:
SELECT True <> False -- returns 1
SELECT True != True -- returns 0
<> return 1 where = returns 0 and vice versa.
IS and NULL-safe comparison
editWhen you compare a NULL value with a non-NULL value, you'll get NULL. If you want to check if a value is null, you can use IS:
SELECT (NULL IS NULL) -- returns 1
SELECT (1 IS NULL) -- returns 0
SELECT (True IS True) -- returns an error!
You can check if a value is non-NULL:
SELECT (True IS NOT NULL) -- returns 1
There is also an equality operator which considers NULL as a normal value, so it returns 1 (not NULL) if both values are NULL and returns 0 (not NULL) if one of the values is NULL:
SELECT NULL <=> NULL -- 1
SELECT True <=> True -- 1
SELECT col1 <=> col2 FROM myTable
There is not a NULL-safe non-equality operator, but you can type the following:
SELECT NOT (col1 <=> col2) FROM myTable
IS and Boolean comparisons
editIS and IS NOT can also be used for Boolean comparisons. You can use them with the reserved words TRUE, FALSE and UNKNOWN (which is merely a synonym for NULL).
SELECT 1 IS TRUE -- returns 1
SELECT 1 IS NOT TRUE -- returns 0
SELECT 1 IS FALSE -- returns 0
SELECT (NULL IS NOT FALSE) -- returns 1: unknown is not false
SELECT (NULL IS UNKNOWN) -- returns 1
SELECT (NULL IS NOT UNKNOWN) -- returns 0
Greater, Less...
editYou can check if a value is greater than another value:
SELECT 100 > 0 -- returns 1
SELECT 4 > 5 -- return 0
You can also check if a value is minor than another value:
SELECT 1 < 2 -- returns 1
SELECT 2 < 2 -- returns 0
This kind of comparisons also works on TEXT values:
SELECT 'a' < 'b' -- returns 1
Generally speaking, alphabetical order is used for TEXT comparisons. However, the exact rules are defined by the COLLATION used. A COLLATION defines the sorting rules for a given CHARACTER SET. For example, a COLLATION may be case-sensitive, while another COLLATION may be case-insensitive.
You can check if a value is equal or greater than another value. For example, the following queries have the same meaning:
SELECT `a` >= `b` FROM `myTable`
SELECT NOT (`a` < `b`) FROM `myTable`
Similarly, you can check if a value is less or equal to another value:
SELECT `a` <= `b` FROM `myTable`
BETWEEN
editIf you want to check if a value is included in a given range (boundaries included), you can use the BETWEEN ... AND ... operator. AND doesn't have its usual meaning. Example:
SELECT 2 BETWEEN 10 AND 100 -- 0
SELECT 10 BETWEEN 10 AND 100 -- 1
SELECT 20 BETWEEN 10 AND 100 -- 1
The value after BETWEEN and the value after AND are included in the range.
You can also use NOT BETWEEN to check if a value is not included in a range:
SELECT 8 NOT BETWEEN 5 AND 10 -- returns 0
IN
editYou can use the IN operator to check if a value is included in a list of values:
SELECT 5 IN (5, 6, 7) -- returns 1
SELECT 1 IN (5, 6, 7) -- returns 0
You should not include in the list both numbers and strings, or the results may be unpredictable. If you have numbers, you should quote them:
SELECT 4 IN ('a', 'z', '5')
There is not a theoretical limit to the number of values included in the IN operator.
You can also use NOT IN:
SELECT 1 NOT IN (1, 2, 3) -- returns 0
Logical operators
editMySQL Boolean logic
editMySQL doesn't have a real BOOLEAN datatype.
FALSE is a synonym for 0. Empty strings are considered as FALSE in a Boolean context.
TRUE is a synonym for 1. All non-NULL and non-FALSE data are considered as TRUE in a boolean context.
UNKNOWN is a synonym for NULL. The special date 0/0/0 is NULL.
NOT
editNOT is the only operator which has only one operand. It returns 0 if the operand is TRUE, returns 1 if the operand is FALSE and returns NULL if the operand is NULL.
SELECT NOT 1 -- returns 0
SELECT NOT FALSE -- returns 1
SELECT NOT NULL -- returns NULL
SELECT NOT UNKNOWN -- returns NULL
! is a synonym for NOT.
SELECT !1
AND
editAND returns 1 if both the operands are TRUE, else returns 0; if at least one of the operands is NULL, returns NULL.
SELECT 1 AND 1 -- returns 1
SELECT 1 AND '' -- return 0
SELECT '' AND NULL -- returns NULL
&& is a synonym for AND.
SELECT 1 && 1
OR
editOR returns TRUE if at least one of the operands is TRUE, else returns FALSE; if the two operands are NULL, returns NULL.
SELECT TRUE OR FALSE -- returns 1
SELECT 1 OR 1 -- returns 1
SELECT FALSE OR FALSE -- returns 0
SELECT NULL OR TRUE -- returns NULL
|| is a synonym for OR.
SELECT 1 || 0
XOR
editXOR (eXclusive OR) returns 1 if only one of the operands is TRUE and the other operand is FALSE; returns 0 if both the operands are TRUE o both the operands are FALSE; returns NULL if one of the operands is NULL.
SELECT 1 XOR 0 -- returns 1
SELECT FALSE XOR TRUE -- returns 1
SELECT 1 XOR TRUE -- returns 0
SELECT 0 XOR FALSE -- returns 0
SELECT NULL XOR 1 -- returns NULL
Synonyms
edit- AND can be written as &&
- OR can be written ad ||
- NOT can be written as !
Only NOT (usually) has a different precedence from its synonym. See operator precedence for detail.
Arithmetic operators
editMySQL supports operands which perform all basic arithmetic operations.
You can type positive values with a '+', if you want:
SELECT +1 -- return 1
You can type negative values with a '-'. - is an inversion operand:
SELECT -1 -- returns -1
SELECT -+1 -- returns -1
SELECT --1 -- returns 1
You can make sums with '+':
SELECT 1 + 1 -- returns 2
You can make subtractions with '-':
SELECT True - 1 -- returns 0
You can multiply a number with '*':
SELECT 1 * 1 -- returns 1
You can make divisions with '/'. Returns a FLOAT number:
SELECT 10 / 2 -- returns 5.0000
SELECT 1 / 1 -- returns 1.0000
SELECT 1 / 0 -- returns NULL (not an error)
You can make integer divisions with DIV. Resulting number is an INTEGER. No remainder. This has been added in MySQL 4.1.
SELECT 10 DIV 3 -- returns 3
You can get the remainder of a division with '%' or MOD:
SELECT 10 MOD 3 -- returns 1
Using + to cast data
editYou can convert an INTEGER to a FLOAT doing so:
SELECT 1 + 0.0 -- returns 1.0
SELECT 1 + 0.000 -- returns 1.000
SELECT TRUE + 0.000 -- returns 1.000
You can't convert a string to a FLOAT value by adding 0.0, but you can cast it to an INTEGER:
SELECT '1' + 0 -- returns 1
SELECT '1' + FALSE -- returns 1
SELECT <nowiki>''</nowiki> + <nowiki>''</nowiki> -- returns 0
Text operators
editThere are no concatenation operators in MySQL.
Arithmetic operators convert the values into numbers and then perform arithmetic operations, so you can't use + to concatenate strings.
You can use the CONCAT() function instead.
LIKE
editThe LIKE operator may be used to check if a string matches to a pattern. A simple example:
SELECT * FROM articles WHERE title LIKE 'hello world'
The pattern matching is usually case insensitive. There are two exceptions:
- when a LIKE comparison is performed against a column which has been declared with the BINARY flag (see CREATE TABLE);
- when the expression contains the BINARY clause:
SELECT * 'test' LIKE BINARY 'TEST' -- returns 0
You can use two special characters for LIKE comparisons:
- _ means "any character" (but must be 1 char, not 0 or 2)
- % means "any sequence of chars" (even 0 chars or 1000 chars)
Note that "\" also escapes quotes ("'") and this behaviour can't be changed by the ESCAPE clause. Also, the escape character does not escape itself.
Common uses of LIKE:
- Find titles starting with the word "hello":
SELECT * FROM articles WHERE title LIKE 'hello%'
- Find titles ending with the word "world":
SELECT * FROM articles WHERE title LIKE '%world'
- Find titles containing the word "gnu":
SELECT * FROM articles WHERE title LIKE '%gnu%'
These special chars may be contained in the pattern itself: for example, you could need to search for the "_" character. In that case, you need to "escape" the char:
SELECT * FROM articles WHERE title LIKE '\_%' -- titles starting with _
SELECT * FROM articles WHERE title LIKE '\%%' -- titles starting with %
Sometimes, you may want to use an escape character different from "\". For example, you could use "/":
SELECT * FROM articles WHERE title LIKE '/_%' ESCAPE '/'
When you use = operator, trailing spaces are ignored. When you use LIKE, they are taken into account.
SELECT 'word' = 'word ' -- returns 1
SELECT 'word' LIKE 'word ' -- returns 0
LIKE also works with numbers.
SELECT 123 LIKE '%2%' -- returns 1
If you want to check if a pattern doesn't match, you can use NOT LIKE:
SELECT 'a' NOT LIKE 'b' -- returns 1
SOUNDS LIKE
editYou can use SOUNDS LIKE to check if 2 text values are pronounced in the same way. SOUNDS LIKE uses the SOUNDEX algorithm, which is based on English rules and is very approximate (but simple and thus fast).
SELECT `word1` SOUNDS LIKE `word2` FROM `wordList` -- short form
SELECT SOUNDEX(`word1`) = SOUNDEX(`word2`) FROM `wordList` -- long form
SOUNDS LIKE is a MySQL-specific extension to SQL. It has been added in MySQL 4.1.
Regular expressions
editYou can use REGEXP to check if a string matches to a pattern using regular expressions.
SELECT 'string' REGEXP 'pattern'
You can use RLIKE as a synonym for REGEXP.
Bitwise operators
editBit-NOT:
SELECT ~0 -- returns 18446744073709551615
SELECT ~1 -- returns 18446744073709551614
Bit-AND:
SELECT 1 & 1 -- returns 1
SELECT 1 & 3 -- returns 1
SELECT 2 & 3 -- returns 2
Bit-OR:
SELECT 1 | 0 -- returns 1
SELECT 3 | 0 -- returns 3
SELECT 4 | 2 -- returns 6
Bit-XOR:
SELECT 1 ^ 0 -- returns 1
SELECT 1 ^ 1 -- returns 0
SELECT 3 ^ 1 -- returns 2
Left shift:
SELECT 1 << 2 -- returns 4
Right shift:
SELECT 1 >> 2 -- 0
Conditions
editIF
editThe structure IF ... THEN ... ELSE ... END IF;
only functions in the stored procedures. To manage a condition out of them, we can use[1]: IF(condition, ifTrue, ifFalse);
.
Example: SELECT IF(-1 < 0, 0, 1);
returns 0.
Example with several conditions (switch)[2][3]:
IF n > m THEN SET s = '>';
ELSEIF n = m THEN SET s = '=';
ELSE SET s = '<';
END IF;
CASE
editSELECT CASE WHEN condition THEN ifTrue ELSE ifFalse END;
Example: SELECT CASE WHEN '-1 < 0' THEN 0 ELSE 1 END;
renvoie 0.
Example with several conditions[4]:
CASE v
WHEN 2 THEN SELECT v;
WHEN 3 THEN SELECT 0;
ELSE
BEGIN
END;
END CASE;
In one request:
SELECT CASE v
WHEN 1 THEN 'a'
WHEN 2 THEN 'b'
WHEN 3 THEN 'c'
WHEN 4 THEN 'd'
ELSE 0
END as value
Precedence
editOperator precedence
editTable of operator precedence:
INTERVAL
BINARY, COLLATE
!
- (unary minus), ~ (unary bit inversion)
^
*, /, DIV, %, MOD
-, +
<<, >>
&
|
=, <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
BETWEEN, CASE, WHEN, THEN, ELSE
NOT
&&, AND
XOR
||, OR
:=
Modifiers:
- PIPES_AS_CONCAT - If this SQL mode is enabled, || has precedence on ^, but - and ~ have precedence on ||.
- HIGH_NOT_PRECEDENCE - If this SQL mode is enabled, NOT has the same precedence level as !.
Use of parenthesis
editYou can use parenthesis to force MySQL to evaluate a subexpression before another independently from operator precedence:
SELECT (1 + 1) * 5 -- returns 10
You can also use parenthesis to make an expression more readable by humans, even if they don't affect the precedence:
SELECT 1 + (2 * 5) -- the same as 1 + 2 * 5
Assignment operators
editYou can use the = operator to assign a value to a column:
UPDATE `myTable` SET `uselessField`=0
When you want to assign a value to a variable, you must use the := operator, because the use of = would be ambiguous (is it as assignment or a comparison?)
SELECT @myvar := 1
You can also use SELECT INTO to assign values to one or more variables.