MySQL运算符

最新推荐文章于 2023-12-18 22:15:00 发布

原创最新推荐文章于 2023-12-18 22:15:00 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

MySQL 专栏收录该内容

20 篇文章

订阅专栏

这篇博客详细介绍了MySQL中的运算符，包括算术运算符、比较运算符、逻辑运算符和位运算符。比较运算符中详细讲解了等于、不等于、小于、大于、小于等于和大于等于，以及IS NULL和IS NOT NULL的用法。逻辑运算符包括NOT、AND、OR和XOR，位运算符则包括|、&、^、<<、>>。文章还提到了运算符的优先级和IN与NOT IN的用法，特别强调了对空值NULL的处理。

MySQL常用运算符概述

在 MySQL 中，可以通过运算符来获取表结构以外的另一种数据。

例如，学生表中存在一个 birth 字段，这个字段表示学生的出生年份。如果想得到这个学生的实际年龄，可以使用 MySQL 中的算术运算符用当前的年份减学生出生的年份，求出的结果就是这个学生的实际年龄了。

MySQL 所提供的运算符可以直接对表中数据或字段进行运算，进而实现用户的新需求，增强了 MySQL 的功能。

每种数据库都支持 SQL 语句，但是它们也都有各自支持的运算符。我们除了需要学会使用 SQL 语句外，还需要掌握各种运算符。

MySQL 支持 4 种运算符，分别是：

算术运算符

算术运算符是 SQL 中最基本的运算符，MySQL 支持的运算符包括加、减、乘、除和取余运算，它们是最常用、最简单的一类运算符。

下表列出了这些运算符的作用和使用方法：
(1) + ：加法运算
(2) - ：减法运算
(3) * ：乘法运算
(4) / ：除法运算，返回商
(5) % ：求余运算，返回余数

比较运算符

当使用 SELECT 语句进行查询时，MYSQL 允许用户对表达式的左边操作数和右边操作数进行比较，比较结果为真，则返回 1，为假则返回 0，比较结果不确定则返回 NULL。MySQL 支持的比较运算符如下表所示
在这里插入图片描述
比较运算符可以用于比较数字、字符串和表达式的值。注意，字符串的比较是不区分大小写的。

等于运算（=）

= 运算符用来比较两边的操作数是否相等，相等的话返回 1，不相等的话返回 0。具体的语法规则如下：

若有一个或两个操作数为 NULL，则比较运算的结果为 NULL。
若两个操作数都是字符串，则按照字符串进行比较。
若两个操作数均为整数，则按照整数进行比较。
若一个操作数为字符串，另一个操作数为数字，则 MySQL 可以自动将字符串转换为数字。

注意：NULL 不能用于 = 比较。

安全等于运算符（<=>）

<=> 操作符和 = 操作符类似，不过 <=> 可以用来判断 NULL 值，具体语法规则为：

当两个操作数均为 NULL 时，其返回值为 1 而不为 NULL；
而当一个操作数为 NULL 时，其返回值为 0 而不为 NULL。

不等于运算符（<> 或者 !=）

与 = 的作用相反，<> 和 != 用于判断数字、字符串、表达式是否不相等。对于 <> 和 !=，如果两侧操作数不相等，返回值为 1，否则返回值为 0；如果两侧操作数有一个是 NULL，那么返回值也是 NULL。

两个不等于运算符作用相同，都可以进行数字、字符串、表达式的比较判断。

小于等于运算符（<=）

<= 是小于等于运算符，用来判断左边的操作数是否小于或者等于右边的操作数；如果小于或者等于，返回值为 1，否则返回值为 0；如果两侧操作数有一个是 NULL，那么返回值也是 NULL。

左边操作数小于或者等于右边时，返回值为 1，例如 4<=4；
当左边操作数大于右边时，返回值为 0，例如“good”第 3 个位置的“o”字符在字母表中的顺序大于“god”中的第 3 个位置的“d”字符，因此返回值为0；
同样，比较 NULL 值时返回 NULL。

