MySQL 运算符（五）

MySQL 支持多种类型的运算符，来连接表达式的项。这些类型主要包括算术运算符、比较 运算符、逻辑运算符和位运算符。

算术运算符 

 

基本用法： 

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.13 sec)

 除法运算和模运算中，如果除数为 0，将是非法除数，返回结果为 NULL，如下例所示:

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

对于模运算，还有另外一种表达方式，使用 MOD(a,b)函数与 a%b 效果一样:

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

比较运算符

熟悉了简单的算术运算符，再来看一下比较运算符。当使用SELECT语句进行查询时，MySQL 允许用户对表达式的左边操作数和右边操作数进行比较，比较结果为真，则返回 1，为假则 返回 0，比较结果不确定则返回 NULL。

比较运算符可以用于比较数字、字符串和表达式。数字作为浮点数比较，而字符串以不 区分大小写的方式进行比较。

“=”运算符

用于比较运算符两侧的操作数是否相等，如果两侧操作数相等返回值为 1， 否则为 0。注意 NULL 不能用于“=”比较。 

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

“<>”运算符

和“=”相反，如果两侧操作数不等，则值为 1，否则为 0。NULL 不能 用于“<>”比较

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

“<=>”安全的等于运算符

和“=”类似，在操作数相等时值为 1，不同之处在于即使 操作的值为 NULL 也可以正确比较。 

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

“<”运算符

当左侧操作数小于右侧操作数时，其返回值为 1，否则其值为 0。 

mysql>  select 'a'<'b' ,'a'<'a' ,'a'<'c',1<2;
+---------+---------+---------+-----+
| 'a'<'b' | 'a'<'a' | 'a'<'c' | 1<2 |
+---------+---------+---------+-----+
|       1 |       0 |       1 |   1 |
+---------+---------+---------+-----+
1 row in set (0.00 sec)

“<=”运算符

当左侧操作数小于等于右侧操作数时，其返回值为 1，否则返回值为 0。

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

“>”运算符

当左侧操作数大于右侧操作数时，其返回值为 1，否则返回值为 0。

mysql>  select 'a'>'b','abc'>'a' ,1>0;
+---------+-----------+-----+
| 'a'>'b' | 'abc'>'a' | 1>0 |
+---------+-----------+-----+
|       0 |         1 |   1 |
+---------+-----------+-----+
1 row in set (0.00 sec)

“>=”运算符

当左侧操作数大于等于右侧操作数时，其返回值为 1，否则返回值为 0。

mysql> select 'a'>='b','abc'>='a' ,1>=0 ,1>=1;
+----------+------------+------+------+
| 'a'>='b' | 'abc'>='a' | 1>=0 | 1>=1 |
+----------+------------+------+------+
|        0 |          1 |    1 |    1 |
+----------+------------+------+------+
1 row in set (0.00 sec)

BETWEEN  AND用法

“BETWEEN”运算符的使用格式为“a BETWEEN  min AND max”，当 a 大于等于 min 并 且小于等于 max，则返回值为 1，否则返回 0；当操作数 a、min、max 类型相同时，此 表达式等价于（a>=min and a<=max）， 当操作数类型不同时，比较时会遵循类型转换原 则进行转换后，再进行比较运算。下例中述了 BETWEEN 的用法： 

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.00 sec)

“IN”运算符

使用格式为“a IN (value1,value2,…)”,当 a 的值存在于列表中时，则整 个比较表达式返回的值为 1，否则返回 0。 

mysql>  select 1 in (1,2,3) , 't' in ('t','a','b','l','e'),0 in (1,2);
+--------------+------------------------------+------------+
| 1 in (1,2,3) | 't' in ('t','a','b','l','e') | 0 in (1,2) |
+--------------+------------------------------+------------+
|            1 |                            1 |          0 |
+--------------+------------------------------+------------+
1 row in set (0.33 sec)

“IS NULL”运算符

使用格式为“a IS NULL”,当 a 的值为 NULL，则返回值为 1，否则 返回 0。 

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

“IS NOT NULL”运算符

使用格式为“a IS NOT NULL”。和“IS NULL”相反，当 a的 值不为 NULL，则返回值为 1，否则返回 0。 

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

“LIKE”运算符

使用格式为“a LIKE %123%”,当 a中含有字符串“123”时，则返回 值为 1，否则返回 0

mysql> select 123456 like '123%',123456 like '%123%',123456 like '%321%';
+--------------------+---------------------+---------------------+
| 123456 like '123%' | 123456 like '%123%' | 123456 like '%321%' |
+--------------------+---------------------+---------------------+
|                  1 |                   1 |                   0 |
+--------------------+---------------------+---------------------+
1 row in set (1.67 sec)

