Operators
This document describes the operators precedence, comparison functions and operators, logical operators, and assignment operators.
| Name | Description |
|---|---|
| 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 |
| BINARY | Cast a string to a binary string |
| & | Bitwise AND |
| ~ | Bitwise inversion |
| | | Bitwise OR |
| ^ | Bitwise XOR |
| CASE | Case operator |
| DIV | Integer division |
| / | Division operator |
| = | Equal operator |
<=> | NULL-safe equal to operator |
| > | Greater than operator |
| >= | Greater than or equal operator |
| IS | Test a value against a boolean |
| IS NOT | Test a value against a boolean |
| IS NOT NULL | NOT NULL value test |
| IS NULL | NULL 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 |
| LIKE | Simple pattern matching |
| ILIKE | Simple pattern matching with case insensitive (Supported in TiDB, but not supported in MySQL) |
| - | Minus operator |
| %, MOD | Modulo operator |
| NOT, ! | Negates value |
| NOT BETWEEN ... AND ... | Check whether a value is not within a range of values |
!=, <> | Not equal operator |
| NOT LIKE | Negation of simple pattern matching |
| NOT REGEXP | Negation of REGEXP |
| ||, OR | Logical OR |
| + | Addition operator |
| REGEXP | Pattern matching using regular expressions |
| >> | Right shift |
| RLIKE | Synonym for REGEXP |
| * | Multiplication operator |
| - | Change the sign of the argument |
| XOR | Logical 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.
INTERVAL
BINARY, COLLATE
!
- (unary minus), ~ (unary bit inversion)
^
*, /, DIV, %, MOD
-, +
<<, >>
&
|
= (comparison), <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
BETWEEN, CASE, WHEN, THEN, ELSE
NOT
AND, &&
XOR
OR, ||
= (assignment), :=
For details, see Operator Precedence.
Comparison functions and operators
| Name | Description |
|---|---|
| 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 |
| IS | Test a value against a boolean |
| IS NOT | Test a value against a boolean |
| IS NOT NULL | NOT NULL value test |
| IS NULL | NULL value test |
| ISNULL() | Test whether the argument is NULL |
| LEAST() | Return the smallest argument |
| < | Less than operator |
| <= | Less than or equal operator |
| LIKE | Simple pattern matching |
| ILIKE | Simple 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 LIKE | Negation of simple pattern matching |
| STRCMP() | Compare two strings |
For details, see Comparison Functions and Operators.
Logical operators
| Name | Description |
|---|---|
| AND, && | Logical AND |
| NOT, ! | Negates value |
| ||, OR | Logical OR |
| XOR | Logical XOR |
For details, see MySQL Handling of GROUP BY.
Assignment operators
| Name | Description |
|---|---|
| = | 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
ILIKEoperator.