MySQL中的运算符

· 大约 600 个字 · 预计 3 分钟 读完
数据库 · MySQL
数据库 · MySQL · 运算符

前言

上一篇对MySQL的基础有了一个初步认识，接下来我们看一下MySQL中的运算符，这些运算符可以用来连接表达式，这一篇多为SQL语句。

算数运算符

算数运算符包括加、减、乘、除和模运算。

运算符作用
+加法
-减法
*乘法
/,DIV除法，返回商
%,MOD除法，返回余数
mysql> select 0.1+0.3333, 0.1-0.3333, 0.1*0.3333, 1/2, 1%2;
+------------+------------+------------+--------+------+
| 0.1+0.3333 | 0.1-0.3333 | 0.1*0.3333 | 1/2    | 1%2  |
+------------+------------+------------+--------+------+
|     0.4333 |    -0.2333 |    0.03333 | 0.5000 |    1 |
+------------+------------+------------+--------+------+
1 row in set (0.01 sec)

值得注意的是如果除数是0则是非法的，返回结果为NULL.

比较运算符

比较运算符如果比较结果为真则返回1，如果比较结果为假则返回0，如果结果不确定则返回NULL.

运算符作用
=等于
<>或!=不等于
<=>NULL安全的等于
<小于
<=小于等于
>大于
>=大于等于
BETWEEN存在于指定范围
IN存在于指定集合
IS NULL是否为NULL
IS NOT NULL不为NULL
LIKE通配符匹配
REGEXP 或 RLIKE正则表达式匹配
mysql> select 1=0, 1=1, NULL=NULL;
+-----+-----+-----------+
| 1=0 | 1=1 | NULL=NULL |
+-----+-----+-----------+
|   0 |   1 |      NULL |
+-----+-----+-----------+
1 row in set (0.00 sec)

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

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

mysql> select 10 between 10 and 20, 9 between 10 and 20;
+----------------------+---------------------+
| 10 between 10 and 20 | 9 between 10 and 20 |
+----------------------+---------------------+
|                    1 |                   0 |
+----------------------+---------------------+
1 row in set (0.01 sec)

mysql> select 'abcdef' regexp 'ab', 'abcdefg' regexp 'k';
+----------------------+----------------------+
| 'abcdef' regexp 'ab' | 'abcdefg' regexp 'k' |
+----------------------+----------------------+
|                    1 |                    0 |
+----------------------+----------------------+
1 row in set (0.00 sec)

逻辑运算符

运算符作用
NOT或!逻辑非
AND或&&逻辑与
OR或||逻辑或
XOR逻辑异或
mysql> select not 0, not 1, not null;
+-------+-------+----------+
| not 0 | not 1 | not null |
+-------+-------+----------+
|     1 |     0 |     NULL |
+-------+-------+----------+
1 row in set (0.00 sec)

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

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

这里说一下异或运算XOR,当任意一个操作数是NULL时返回NULL,如果两个的逻辑真假值相异则返回1，否则返回0.

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

位运算符

运算符作用
&位与
|位或
^位异或
~位取反
>>位右移
<<位左移
mysql> select 2&3;
+-----+
| 2&3 |
+-----+
|   2 |
+-----+
1 row in set (0.00 sec)

2的二进制是10，3的二进制是11，按位与10&11结果是10，所以是2.

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

mysql> select 100>>3;
+--------+
| 100>>3 |
+--------+
|     12 |
+--------+
1 row in set (0.00 sec)

100的二进制是0001100100，右移三位是0000001100，转换为10进制数是12