“REGEXP”运算符

使用格式为“str  REGEXP str_pat”,当 str 字符串中含有 str_pat 相匹配的字符串时，则返回值为 1，否则返回 0

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

逻辑运算符 

逻辑运算符又称为布尔运算符，用来确认表达式的真和假。

运算符	作用
NOT 或！	逻辑非
AND 或 &&	逻辑与
OR 或  ||	逻辑或
XOR	逻辑异或

 “ NOT”或“！”表示逻辑非

返回和操作数相反的结果：当操作数为 0（假）， 则返回 值为 1，否则值为 0。但是有一点除外，那就是 NOT NULL 的返回值为 NULL，这一点请 大家注意。如下例所示

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

“AND”或“&&”表示逻辑与运算

当所有操作数均为非零值并且不为 NULL 时，计 算所得结果为 1，当一个或多个操作数为 0 时，所得结果为 0，操作数中有任何一个为 NULL 则返回值为 NULL。如下例所示： 

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)

“OR”或“||”表示逻辑或运算

当两个操作数均为非 NULL 值时，如有任意一个操作 数为非零值，则结果为 1，否则结果为 0。当有一个操作数为 NULL 时，如另一个操作 数为非零值，则结果为 1，否则结果为 NULL。假如两个操作数均为 NULL，则所得结果 为 NULL。如下例所示： 

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。对于非 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)

位运算符 

位运算是将给定的操作数转化为二进制后，对各个操作数每一位都进行指定的逻辑运算， 得到的二进制结果转换为十进制数后就是位运算的结果。

运算符	作用
&	位与（位AND）
|	位或（位OR）
^	位异或（位XOR）
~	位取反
>>	位右移
<<	位左移

 

“位与”

对多个操作数的二进制位作逻辑与操作，例如 2&3，因为 2 的二进制数是 10， 3 是 11，所有 10&11 的结果是 10，十进制数字还是 2，来看实际结果： 

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

可以对 2 个以上操作数做或操作，测试一下 2&3&4，因为 4 的二进制是 100，和上面的 10 做与操作 100&010 后，结果应该是 000，可以看实际结果为： 

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

“位或”

对多个操作数的二进制位作逻辑或操作，还是上面的例子，2|3 的结果应该是 10|11，结果还是 11，应该是 3，实际结果如下： 

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

 “位异或”

对操作数的二进制位做异或操作，10^11 的结果是 01，结果应该是 1，可以 看实际结果为： 

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

“位取反”

对操作数的二进制位作 NOT 操作，这里的操作数只能是一位，下面看一个经 典的取反例子：对 1 做位取反，具体如下所示： 

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

结果让大家可能有些疑惑， 1的位取反怎么会是这么大的数字？来研究一下，在MySQL 中， 常量数字默认会以 8 个字节来表示，8 个字节就是 64位，常量 1 的二进制表示为 63 个“0” 加1个“1”，位取反后就是63个 “1”加一个“0”，转换为二进制后就是18446744073709551614， 实际结果如下

mysql> select bin(18446744073709551614);
+------------------------------------------------------------------+
| bin(18446744073709551614)                                        |
+------------------------------------------------------------------+
| 1111111111111111111111111111111111111111111111111111111111111110 |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

“位右移”

对左操作数向右移动右操作数指定的位数。例如 100>>3，就是对 100 的二进 制数 0001100100 右移 3 位，左边补 0，结果是 0000001100，转换为二进制数是 12，实际结果如下： 

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

“位左移”

对左操作数向左移动右操作数指定的位数。例如 100<<3，就是对 100 的二进 制数 0001100100 左移 3 位，右边补 0，结果是 1100100000，转换为二进制数是 800， 实际结果如下： 

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

 运算符的优先级 

优先级顺序	运算符
1	:=
2	||,OR,XOR
3	&&,AND
4	NOT
5	BETWEEN,CASE,THEN,ELSE
6	=,<=>,>=,>,<=,<,<>,IS,LIKE,REGEXP,IN
7	|
8	&
9	<<,>>
10	-,+
11	*,/,DIV,%,MOD
12	^
13	-(一元减号)，~（一元比特反转）
14	！

在实际运行的时候，可以参考表 4-5 中的优先级。实际上，很少有人能将这些优先级熟练记忆，很多情况下我们都是用“（）”来将需要优先的操作括起来，这样既起到了优先的作用， 又使得其他用户看起来更加易于理解。 

小结

 MySQL 中支持的各种运算符。这些运算符可以帮助用户完成算术、比较、 逻辑和位逻辑操作，大家在使用时要注意运算符的优先级。另外，在使用比较运算符时要保 证比较的操作数类型是一致的，这样可以避免由于操作数类型的不一致而得出错误的数据。