English | 简体中文 | 繁體中文 | Русский язык | Français | Español | Português | Deutsch | 日本語 | 한국어 | Italiano | بالعربية

MySql Tutorial

MySQL Operators

In this chapter, we mainly introduce the operators and operator precedence in MySQL. MySQL mainly has the following types of operators:

  • Arithmetic operators

  • Comparison operators

  • Logical operators

  • Bitwise Operators

Arithmetic operators

The arithmetic operators supported by MySQL include:

operatorEffect
+addition
-subtraction
*multiplication
/ or DIVdivision
% or MODmodulus

In division and modulus operations, if the divisor is 0, it will be an illegal divisor, and the return result will be NULL.

1and addition

mysql> select　1+2;
+-----+
|　1+2　|
+-----+
|　　　3　|
+-----+

2and subtraction

mysql> select　1-2;
+-----+
|　1-2　|
+-----+
|　　-1　|
+-----+

3and multiplication

mysql> select　2*3;
+-----+
|　2*3　|
+-----+
|　　　6　|
+-----+

4and division

mysql> select　2/3;
+--------+
|　2/3　　　　|
+--------+
| 0.6667　|
+--------+

5and quotient

mysql> select　10　DIV　4;
+----------+
|　10　DIV　4　|
+----------+
|　　　　　　　　2　|
+----------+

6and modulus

mysql> select　10　MOD　4;
+----------+
|　10　MOD　4　|
+----------+
|　　　　　　　　2　|
+----------+

Comparison operators

Conditional statements in SELECT statements often need to use comparison operators. Through these comparison operators, you can determine which records in the table meet the conditions. If the comparison result is true, then return 1If false, return 0, and if the comparison result is uncertain, return NULL.

SymbolDescriptionNote
=equal to
<>, !=not equal to
>greater than
less thanless than
<=less than or equal to
>=greater than or equal to
BETWEENbetween the two values>=min && <=max
NOT BETWEENnot between the two values
INin the set
NOT INnot in the set
<=>strictly compare whether two NULL values are equalThe value obtained when both operands are NULL is1The value obtained is 0 when both operands are NULL.
LIKEfuzzy match
REGEXP or RLIKEregular expression match
IS NULLis empty
IS NOT NULLis not empty

1and equal to

mysql> select　2=3;
+-----+
|　2=3　|
+-----+
|                                                         0 |
+-----+
mysql> select NULL = NULL;
+-------------+
| NULL = NULL |
+-------------+
| NULL |
+-------------+

2and not equal to

mysql> select　2<>3;
+------+
|　2<>3　|
+------+
|　　　　1　|
+------+

3and secure equal to

The difference between = and NULL lies in the value obtained when both operands are NULL. 1 is not NULL, while a value of 0 is returned when an opcode is NULL, and it is not NULL.

mysql> select　2<=>3;
+-------+
|　2<=>3　|
+-------+
|                 0 |
+-------+
mysql> select null=null;
+-----------+
| null=null |
+-----------+
|                                                         NULL |
+-----------+
　　　　　　　　
mysql> select null<=>null;
+-------------+
| null<=>null |
+-------------+
|　　　　　　　　　　　1　|
+-------------+

4less than

mysql> select　2less than3;
+-----+
|　2less than3　|
+-----+
|　　　1　|
+-----+

5less than or equal to

mysql> select　2<=3;
+------+
|　2<=3　|
+------+
|　　　　1　|
+------+

6greater than

mysql> select　2>3;
+-----+
|　2>3　|
+-----+
|                                                         0 |
+-----+

7greater than or equal to

mysql> select　2>=3;
+------+
|　2>=3　|
+------+
|                                                         0 |
+------+

8BETWEEN

mysql> select　5　between　1　and　10;
+--------------------+
|　5　between　1　and　10　|
+--------------------+
|　　　　　　　　　　　　　　　　　　1　|
+--------------------+

9IN

mysql> select　5　in (1,2,3,4,5);
+------------------+
|　5　in (1,2,3,4,5) |
+------------------+
|　　　　　　　　　　　　　　　　1　|
+------------------+

10NOT IN

mysql> select　5　not in (1,2,3,4,5);
+----------------------+
|　5　not in (1,2,3,4,5) |
+----------------------+
|                                                         0 |
+----------------------+

11IS NULL

mysql> select null is NULL;
+--------------+
| null is NULL |
+--------------+
|　　　　　　　　　　　　1　|
+--------------+
mysql> select a is NULL;
+-------------+
| a is NULL |
+-------------+
|                                                         0 |
+-------------+