小于运算符（<）

< 是小于运算符，用来判断左边的操作数是否小于右边的操作数；如果小于，返回值为 1，否则返回值为 0；如果两侧操作数有一个是 NULL，那么返回值也是 NULL。

当左边操作数小于右边时，返回值为 1，例如 1<2；
当左边操作数大于右边时，返回值为 0，例如“good”第 3 个位置的“o”字符在字母表中的顺序大于“god”中的第 3 个位置的“d”字符，因此返回值为 0；
同样，比较 NULL 值时返回 NULL。

大于等于运算符（>=）

>= 是大于等于运算符，用来判断左边的操作数是否大于或者等于右边的操作数；如果大于或者等于，返回值为 1，否则返回值为 0；如果两侧操作数有一个是 NULL，那么返回值也是 NULL。

左边操作数大于或者等于右边时，返回值为 1，例如 4>=4；
当左边操作数小于右边时，返回值为 0，例如 1>=2；
同样，比较 NULL 值时返回 NULL。

大于运算符（>）

> 是大于运算符，用来判断左边的操作数是否大于右边的操作数；如果大于，返回值为 1，否则返回值为 0；如果两侧操作数有一个是 NULL，那么返回值也是 NULL。

由结果可以看到：

左边操作数大于右边时，返回值为 1，例如 5.5>5；
当左边操作数小于右边时，返回 0，例如 1>2；
同样，比较 NULL 值时返回 NULL。

IS NULL(ISNULL) 和 IS NOT NULL 运算符

IS NULL 或 ISNULL 运算符用来检测一个值是否为 NULL，如果为 NULL，返回值为 1，否则返回值为 0。ISNULL 可以认为是 IS NULL 的简写，去掉了一个空格而已，两者的作用和用法都是完全相同的。

IS NOT NULL 运算符用来检测一个值是否为非 NULL，如果是非 NULL，返回值为 1，否则返回值为 0。

IS NULL 和 ISNULL 的作用相同，只是写法略有不同。ISNULL 和 IS NOT NULL 的返回值正好相反。

BETWEEN AND 运算符

BETWEEN AND 运算符用来判断表达式的值是否位于两个数之间，或者说是否位于某个范围内，它的语法格式如下：

expr BETWEEN min AND max

expr 表示要判断的表达式，min 表示最小值，max 表示最大值。如果 expr 大于等于 min 并且小于等于 max，那么返回值为 1，否则返回值为 0。

总结

使用比较运算符时需要注意空值 NULL，大部分比较运算符遇到 NULL 时也会返回 NULL。

逻辑运算符

逻辑运算符又称为布尔运算符，用来确定表达式的真和假。包括与、或、非和异或、等逻辑运算符。其返回值为布尔型，真值（1 或 true）和假值（0 或 false）。MySQL中支持的逻辑运算符如下表所示
NOT 或 ! 逻辑非
AND 逻辑与
OR 逻辑或
XOR 逻辑异或

逻辑非运算（NOT 或者 !）

NOT和!都是逻辑非运算符，返回和操作数相反的结果，具体语法规则为：

当操作数为 0（假）时，返回值为 1；
当操作数为非零值时，返回值为 0；
当操作数为 NULL 时，返回值为 NULL。

NOT 1+1和! 1+1的返回值不同，这是因为 NOT 与 ! 的优先级不同：

NOT 的优先级低于 +，因此NOT 1+1相当于NOT(1+1)，先计算1+1，然后再进行 NOT 运算，由于操作数不为 0，因此NOT 1+1的结果是 0；
相反，! 的优先级别要高于 +，因此! 1+1相当于(!1)+1，先计算!1结果为 0，再加 1，最后结果为 1。

注意：在使用运算符运算时，一定要注意运算符的优先级，如果不能确定计算顺序，最好使用括号，以保证运算结果的正确。

