MENU


Operators

To illustrate MySQL's operators and built-in functions, most examples in this section (3.7) use the SELECT clause, but the same operators and functions may be used in other clauses such as WHERE and HAVING.

The operators below are listed from highest to lowest precedence — an operator earlier in the list binds more tightly than one that follows it:
BINARY (binary string casting), COLLATE
!
- (unary minus), ~ (unary bit inversion)
^ (bitwise XOR)
*, /, DIV (integer division), %, MOD (same as %)
-, +
<< (bit left shift), >> (bit right shift)
& (bitwise AND)
|
= (comparison), <=> (NULL-safe equal), >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
BETWEEN...AND
CASE...WHEN...THEN...ELSE...END
NOT
&&, AND
XOR
||, OR
= (assignment), :=

Arithmetic and Bitwise

SELECT 11 DIV 2;

Integer division, discarding the remainder: 5.


SELECT 11 DIV 2;

11 DIV 2
5

Comparison

SELECT 1 = 1, NULL = NULL, 1 = NULL;

1, NULL, NULL — any comparison against NULL with plain = yields NULL, never TRUE or FALSE.

SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;

1, 1, 0 — the NULL-safe equal operator treats NULL as a comparable value instead of propagating NULL.

SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN, NULL IS NOT UNKNOWN;

1, 1, 1, 0

SELECT 0 IS NULL, 0 IS NOT NULL, FALSE IS NULL, NULL IS FALSE;

0, 1, 0, 0 — note that NULL IS FALSE is 0, not 1; see NULL Handling for more on three-valued logic.


SELECT 1 = 1, NULL = NULL, 1 = NULL;

SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;

SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN, NULL IS NOT UNKNOWN;

SELECT 0 IS NULL, 0 IS NOT NULL, FALSE IS NULL, NULL IS FALSE;

SELECT 1 = 1, NULL = NULL, 1 = NULL:
1 = 1NULL = NULL1 = NULL
1NULLNULL
SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL:
1 <=> 1NULL <=> NULL1 <=> NULL
110
SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN, NULL IS NOT UNKNOWN:
1 IS TRUE0 IS FALSENULL IS UNKNOWNNULL IS NOT UNKNOWN
1110
SELECT 0 IS NULL, 0 IS NOT NULL, FALSE IS NULL, NULL IS FALSE:
0 IS NULL0 IS NOT NULLFALSE IS NULLNULL IS FALSE
0100

LIKE and Wildcards

SELECT 'ab' LIKE 'a%b', 'axxb' LIKE 'a%b', 'abx' LIKE 'a%b';

1, 1, 0 — % matches any number of characters, _ matches exactly one.

SELECT 'ab' LIKE 'a_b', 'axb' LIKE 'a_b', 'axxb' LIKE 'a_b';

0, 1, 0

SELECT 'a_b%' LIKE 'a\_b\%';
SELECT 'a_b%' NOT LIKE 'a\_b\%';

1, 0 — a backslash escapes a literal _ or % inside the pattern.


SELECT 'ab' LIKE 'a%b', 'axxb' LIKE 'a%b', 'abx' LIKE 'a%b';

SELECT 'ab' LIKE 'a_b', 'axb' LIKE 'a_b', 'axxb' LIKE 'a_b';

SELECT 'a_b%' LIKE 'a\_b\%';

SELECT 'a_b%' NOT LIKE 'a\_b\%';

SELECT 'ab' LIKE 'a%b', 'axxb' LIKE 'a%b', 'abx' LIKE 'a%b':
'ab' LIKE 'a%b''axxb' LIKE 'a%b''abx' LIKE 'a%b'
110
SELECT 'ab' LIKE 'a_b', 'axb' LIKE 'a_b', 'axxb' LIKE 'a_b':
'ab' LIKE 'a_b''axb' LIKE 'a_b''axxb' LIKE 'a_b'
010
SELECT 'a_b%' LIKE 'a\_b\%':
'a_b%' LIKE 'a\_b\%'
1
SELECT 'a_b%' NOT LIKE 'a\_b\%':
'a_b%' NOT LIKE 'a\_b\%'
0
A handy technique for testing against a fixed, delimited list of values:
SELECT * FROM Tags
  WHERE '|ruby|rails|scruffy|rubyonrails|' LIKE CONCAT('%|', Name, '|%');
SELECT * FROM mytable
WHERE column1 LIKE '%word1%'
  OR column1 LIKE '%word2%'
  OR column1 LIKE '%word3%';

REGEXP / RLIKE

SELECT 'abc' REGEXP '^[a-d]*';
SELECT 'x\n' REGEXP 'X\n', 'x\n' REGEXP BINARY 'X\n';
SELECT 'a' NOT REGEXP 'A';

1; 1, 0 (BINARY forces a case-sensitive byte comparison); 0 — [NOT] RLIKE is a synonym for [NOT] REGEXP. See Regular Expression for the full REGEXP_* function family.


SELECT 'abc' REGEXP '^[a-d]*';

SELECT 'x\n' REGEXP 'X\n', 'x\n' REGEXP BINARY 'X\n';

SELECT 'a' NOT REGEXP 'A';

SELECT 'abc' REGEXP '^[a-d]*':
'abc' REGEXP '^[a-d]*'
1
SELECT 'x\n' REGEXP 'X\n', 'x\n' REGEXP BINARY 'X\n':
'x\n' REGEXP 'X\n''x\n' REGEXP BINARY 'X\n'
10
SELECT 'a' NOT REGEXP 'A':
'a' NOT REGEXP 'A'
0

IN and BETWEEN

SELECT 'a' IN ('ab','0','c');
SELECT 10 NOT IN (10,20,30);

0; 0

SELECT 4 BETWEEN '4' AND 5;
SELECT 'y' BETWEEN 'x' AND 'Z';
SELECT 2 NOT BETWEEN 3 AND 1;

1; 1 (string comparison); 1 — BETWEEN does not reorder its bounds, so a NOT BETWEEN with a reversed range is always true.


SELECT 'a' IN ('ab','0','c');

SELECT 10 NOT IN (10,20,30);

SELECT 4 BETWEEN '4' AND 5;

SELECT 'y' BETWEEN 'x' AND 'Z';

SELECT 2 NOT BETWEEN 3 AND 1;

SELECT 'a' IN ('ab','0','c'):
'a' IN ('ab','0','c')
0
SELECT 10 NOT IN (10,20,30):
10 NOT IN (10,20,30)
0
SELECT 4 BETWEEN '4' AND 5:
4 BETWEEN '4' AND 5
1
SELECT 'y' BETWEEN 'x' AND 'Z':
'y' BETWEEN 'x' AND 'Z'
1
SELECT 2 NOT BETWEEN 3 AND 1:
2 NOT BETWEEN 3 AND 1
1

Assignment

SELECT @a:=5, @b=5;

:= assigns 5 to the session variable @a (and evaluates to 5); plain = here is an ordinary comparison between the (still unset) @b and 5, yielding NULL.


SELECT @a:=5, @b=5;

-- Assignment operator vs comparison operator
SET @a := 0;
SELECT @a := @a + 1 AS assigned, @a = 1 AS compared;

SELECT @a:=5, @b=5:
@a:=5@b=5
5NULL
SET @a := 0; SELECT @a := @a + 1 AS assigned, @a = 1 AS compared:
assignedcompared
11