Operators

This document describes the operators precedence, comparison functions and operators, logical operators, and assignment operators.

NameDescription
AND, &&Logical AND
=Assign a value (as part of a SET statement, or as part of the SET clause in an UPDATE statement)
:=Assign a value
BETWEEN … AND …Check whether a value is within a range of values
BINARYCast a string to a binary string
&Bitwise AND
~Bitwise inversion
|Bitwise OR
^Bitwise XOR
CASECase operator
DIVInteger division
/Division operator
=Equal operator
<=>NULL-safe equal to operator
>Greater than operator
>=Greater than or equal operator
ISTest a value against a boolean
IS NOTTest a value against a boolean
IS NOT NULLNOT NULL value test
IS NULLNULL value test
->Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT()
->>Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT())
<<Left shift
<Less than operator
<=Less than or equal operator
LIKESimple pattern matching
ILIKESimple pattern matching with case insensitive (Supported in TiDB, but not supported in MySQL)
-Minus operator
%, MODModulo operator
NOT, !Negates value
NOT BETWEEN … AND …Check whether a value is not within a range of values
!=, <>Not equal operator
NOT LIKENegation of simple pattern matching
NOT REGEXPNegation of REGEXP
||, ORLogical OR
+Addition operator
REGEXPPattern matching using regular expressions
>>Right shift
RLIKESynonym for REGEXP
*Multiplication operator
-Change the sign of the argument
XORLogical XOR

Unsupported operators

Operator precedence

Operator precedences are shown in the following list, from highest precedence to the lowest. Operators that are shown together on a line have the same precedence.

  1. INTERVAL
  2. BINARY, COLLATE
  3. !
  4. - (unary minus), ~ (unary bit inversion)
  5. ^
  6. *, /, DIV, %, MOD
  7. -, +
  8. <<, >>
  9. &
  10. |
  11. = (comparison), <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
  12. BETWEEN, CASE, WHEN, THEN, ELSE
  13. NOT
  14. AND, &&
  15. XOR
  16. OR, ||
  17. = (assignment), :=

For details, see Operator Precedence.

Comparison functions and operators

NameDescription
BETWEEN … AND …Check whether a value is within a range of values
COALESCE()Return the first non-NULL argument
=Equal operator
<=>NULL-safe equal to operator
>Greater than operator
>=Greater than or equal operator
GREATEST()Return the largest argument
IN()Check whether a value is within a set of values
INTERVAL()Return the index of the argument that is less than the first argument
ISTest a value against a boolean
IS NOTTest a value against a boolean
IS NOT NULLNOT NULL value test
IS NULLNULL value test
ISNULL()Test whether the argument is NULL
LEAST()Return the smallest argument
<Less than operator
<=Less than or equal operator
LIKESimple pattern matching
ILIKESimple pattern matching with case insensitive (Supported in TiDB, but not supported in MySQL)
NOT BETWEEN … AND …Check whether a value is not within a range of values
!=, <>Not equal operator
NOT IN()Check whether a value is not within a set of values
NOT LIKENegation of simple pattern matching
STRCMP()Compare two strings

For details, see Comparison Functions and Operators.

Logical operators

NameDescription
AND, &&Logical AND
NOT, !Negates value
||, ORLogical OR
XORLogical XOR

For details, see MySQL Handling of GROUP BY.

Assignment operators

NameDescription
=Assign a value (as part of a SET statement, or as part of the SET clause in an UPDATE statement)
:=Assign a value

For details, see Detection of Functional Dependence.

MySQL compatibility

  • MySQL does not support the ILIKE operator.