逻辑与运算符（AND 或者 &&）

AND 和 && 都是逻辑与运算符，具体语法规则为：

当所有操作数都为非零值并且不为 NULL 时，返回值为 1；
当一个或多个操作数为 0 时，返回值为 0；
操作数中有任何一个为 NULL 时，返回值为 NULL。

AND 和 && 的作用相同。1 AND-1中没有 0 或者 NULL，所以返回值为 1；1 AND 0中有操作数 0，所以返回值为 0；1 AND NULL虽然有 NULL，所以返回值为 NULL。

注意：AND 运算符可以有多个操作数，但要注意多个操作数运算时，AND 两边一定要使用空格隔开，不然会影响结果的正确性。

逻辑或运算符（OR 或者 ||）

OR 和 || 都是逻辑或运算符，具体语法规则为：

当两个操作数都为非 NULL 值时，如果有任意一个操作数为非零值，则返回值为 1，否则结果为 0；
当有一个操作数为 NULL 时，如果另一个操作数为非零值，则返回值为 1，否则结果为NULL；
假如两个操作数均为 NULL 时，则返回值为 NULL。

OR 和 || 的作用相同。下面是对各个结果的解析：

1 OR -1 OR 0含有 0，但同时包含有非 0 的值 1 和 -1，所以返回结果为 1；
1 OR 2中没有操作数 0，所以返回结果为 1；
1 OR NULL虽然有 NULL，但是有操作数 1，所以返回结果为 1；
0 OR NULL中没有非 0 值，并且有 NULL，所以返回值为 NULL；
NULL OR NULL中只有NULL，所以返回值为NULL。

异或运算（XOR 运算符）

XOR 表示逻辑异或，具体语法规则为：

当任意一个操作数为 NULL 时，返回值为 NULL；
对于非 NULL 的操作数，如果两个操作数都是非 0 值或者都是 0 值，则返回值为 0；
如果一个为0值，另一个为非 0 值，返回值为 1。

1 XOR 1和0 XOR 0中运算符两边的操作数都为非零值，或者都是零值，因此返回 0；
1 XOR 0中两边的操作数，一个为 0 值，另一个为非 0 值，所以返回值为 1；
1 XOR NULL中有一个操作数为 NULL，所以返回值为 NULL；
1 XOR 1 XOR 1中有多个操作数，运算符相同，因此运算顺序从左到右依次计算，1 XOR 1的结果为 0，再与 1 进行异或运算，所以返回值为 1。
提示：a XOR b 的计算等同于 (a AND (NOT b))或者 ((NOT a) AND b)。

位运算符

所谓位运算，就是按照内存中的比特位（Bit）进行操作，这是计算机能够支持的最小单位的运算。程序中所有的数据在内存中都是以二进制形式存储的，位运算就是对这些二进制数据进行操作。

位运算一般用于操作整数，对整数进行位运算才有实际的意义。整数在内存中是以补码形式存储的，正数的补码形式和原码形式相同，而负数的补码形式和它的原码形式是不一样的，这一点大家要特别注意；这意味着，对负数进行位运算时，操作的是它的补码，而不是它的原码。

MySQL 中的整数字面量（常量整数，也就是直接书写出来的整数）默认以 8 个字节（Byte）来表示，也就是 64 位（Bit）
| 位或
& 位与
^ 位异或
<< 位左移

位右移
~ 位取反
注意：位运算中的&、|、~和逻辑运算中的&&、||、!非常相似。

1、位或运算符 ( | ) ：对应的二进制位有一个或两个为 1 ，则该位的运算结果为 1 ，否则为 0

mysql> SELECT 10 | 15 , 9 | 4 | 2 ;
±--------±----------+
| 10 | 15 | 9 | 4 | 2 | # 10的二进制为1010,15的二进制为1111，按位或运算之后结果为1111，即15
±--------±----------+ # 9的二进制为1001,4的热禁止为0100,2的二进制为0010，按位或运算之后结果为1111，即15
| 15 | 15 |
±--------±----------+

