MySQL 运算符

MySQL支持多种类型的运算符，这些运算符可以用来链接表达式的项。运算符主要类型包括：算数运算符、比较运算符、逻辑运算符、位运算符。下面的笔记介绍MySQL5.7支持的几种运算符。

算数运算符

MySQL支持算数运算符的加、减、乘、除和模运算。是最常使用最简单的位运算符。

运算符	作用
`+`	加法
`-`	减法
`*`	乘法
`/`，`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：

mysql> select 1/0, 100%0;
+------+-------+
| 1/0  | 100%0 |
+------+-------+
| NULL |  NULL |
+------+-------+
1 row in set (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。下面列出了MySQL5.7支持的比较运算符：

运算符	作用
`=`	等于
`<>`、`!=`	不等于
`<=>`	NULL安全的等于
`<`	小于
`<=`	小于等于
`>`	大于
`>=`	大于等于
`BETWEEN`	存在于指定范围
`IN`	存在于指定集合
`IS NULL`	为NULL
`IS NOT NULL`	不为NULL
`LIKE`	通配符匹配
`PEGEXP` 或 `PLIKE`	正则表达式匹配

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

“=” 运算符

用于比较运算符两侧的操作数是否相等，如果两侧操作数相等则返回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<=>0, 1<=>1, NULL<=>NULL;
+-------+-------+-------------+
| 1<=>0 | 1<=>1 | NULL<=>NULL |
+-------+-------+-------------+
|     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” 运算符 **

使用格式：a BETWEEN min AND max

当a大于等于min 并小于等于 max，则返回值为1，否则返回值为0；
当a、min、max类型相同时，此表达式等价于 a >= min and a <= max ，当操作数类型不同时，比较时会遵循类型转换原则进行转换后，再进行比较运算。

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.00 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 ，当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 (0.00 sec)

“PEGEXP” 运算符

使用格式为：str PEGEXP 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 (0.00 sec)

个人理解就是正则表达式，但与 PHP所使用的正则表达式还有区别，仅支持以下语法：

模式	描述
^	匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 ‘n’ 或 ‘r’ 之后的位置。
$	匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 ‘n’ 或 ‘r’ 之前的位置。
.	匹配除 “n” 之外的任何单个字符。要匹配包括 ‘n’ 在内的任何字符，请使用像 ‘[.n]’ 的模式。
[…]	字符集合。匹配所包含的任意一个字符。例如， ‘[abc]’ 可以匹配 “plain” 中的 ‘a’。
[^…]	负值字符集合。匹配未包含的任意字符。例如， ‘[^abc]’ 可以匹配 “plain” 中的’p’。
p1\|p2\|p3	匹配 p1 或 p2 或 p3。例如，’z\|food’ 能匹配 “z” 或 “food”。'(z\|f)ood’ 则匹配 “zood” 或 “food”。
*	匹配前面的子表达式零次或多次。例如，zo* 能匹配 “z” 以及 “zoo”。* 等价于{0,}。
+	匹配前面的子表达式一次或多次。例如，’zo+’ 能匹配 “zo” 以及 “zoo”，但不能匹配 “z”。+ 等价于 {1,}。
{n}	n 是一个非负整数。匹配确定的 n 次。例如，’o{2}’ 不能匹配 “Bob” 中的 ‘o’，但是能匹配 “food” 中的两个 o。
{n,m}	m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。

MySQL使用正则和其他比较运算符效率一致，不会使查询变慢，使用正则的目的是可以简化我们的SQL查询条件。

逻辑运算符

逻辑运算符又称布尔运算符，用来确认表达式的真和假。MySQL支持四种逻辑运算符：

运算符	作用
`NOT` 、`!`	逻辑非
`AND`、`&&`	逻辑与
`OR`、`\|\|`	逻辑或
`XOR`	逻辑异或

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

返回和操作数相反的结果：当操作数为0（假）时返回值为1，否则值为0。但有一点除外，NOT NULL的返回值依然是NULL

mysql> select not 1, not 0,not null;
+-------+-------+----------+
| not 1 | not 0 | not null |
+-------+-------+----------+
|     0 |     1 |     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值时，如有任意一个操作数为非0值，则结果为1，否则结果为0；
当有一个操作数为NULL时，如另一个操作数为非0值，则结果为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)

位运算符

位运算是将给定的操作数转换为二进制后，对各个操作数的每一位都进行指定的逻辑运算，得到的二进制结果在转换为十进制之后就是位运算的结果。MySQL5.7 支持六种位运算符：

运算符	描述
`&`	位与（位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&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)

因为在MySQL中，常量数字默认会以8个字节来表示，8个字节就是64位，常量1的二进制表示为63个0和一个1，取反后就是63个1和一个0，转换为十进制之后就是18446744073709551614，实际结果如下：

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

位右移

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

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

位左移

与位右移相似，只是反方向的移动：

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

运算符的优先级

前面介绍的MySQL各种运算符使用方法，在实际应用中，很可能将这些运算符进行混合运算，那么就涉及到了优先级的问题了，下面列出所有的运算符，优先级由低到高排列，同一行中的运算符拥有相同的优先级。

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