12IS NOT NULL

mysql> select null IS NOT NULL;
+------------------+
| null IS NOT NULL |
+------------------+
|                                                         0 |
+------------------+
　　　　　　　　
mysql> select a IS NOT NULL;
+-----------------+
| a IS NOT NULL |
+-----------------+
|　　　　　　　　　　　　　　　1　|
+-----------------+

13LIKE

mysql> select '';12345' like '12'';
+--------------------+
| '' |12345' like '12'' |
+--------------------+
|　　　　　　　　　　　　　　　　　　1　|
+--------------------+
mysql> select '';12345' like '12_';
+--------------------+
| '' |12345' like '12'' |
+--------------------+
|                                                         0 |
+--------------------+

14REGEXP

mysql> select 'beijing' REGEXP 'jing';
+-------------------------+
| beijing REGEXP jing |
+-------------------------+
|　　　　　　　　　　　　　　　　　　　　　　　1　|
+-------------------------+
mysql> select 'beijing' REGEXP 'xi';
+-----------------------+
| beijing REGEXP xi |
+-----------------------+
|                                                         0 |
+-----------------------+

Logical operators

Logical operators are used to determine the truth or falsity of an expression. If the expression is true, the result returns 1If the expression is false, the result returns 0.

OperatorEffect
NOT or !logical not
ANDlogical and
ORlogical or
XORlogical exclusive or

1and

mysql> select　2　and 0;
+---------+
|　2　and 0 |
+---------+
|                 0 |
+---------+
　　　　　　　　
mysql> select　2　and　1;　　　
+---------+　　　　　
|　2　and　1　|　　　　　　
+---------+　　　　　　
|　　　　　　　1　|　　　　　　
+---------+

2or

mysql> select　2　or 0;
+--------+
|　2　or 0 |
+--------+
|　　　　　　1　|
+--------+
mysql> select　2　or　1;
+--------+
|　2　or　1　|
+--------+
|　　　　　　1　|
+--------+
mysql> select 0 or 0;
+--------+
| 0 or 0 |
+--------+
|                   0 |
+--------+
mysql> select　1　|| 0;
+--------+
|　1　|| 0 |
+--------+
|　　　　　　1　|
+--------+

3, NOT

mysql> select not　1;
+-------+
| not　1　|
+-------+
|                 0 |
+-------+
mysql> select !0;
+----+
| !0 |
+----+
|　　1　|
+----+

4, XOR

mysql> select　1　xor　1;
+---------+
|　1　xor　1　|
+---------+
|                 0 |
+---------+
mysql> select 0 xor 0;
+---------+
| 0 xor 0 |
+---------+
|                 0 |
+---------+
mysql> select　1　xor 0;
+---------+
|　1　xor 0 |
+---------+
|　　　　　　　1　|
+---------+
mysql> select null or　1;
+-----------+
| null or　1　|
+-----------+
|　　　　　　　　　1　|
+-----------+
mysql> select　1　^ 0;
+-------+
|　1　^ 0 |
+-------+
|　　　　　1　|
+-------+

Bitwise Operators

Bitwise operators are operators that perform calculations on binary numbers. Bitwise operations first convert the operands to binary numbers, perform bitwise operations, and then convert the calculation results from binary numbers back to decimal numbers.

OperatorEffect
&Bitwise AND
|Bitwise OR
^Bitwise XOR
!NOT
<<Left Shift
>>Right Shift

1, Bitwise AND

mysql> select　3&5;
+-----+
|　3&5　|
+-----+
|　　　1　|
+-----+

2, Bitwise OR

mysql> select　3|5;
+-----+
|　3|5　|
+-----+
|　　　7　|
+-----+

3, Bitwise XOR

mysql> select　3^5;
+-----+
|　3^5　|
+-----+
|　　　6　|
+-----+

4, Bitwise NOT

mysql> select ~18446744073709551612;
+-----------------------+
| ~18446744073709551612　|
+-----------------------+
|　　　　　　　　　　　　　　　　　　　　　3　|
+-----------------------+

5, Bitwise Right Shift

mysql> select　3>>1;
+------+
|　3>>1　|
+------+
|　　　　1　|
+------+

6, Bitwise Left Shift

mysql> select　3<<1;
+------+
|　3<<1　|
+------+
|　　　　6　|
+------+

Operator Precedence

The lowest priority is: :=.

The highest priority is: !, BINARY, COLLATE.

MySQL Functions