This topic describes the operators supported by PolarDB-X.
Logical operators
PolarDB-X supports the following logical operators.
Operator | Description |
Operator | Description |
AND, && | The logical AND operator. |
NOT, ! | The logical NOT operator. |
||, OR | The logical OR operator. |
XOR | The logical XOR operator. |
Arithmetic operators
PolarDB-X supports the following arithmetic operators.
Operator | Description |
Operator | Description |
/, DIV | The division operator. |
%, MOD | Returns the remainder that is obtained when a number is divided by another number. |
+ | The addition operator. |
* | The multiplication operator. |
- | The subtraction operator. |
Comparison operators
Comparison operators are frequently used in conditional SELECT statements. Comparison operators allow you to determine which records in a table meet the requirements. If the result of a comparison is true, 1 is returned. If the result is false, 0 is returned. If the result is uncertain, NULL is returned.
PolarDB-X supports the following comparison operators.
Operator | Description |
Operator | Description |
= | Tests whether a value is equal to another value. |
<>, != | Tests whether a value is not equal to another value. |
> | Tests whether a value is greater than another value. |
< | Tests whether a value is less than another value. |
<= | Tests whether a value is not greater than another value. |
>= | Tests whether a value is not less than another value. |
BETWEEN | Tests whether a value lies between two values. >=min&&<=max |
NOT BETWEEN | Tests whether a value does not lie between two values. |
IN | Tests whether a value is in a set. |
NOT IN | Tests whether a value is not in a set. |
<=> | Tests whether two values are equal. The operands can be NULL. If both operands are NULL, 1 is returned. If one operand is NULL, 0 is returned. |
LIKE | This operator is used for fuzzy matching. |
REGEXP or RLIKE | This operator is used for matching that is based on regular expressions. |
IS NULL | Tests whether a value is NULL. |
IS NOT NULL | Tests whether a value is not NULL. |
Bitwise operators
PolarDB-X supports the following bitwise operators.
Operator | Description |
Operator | Description |
& | The bitwise AND operator. |
~ | The Bitwise NOT operator. |
l | The Bitwise OR operator. |
^ | The Bitwise XOR operator. |
<< | The left shift operator. |
>> | The right shift operator. |
Assignment operators
PolarDB-X supports the equality (=) assignment operator. In most cases, this operator is used in the SET clause of UPDATE statements.
PolarDB-X does not support the := assignment operator.
Operator precedence
The following table describes the precedence of operators that are supported by PolarDB-X. The operators are listed by precedence in descending order.
Precedence | Operator |
Precedence | Operator |
15 | ! |
14 | - (unary minus) and ~ |
13 | ^ |
12 | *, /, %, and MOD |
11 | + and - |
10 | <<,>> |
9 | & |
8 | | |
7 | = (comparison operator), <=>, >, >=, <, <=, <>, !=, IS, LIKE, REGEXP, and IN |
6 | BETWEEN |
5 | NOT |
4 | AND, && |
3 | XOR |
2 | OR, || |
1 | = (assignment operator) |
Note
This section provides sample code to describe whether the IN and NOT IN operators have higher precedence than the equality (=) comparison operator.
Execute the following SQL statements on a database that runs MySQL 5.7.19:
select binary 'a' = 'a' in (1, 2, 3);
+-------------------------------+
| binary 'a' = 'a' in (1, 2, 3) |
+-------------------------------+
| 1 |
+-------------------------------+
1 row in set, 1 warning (0.01 sec)
show warnings;
+---------+------+---------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a' |
+---------+------+---------------------------------------+
1 row in set (0.00 sec)
select 1 in (1, 2, 3) = 'a';
+----------------------+
| 1 in (1, 2, 3) = 'a' |
+----------------------+
| 0 |
+----------------------+
1 row in set, 1 warning (0.00 sec)
show warnings;
+---------+------+---------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a' |
+---------+------+---------------------------------------+
1 row in set (0.00 sec)
The returned result shows that the IN and NOT IN operators have higher precedence than the equality (=) comparison operator in MySQL. The SQL statements in PolarDB-X are executed based on the same precedence rule. Operators of the same precedence are evaluated in order from left to right.
