MySQL 中的运算符
MySQL 支持多种类型的运算符,来连接表达式的项。这些类型主要包括算术运算符、比较运算符、逻辑运算符和位运算符。
算术运算符
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.00 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。
运算符 | 作用 |
= | 等于 |
<>或!= | 不等于 |
<=> | NULL 安全的等于(NULL-safe) |
< | 小于 |
<= | 小于等于 |
> | 大于 |
>= | 大于等于 |
BETWEEN | 存在与指定范围 |
IN | 存在于指定集合 |
IS NULL | 为NULL |
IS NOT NULL | 不为NULL |
LIKE | 通配符匹配 |
REGEXP 或RLIKE | 正则表达式匹配 |
比较运算符可以用于比较数字、字符串和表达式。数字作为浮点数比较,而字符串以不区分大小写的方式进行比较。下面通过实例来学习各种比较运算符的使用。
-
“=”运算符,用于比较运算符两侧的操作数是否相等,如果两侧操作数相等返回值为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),当操作数类型不同时,比较时会遵循类型转换原则进行转换后,再进行比较运算。下例中描述了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'),0 in (1,2); +--------------+----------------------+------------+ | 1 in (1,2,3) | 't' in ('t','a','b') | 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”。和“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 (0.00 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 (0.01 sec)
逻辑运算符
逻辑运算符又称为布尔运算符,用来确认表达式的真和假。MySQL 支持4 种逻辑运算符。
运算符 | 作用 |
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),(1 and null); +-----------+-----------+--------------+ | (1 and 1) | (0 and 1) | (1 and null) | +-----------+-----------+--------------+ | 1 | 0 | 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),(0 or null),(1 or 1),(null or null); +----------+----------+-------------+-------------+----------+----------------+ | (1 or 0) | (0 or 0) | (1 or null) | (0 or null) | (1 or 1) | (null or null) | +----------+----------+-------------+-------------+----------+----------------+ | 1 | 0 | 1 | NULL | 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)
位运算符
位运算是将给定的操作数转化为二进制后,对各个操作数每一位都进行指定的逻辑运算,得到的二进制结果转换为十进制数后就是位运算的结果。MySQL 5.0 支持6 种位运算符
运算符 | 作用 |
& | 位与(位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)
运算符的优先级
前面介绍了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 | ^ |
3 | -(一元减号),~(一元比特反转) |
14 | ! |
在实际运行的时候,可以参考表中的优先级。实际上,很少有人能将这些优先级熟练记忆,很多情况下我们都是用“()”来将需要优先的操作括起来,这样既起到了优先的作用,又使得其他用户看起来更加易于理解。
小结
本章主要介绍了MySQL 中支持的各种运算符。这些运算符可以帮助用户完成算术、比较、逻辑和位逻辑操作,大家在使用时要注意运算符的优先级。另外,在使用比较运算符时要保证比较的操作数类型是一致的,这样可以避免由于操作数类型的不一致而得出错误的数据。