https://yuluhuang.com

MySQL运算符介绍

分类

算数运算符， 比较运算符， 逻辑运算符， 位操作运算符

算数运算符

运算符 作用
+ 加法运算符
- 减法运算符
* 乘法运算符
/ 除法运算符
% 求余运算符

比较运算符

比较运算符的结果总是1， 0， null

运算符 作用
= 等于
<=> 安全的等于
<>/!= 不等于
<= 小于等于
>= 大于等于
> 大于
< 小于
IS NULL
IS NOT NULL
LEAST 返回最小值
GREATEST 返回最大值
BETWEEN AND 判断一个值是否在两个值之间
ISNULL 判断一个值是否为null
IN 判断一个值在in 后面的列表中
NOT IN 判断一个值不在in 后面的列表中
LIKE 通配符匹配
REGEXP 正则表达式匹配
  1. 和 null 运算的结果都为null

    = 和 <=>

1
2
3
4
5
6
mysql> select 1=0, '2'=2, null=null, '2'<=>2,null<=>null, 2=null;
+-----+-------+-----------+---------+-------------+--------+
| 1=0 | '2'=2 | null=null | '2'<=>2 | null<=>null | 2=null |
+-----+-------+-----------+---------+-------------+--------+
|   0 |     1 |      NULL |       1 |           1 |   NULL |
+-----+-------+-----------+---------+-------------+--------+

<> or !=

1
2
3
4
5
6
mysql> select 1<>0, '2'<>2, '2'!=null, null<>null,null!=null;
+------+--------+-----------+------------+------------+
| 1<>0 | '2'<>2 | '2'!=null | null<>null | null!=null |
+------+--------+-----------+------------+------------+
|    1 |      0 |      NULL |       NULL |       NULL |
+------+--------+-----------+------------+------------+

IS NULL AND IS NOT NULL

1
2
3
4
5
6
mysql> select NULL IS NULL, ISNULL(NULL), 10 IS NOT NULL;
+--------------+--------------+----------------+
| NULL IS NULL | ISNULL(NULL) | 10 IS NOT NULL |
+--------------+--------------+----------------+
|            1 |            1 |              1 |
+--------------+--------------+----------------+

BETWEEN AND

1
2
3
4
5
6
mysql> select 4 BETWEEN 4 AND 6, 6 BETWEEN 6 AND 9;
+-------------------+-------------------+
| 4 BETWEEN 4 AND 6 | 6 BETWEEN 6 AND 9 |
+-------------------+-------------------+
|                 1 |                 1 |
+-------------------+-------------------+

LEAST

1
2
3
4
5
6
mysql> SELECT LEAST(2, 0), LEAST(1, 1.1, 101), LEAST(10, NULL);
+-------------+--------------------+-----------------+
| LEAST(2, 0) | LEAST(1, 1.1, 101) | LEAST(10, NULL) |
+-------------+--------------------+-----------------+
|           0 |                1.0 |            NULL |
+-------------+--------------------+-----------------+

GREATEST

1
2
3
4
5
6
mysql> SELECT GREATEST(2, 0), GREATEST(1, 1.1, 101), GREATEST(10, NULL);
+----------------+-----------------------+--------------------+
| GREATEST(2, 0) | GREATEST(1, 1.1, 101) | GREATEST(10, NULL) |
+----------------+-----------------------+--------------------+
|              2 |                 101.0 |               NULL |
+----------------+-----------------------+--------------------+

IN , NOT IN

1
2
3
4
5
6
mysql> SELECT 2 IN(1,2,3,'A','B'), 'A' IN('A',1), NULL IN(NULL,1);
+---------------------+---------------+-----------------+
| 2 IN(1,2,3,'A','B') | 'A' IN('A',1) | NULL IN(NULL,1) |
+---------------------+---------------+-----------------+
|                   1 |             1 |            NULL |
+---------------------+---------------+-----------------+

LIKE

1
2
3
4
5
6
mysql> SELECT 'ABC' LIKE 'ABC', 'ABC' LIKE 'AB_', 'ABC' LIKE 'A__', 'A%' LIKE 'ABC';
+------------------+------------------+------------------+-----------------+
| 'ABC' LIKE 'ABC' | 'ABC' LIKE 'AB_' | 'ABC' LIKE 'A__' | 'A%' LIKE 'ABC' |
+------------------+------------------+------------------+-----------------+
|                1 |                1 |                1 |               0 |
+------------------+------------------+------------------+-----------------+

REGEXP

1
2
3
4
5
6
mysql> SELECT 'ABC' REGEXP '^A', 'ABC' REGEXP 'C$', 'ABC' REGEXP '.BC', 'AC' REGEXP 'A-Z';
+-------------------+-------------------+--------------------+-------------------+
| 'ABC' REGEXP '^A' | 'ABC' REGEXP 'C$' | 'ABC' REGEXP '.BC' | 'AC' REGEXP 'A-Z' |
+-------------------+-------------------+--------------------+-------------------+
|                 1 |                 1 |                  1 |                 0 |
+-------------------+-------------------+--------------------+-------------------+