mysql 操作符

1、=等号操作符

用于比较两个变量是否相等。

注意：在比较时，许多字符串会自动被转换为整数

mysql> select 1 = 0; //不相等，返回0
+-------+
| 1 = 0 |
+-------+
|     0 |
+-------+
1 row in set (0.02 sec)

mysql> select '0' = 0; //返回1，说明相等，此时字符串转换为整型
+---------+
| '0' = 0 |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

mysql> select '0.0' = 0; //返回1，相等
+-----------+
| '0.0' = 0 |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

mysql> select '0.01' = 0; //返回0，说明不相等
+------------+
| '0.01' = 0 |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)

mysql> select '.01' = 0.01; //返回1，相等
+--------------+
| '.01' = 0.01 |
+--------------+
|            1 |
+--------------+
1 row in set (0.12 sec)

 

2、<=> NULL-safe equal. 这个操作符与=操作符执行相同的比较操作。其区别是当两个操作数为null时，其返回值为1而不是null；当其中一个操作数为null时，其返回值为0而不是null。

mysql> select null <=> null, 1 <=> null, 1 <=> 1;
+---------------+------------+---------+
| null <=> null | 1 <=> null | 1 <=> 1 |
+---------------+------------+---------+
|             1 |          0 |       1 |
+---------------+------------+---------+
1 row in set (0.02 sec)

mysql> select null = null, 1 = null, 1 = 1;
+-------------+----------+-------+
| null = null | 1 = null | 1 = 1 |
+-------------+----------+-------+
|        NULL |     NULL |     1 |
+-------------+----------+-------+
1 row in set (0.00 sec)

 

3、<> !=不等于

mysql> select '.01' <> '0.01';
+-----------------+
| '.01' <> '0.01' |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.04 sec)

mysql> select 0.01 <> '0.01';
+----------------+
| 0.01 <> '0.01' |
+----------------+
|              0 |
+----------------+
1 row in set (0.00 sec)

 

4、is boolean_value/is not boolean_value。Boolean判断，boolean_value的值可以为true/false/unknown

mysql> select 1 is true, 0 is not true;
+-----------+---------------+
| 1 is true | 0 is not true |
+-----------+---------------+
|         1 |             1 |
+-----------+---------------+
1 row in set (0.00 sec)

 

5、is null/is not null 判断一个值是否为null

mysql> select 1 is not null, 0 is not null, null is null;
+---------------+---------------+--------------+
| 1 is not null | 0 is not null | null is null |
+---------------+---------------+--------------+
|             1 |             1 |            1 |
+---------------+---------------+--------------+
1 row in set (0.00 sec)

 

6、expr [not] between min and max 判断expr的值是否位于min与max之间

mysql> select 1 not between 2 and 3, 2 between 2 and 3;
+-------------------+-------------------+
| 1 between 2 and 3 | 2 between 2 and 3 |
+-------------------+-------------------+
|                 1 |                 1 |
+-------------------+-------------------+
1 row in set (0.00 sec)

 

7、expr [not] in (value1, value2, value3, ...) 判断expr是否等于列表中的任一值

mysql> select 2 in (1, 2, 3), 4 not in (1, 2, 3);
+----------------+--------------------+
| 2 in (1, 2, 3) | 4 not in (1, 2, 3) |
+----------------+--------------------+
|              1 |                  1 |
+----------------+--------------------+
1 row in set (0.00 sec)