mysql 运算符

Dear 丶 2021-07-28 17:32 582阅读 0赞

本章节我们主要介绍 MySQL 的运算符及运算符的优先级。 MySQL 主要有以下几种运算符：

*  算术运算符
 *  比较运算符
 *  逻辑运算符
 *  位运算符

--------------------

## 算术运算符 ##

MySQL 支持的算术运算符包括:

在除法运算和模运算中，如果除数为0，将是非法除数，返回结果为NULL。

1、加

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

2、减

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

3、乘

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

4、除

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

5、商

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

6、取余

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

--------------------

## 比较运算符 ##

SELECT 语句中的条件语句经常要使用比较运算符。通过这些比较运算符，可以判断表中的哪些记录是符合条件的。比较结果为真，则返回 1，为假则返回 0，比较结果不确定则返回 NULL。

<table> 
 <thead> 
  <tr> 
   <th>符号</th> 
   <th>描述</th> 
   <th>备注</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>=</td> 
   <td>等于</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>&lt;&gt;, !=</td> 
   <td>不等于</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>&gt;</td> 
   <td>大于</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>&lt;</td> 
   <td>小于</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>&lt;=</td> 
   <td>小于等于</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>&gt;=</td> 
   <td>大于等于</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>BETWEEN</td> 
   <td>在两值之间</td> 
   <td>&gt;=min&amp;&amp;&lt;=max</td> 
  </tr> 
  <tr> 
   <td>NOT BETWEEN</td> 
   <td>不在两值之间</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>IN</td> 
   <td>在集合中</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>NOT IN</td> 
   <td>不在集合中</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>&lt;=&gt;</td> 
   <td>严格比较两个NULL值是否相等</td> 
   <td>两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0</td> 
  </tr> 
  <tr> 
   <td>LIKE</td> 
   <td>模糊匹配</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>REGEXP 或 RLIKE</td> 
   <td>正则式匹配</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>IS NULL</td> 
   <td>为空</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>IS NOT NULL</td> 
   <td>不为空</td> 
   <td>&nbsp;</td> 
  </tr> 
 </tbody> 
</table>

1、等于

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

2、不等于

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

3、安全等于

与 = 的区别在于当两个操作码均为 NULL 时，其所得值为 1 而不为 NULL，而当一个操作码为 NULL 时，其所得值为 0而不为 NULL。

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

4、小于

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

5、小于等于

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

6、大于

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

7、大于等于

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

8、BETWEEN

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

9、IN

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

10、NOT IN

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

11、IS NULL

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

12、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 |
    +-----------------+

13、LIKE

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

14、REGEXP

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

--------------------

## 逻辑运算符 ##

逻辑运算符用来判断表达式的真假。如果表达式是真，结果返回 1。如果表达式是假，结果返回 0。

1、与

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

2、或

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、非

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

4、异或

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 |
    +-------+

--------------------

## 位运算符 ##

位运算符是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数，进行位运算。然后再将计算结果从二进制数变回十进制数。

1、按位与

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

2、按位或

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

3、按位异或

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

4、按位取反

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

5、按位右移

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

6、按位左移

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

--------------------

## 运算符优先级 ##

最低优先级为： :=。

![format_png][]

最高优先级为： !、BINARY、 COLLATE。

[format_png]: /images/20210728/d39ce3e4f29c448f89544ed8aa492df8.png