Skip to content

Latest commit

 

History

History
144 lines (123 loc) · 11.5 KB

operators.md

File metadata and controls

144 lines (123 loc) · 11.5 KB
Raw
title summary aliases
Operators
Learn about the operators precedence, comparison functions and operators, logical operators, and assignment operators.
/docs/dev/functions-and-operators/operators/
/docs/dev/reference/sql/functions-and-operators/operators/

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 ILIKE operator.