mysql运算符

运算符

基本运算符

注意除以0和null的时候都返回null

mysql> select 1+1,1-1,1*2,1/2,1%2,1/0,1/null,1*0;
+-----+-----+-----+--------+------+------+--------+-----+
| 1+1 | 1-1 | 1*2 | 1/2    | 1%2  | 1/0  | 1/null | 1*0 |
+-----+-----+-----+--------+------+------+--------+-----+
|   2 |   0 |   2 | 0.5000 |    1 | NULL |   NULL |   0 |
+-----+-----+-----+--------+------+------+--------+-----+
1 row in set (0.00 sec)

比较运算符

当表达式中有null时，会返回null,<=>可以用来判断null

mysql> select 1=1,1='1',1!=1,1=null,1<=>null,null=null,1!=2,1<>2,1<=2,1>=2;
+-----+-------+------+--------+----------+-----------+------+------+------+------+
| 1=1 | 1='1' | 1!=1 | 1=null | 1<=>null | null=null | 1!=2 | 1<>2 | 1<=2 | 1>=2 |
+-----+-------+------+--------+----------+-----------+------+------+------+------+
|   1 |     1 |    0 |   NULL |        0 |      NULL |    1 |    1 |    1 |    0 |
+-----+-------+------+--------+----------+-----------+------+------+------+------+
1 row in set (0.00 sec)

空值运算符

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

between运算符

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

least运算符

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

greatest运算符

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

in和not in运算符

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

like运算符

%代表任意个字符，_代表单个字符

mysql> select 'abc' like 'abc','abcd' like 'abc','abcde' like 'abc%','abcde' like 'abc_','abcde' like 'abcd_';
+------------------+-------------------+---------------------+---------------------+----------------------+
| 'abc' like 'abc' | 'abcd' like 'abc' | 'abcde' like 'abc%' | 'abcde' like 'abc_' | 'abcde' like 'abcd_' |
+------------------+-------------------+---------------------+---------------------+----------------------+
|                1 |                 0 |                   1 |                   0 |                    1 |
+------------------+-------------------+---------------------+---------------------+----------------------+
1 row in set (0.00 sec)

regexp运算符

正则表达式

mysql> select 'abc' regexp '^a';
+-------------------+
| 'abc' regexp '^a' |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

逻辑运算符

mysql> select 1 and 1,1 and 2,1 and 0,1 or 0,!1,!0,!null,1 xor 1,1 xor 0;
+---------+---------+---------+--------+----+----+-------+---------+---------+
| 1 and 1 | 1 and 2 | 1 and 0 | 1 or 0 | !1 | !0 | !null | 1 xor 1 | 1 xor 0 |
+---------+---------+---------+--------+----+----+-------+---------+---------+
|       1 |       1 |       0 |      1 |  0 |  1 |  NULL |       0 |       1 |
+---------+---------+---------+--------+----+----+-------+---------+---------+
1 row in set (0.00 sec)

位或运算符

mysql> select 10|15;
+-------+
| 10|15 |
+-------+
|    15 |
+-------+
1 row in set (0.00 sec)

位与运算符

mysql> select 10&15;
+-------+
| 10&15 |
+-------+
|    10 |
+-------+
1 row in set (0.00 sec)

位异或运算符

mysql> select 10^15;
+-------+
| 10^15 |
+-------+
|     5 |
+-------+
1 row in set (0.00 sec)

位左移运算符

mysql> select 10<<2;
+-------+
| 10<<2 |
+-------+
|    40 |
+-------+
1 row in set (0.00 sec)

位右移运算符

mysql> select 10>>2;
+-------+
| 10>>2 |
+-------+
|     2 |
+-------+
1 row in set (0.00 sec)

位取反运算符

mysql> select ~2;
+----------------------+
| ~2                   |
+----------------------+
| 18446744073709551613 |
+----------------------+
1 row in set (0.00 sec)