2、位与运算符 ( & ) ：对应的二进制位都为 1 ，则该位的运算结果为 1 ，否则为 0

mysql> SELECT 10 & 15 , 9 & 4 & 2 ;
±--------±----------+
| 10 & 15 | 9 & 4 & 2 |
±--------±----------+
| 10 | 0 |
±--------±----------+

3、位异或运算符 ( ^ ) ：对应的二进制位不相同时，结果为 1 ，否则为 0

mysql> SELECT 10 ^ 15 , 1 ^ 0 , 1 ^ 1 ;
±--------±------±------+
| 10 ^ 15 | 1 ^ 0 | 1 ^ 1 |
±--------±------±------+
| 5 | 1 | 0 |
±--------±------±------+

4、位左移运算符 ( << ) ：使指定的二进制位都左移指定的位数，左移指定位之后，左边高位的数值将被移出并丢弃，右边低位空出的位置用 0 补齐

mysql> SELECT 1<<2 , 4<<2 ;
±-----±-----+
| 1<<2 | 4<<2 | # 1的二进制值为00000001，左移两位之后变成00000100，即十进制数4
±-----±-----+ # 4的二进制值为00000100，左移两位之后变成00010000，即十进制数16
| 4 | 16 |
±-----±-----+

5、位右移运算符 ( >> ) ：使指定的二进制位都右移指定的位数，右移指定位之后，右边低位的数值将被移出并丢弃，左边高位空出的职位用 0 补齐

mysql> SELECT 1>>1 , 16>>2 ;
±-----±------+
| 1>>1 | 16>>2 |
±-----±------+
| 0 | 4 |
±-----±------+

6、位取反运算符 ( ~ ) ：将对应的二进制数逐位反转，即 1 取反后变 0 ， 0 取反后变 1

mysql> SELECT 5 & ~1 ;
±-------+
| 5 & ~1 |
±-------+
| 4 |
±-------+

MySQL运算符的优先级

运算符的优先级决定了不同的运算符在表达式中计算的先后顺序，下表列出了 MySQL 中的各类运算符及其优先级。

=(赋值运算）、:=
II、OR
XOR
&&、AND
NOT
BETWEEN、CASE、WHEN、THEN、ELSE
=(比较运算）、<=>、>=、>、<=、<、<>、!=、 IS、LIKE、REGEXP、IN
|
&
<<、>>
-(减号）、+
*、/、%
^
-(负号）、〜（位反转）
！

可以看出，不同运算符的优先级是不同的。一般情况下，级别高的运算符优先进行计算，如果级别相同，MySQL 按表达式的顺序从左到右依次计算

MySQL IN和NOT IN用法详解

MySQL 中的 IN 运算符用来判断表达式的值是否位于给出的列表中；

如果是，返回值为 1，
否则返回值为 0。

NOT IN 的作用和 IN 恰好相反，NOT IN 用来判断表达式的值是否不存在于给出的列表中；

如果不是，返回值为 1，
否则返回值为 0。

IN 和 NOT IN 的语法格式如下：

expr IN ( value1, value2, value3 ... valueN )
expr NOT IN ( value1, value2, value3 ... valueN )

expr 表示要判断的表达式，
value1, value2, value3 … valueN 表示列表中的值。

MySQL 会将 expr 的值和列表中的值逐一对比。

对空值 NULL 的处理

当 IN 运算符的两侧有一个为空值 NULL 时，如果找不到匹配项，则返回值为 NULL；如果找到了匹配项，则返回值为 1

NOT IN 恰好相反，当 NOT IN 运算符的两侧有一个为空值 NULL 时，如果找不到匹配项，则返回值为 NULL；如果找到了匹配项，则返回值为 0