Easy Tutorial
Mysql Administration Mysql Func Ifnull Mysql Like Clause Mysql Operator Mysql Create Tables Mysql Data Types Mysql Database Info Mysql Group By Statement Mysql Regexp Mysql Database Import Mysql Select Query Mysql Select Database Mysql Temporary Tables Mysql Tutorial Mysql Using Sequences Mysql Drop Tables Mysql Connection Mysql Insert Query Mysql Index Mysql Install Mysql Functions Mysql Sql Injection Mysql Delete Query Mysql Transaction Mysql Php Syntax Mysql Union Operation Mysql Clone Tables Mysql Handling Duplicates Mysql Null Mysql Create Database Mysql Join Mysql Alter Mysql Update Query Mysql Where Clause Mysql Database Export Mysql Order By Mysql Drop Database
❮ Mysql Like Clause Mysql Create Tables ❯

MySQL Operators

Arithmetic Operators

MySQL supports the following arithmetic operators:

Operator Description
+ Addition
- Subtraction
* Multiplication
/ or DIV Division
% or MOD Modulus

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

  1. Addition

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

  2. Subtraction

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

  3. Multiplication

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

  4. Division

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

  5. Quotient

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

  6. Modulus

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

Comparison Operators

Comparison operators are often used in the WHERE clause of the SELECT statement. These operators determine which records in the table meet the conditions. If the comparison is true, it returns 1; if false, it returns 0; and if the result is uncertain, it returns NULL.

Symbol Description Remark
= Equal
<>, != Not Equal
> Greater Than
< Less Than
<= Less Than or Equal
>= Greater Than or Equal
BETWEEN Between Two Values >=min&&<=max
NOT BETWEEN Not Between Two Values
IN In a Set
NOT IN Not In a Set
<=> NULL-Safe Equal Returns 1 if both operands are NULL, 0 if one operand is NULL
LIKE Pattern Matching
REGEXP or RLIKE Regular Expression Matching
IS NULL Is NULL
IS NOT NULL Is Not NULL

  1. Equal

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

mysql> select NULL = NULL;
+-------------+
| NULL = NULL |
+-------------+
|        NULL |
+-------------+

  2. Not Equal

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

  3. NULL-Safe Equal

The difference from = is that it returns 1 if both operands are NULL, and 0 if one operand is NULL, instead of NULL.

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

mysql> select null=null;
+-----------+
| null=null |
+-----------+
|      NULL |
+-----------+

mysql> select null<=>null;
+-------------+
| null<=>null |
+-------------+
|           1 |
+-------------+

  1. Less Than

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

  2. Less Than or Equal

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

  3. Greater Than

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

  4. Greater Than or Equal

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

  5. BETWEEN

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

  6. IN

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

  7. NOT IN

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

  8. IS NULL

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

mysql> select 'a' is NULL;
+-------------+
| 'a' is NULL |
+-------------+
|           0 |
+-------------+

  9. IS NOT NULL

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

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

  10. LIKE

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

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

  11. REGEXP

    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 of an expression. If the expression is true, it returns 1. If the expression is false, it returns 0.

Operator Description
NOT or ! Logical NOT
AND Logical AND
OR Logical OR
XOR Logical XOR

  1. AND

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

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

  2. OR

    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 perform bit manipulations on binary numbers. These operators first convert the operands to binary numbers, perform the bit operations, and then convert the result back to a decimal number.

Operator Description
& Bitwise AND
| Bitwise OR
^ Bitwise XOR
~ Bitwise 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 precedence is: `:=`.

The highest precedence is: `!`, `BINARY`, `COLLATE`.
❮ Mysql Like Clause Mysql Create Tables ❯