[TOC]
MySQL 提供了很多功能强大、使用方便的运算符和函数。通过使用这些运算符完成各种各样的运算操作。函数可以帮助开发人员简单、快速的编写 SQL 语句。
MySQL函数简介
MySQL 函数是 MySQL 数据库提供的内部函数，这些内部函数可以帮助用户更加方便地处理表中的数据。函数就像预定的公式一样存放在数据库里，每个用户都可以调用已经存在的函数来完成某些功能。
提示：函数就是输入值然后得到相应的输出结果，输入值称为参数（parameter），输出值称为返回值。
函数可以很方便的地实现业务逻辑的重用，并且 MySQL 数据库允许用户自己创建函数，以适应实际的业务操作。正确使用函数会让读者在编写 SQL 语句时起到事半功倍的效果。
MySQL 函数用来对数据表中的数据进行相应的处理，以便得到用户希望得到的数据，使 MySQL 数据库的功能更加强大。
MySQL 函数包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数和加密函数等。这些函数不仅能帮助用户做很多事情，比如字符串的处理、数值的运算、日期的运算等，还可以帮助开发人员编写出简单快捷的 SQL 语句。
SELECT、INSERT、UPDATE 和 DELETE 语句及其子句（例如 WHERE、ORDER BY、HAVING 等）中都可以使用 MySQL 函数。例如，数据表中的某个数据是负数，现在需要将这个数据显示为整数，这时就可以在 SELECT 语句中使用绝对值函数。
下面介绍这几类函数的使用范围。
- 数学函数主要用于处理数字。这类函数包括绝对值函数、正弦函数、余弦函数和获得随机数的函数等。
- 字符串函数主要用于处理字符串。其中包括字符串连接函数、字符串比较函数、将字符串的字母都变成小写或大写字母的函数和获取子串的函数等。
- 日期和时间函数主要用于处理日期和时间。其中包括获取当前时间的函数、获取当前日期的函数、返回年份的函数和返回日期的函数等。
- 条件判断函数主要用于在 SQL 语句中控制条件选择。其中包括 IF 语句、CASE 语句和 WHERE 语句等。
- 系统信息函数主要用于获取 MySQL 数据库的系统信息。其中包括获取数据库名的函数、获取当前用户的函数和获取数据库版本的函数等。
- 加密函数主要用于对字符串进行加密解密。其中包括字符串加密函数和字符串解密函数等。
- 其他函数主要包括格式化函数和锁函数等。
以上这些都是 MySQL 数据库中具有代表性的函数，大家并不需要一次全部记住，只需要知道有这样的函数就可以了，实际应用时可以查阅：
- MySQL 常用函数汇总
- MySQL 官方参考文档
MySQL常用运算符概述
在 MySQL 中，可以通过运算符来获取表结构以外的另一种数据。
- 例如，学生表中存在一个 birth 字段，这个字段表示学生的出生年份。如果想得到这个学生的实际年龄，可以使用 MySQL 中的算术运算符用当前的年份减学生出生的年份，求出的结果就是这个学生的实际年龄了。
MySQL 所提供的运算符可以直接对表中数据或字段进行运算，进而实现用户的新需求，增强了 MySQL 的功能。
每种数据库都支持 SQL 语句，但是它们也都有各自支持的运算符。我们除了需要学会使用 SQL 语句外，还需要掌握各种运算符。
MySQL 支持 4 种运算符，分别是：
算术运算符
算术运算符是 SQL 中最基本的运算符，MySQL 支持的运算符包括加、减、乘、除和取余运算，它们是最常用、最简单的一类运算符。
下表列出了这些运算符的作用和使用方法。
比较运算符
当使用 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 或者 !）
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）。
- 例如，5 的二进制形式为：0000 0000 ... 0000 0101
省略号部分都是 0，101 前面总共有 61 个 0。
MySQL 支持 6 种位运算符，包括按位与 & 、按位或 |、按位取反 ~、按位异或 ^、按位左移 << 和按位右移 >> 等位运算符。位运算必须先将数据转换为补码，然后在根据数据的补码进行操作。运算完成后，将得到的值转换为原来的类型（十进制数），返回给用户。
注意：位运算中的&、|、~和逻辑运算中的&&、||、!非常相似。
位或运算符 |
参与|运算的两个二进制位有一个为 1 时，结果就为 1，两个都为 0 时结果才为 0。例如1|1结果为 1，0|0结果为0，1|0结果为1，这和逻辑运算中的||非常类似。
例子：使用位或运算符进行正数运算，SQL 语句如下：
mysql> SELECT 10|15,9|4|2;
+-------+-------+
| 10|15 | 9|4|2 |
+-------+-------+
| 15 | 15 |
+-------+-------+
1 row in set (0.00 sec)
10 的补码为 1010，15 的补码为 1111，按位或运算之后，结果为 1111，即整数 15；9 的补码为 1001，4 的补码为 0100，2 的补码为 0010，按位或运算之后，结果为 111，即整数 15。
例子：使用位或运算符进行负数运算，SQL 语句如下：
mysql> SELECT -7|-1;
+----------------------+
| -7|-1 |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.00 sec)
-7 的补码为 60 个‘1’加 1001，-1 的补码为 64 个‘1’，按位或运算之后，结果为 64 个‘1’，即整数 18446744073709551615。
可以发现，任何数和 -1 进行位或运算时，最终结果都是 -1 的十进制数。
位与运算符 &
参与&运算的两个二进制位都为 1 时，结果就为 1，否则为 0。例如1|1结果为 1，0|0结果为 0，1|0结果为 0，这和逻辑运算中的&&非常类似。
例子：使用位与运算符进行正数运算，SQL 语句如下：
mysql> SELECT 10 & 15,9 & 4 & 2;
+---------+-----------+
| 10 & 15 | 9 & 4 & 2 |
+---------+-----------+
| 10 | 0 |
+---------+-----------+
1 row in set (0.00 sec)
10 的补码为 1010，15 的补码为 1111，按位与运算之后，结果为 1010，即整数 10；9 的补码为 1001，4 的补码为 0100，2 的补码为 0010，按位与运算之后，结果为 0000，即整数 0。
例子：使用位与运算符进行负数运算，SQL 语句如下：
mysql> SELECT -7&-1;
+----------------------+
| -7&-1 |
+----------------------+
| 18446744073709551609 |
+----------------------+
1 row in set (0.01 sec)
-7 的补码为 60 个‘1’加 1001，-1 的补码为 64 个‘1’，按位与运算之后，结果为 60 个‘1’加 1001，即整数 18446744073709551609。
可以发现，任何数和 -1 进行位与运算时，最终结果都为任何数本身的十进制数。
位异或运算符 ^
参与^运算的两个二进制位不同时，结果为 1，相同时，结果为 0。例如1|1结果为 0，0|0结果为 0，1|0结果为1。
例子：使用位异或运算符进行正数运算，SQL 语句如下：
mysql> SELECT 10^15,1^0,1^1;
+-------+-----+-----+
| 10^15 | 1^0 | 1^1 |
+-------+-----+-----+
| 5 | 1 | 0 |
+-------+-----+-----+
1 row in set (0.00 sec)
10 的补码为 1010，15 的补码为 111，按位异或运算之后，结果为 0101，即整数 5；1 的补码为 0001，0 的补码为 0000，按位异或运算之后，结果为 0001；1 和 1 本身二进制位完全相同，因此结果为 0。
例子：使用位异或运算符进行负数运算，SQL 语句如下：
mysql> SELECT -7^-1;
+-------+
| -7^-1 |
+-------+
| 6 |
+-------+
1 row in set (0.00 sec)
-7 的补码为 60 个‘1’加 1001，-1 的补码为 64 个‘1’，按位异或运算之后，结果为 110，即整数 6。
位左移运算符 <<
位左移是按指定值的补码形式进行左移，左移指定位数之后，左边高位的数值被移出并丢弃，右边低位空出的位置用 0 补齐。
位左移的语法格式为：
expr << n
其中，n 指定值 expr 要移位的位数，n 必须为非负数。
例子：使用位左移运算符进行正数计算，SQL 语句如下：
mysql> SELECT 1<<2,4<<2;
+------+------+
| 1<<2 | 4<<2 |
+------+------+
| 4 | 16 |
+------+------+
1 row in set (0.00 sec)
1 的补码为 0000 0001，左移两位之后变成 0000 0100，即整数 4；4 的补码为 0000 0100，左移两位之后变成 0001 0000，即整数 16。
例子：使用位左移运算符进行负数计算，SQL 语句如下：
mysql> SELECT -7<<2;
+----------------------+
| -7<<2 |
+----------------------+
| 18446744073709551588 |
+----------------------+
1 row in set (0.00 sec)
-7 的补码为 60 个‘1’加 1001，左移两位之后变成 56 个‘1’加 1110 0100，即整数 18446744073709551588。
位右移运算符 >>
位右移是按指定值的补码形式进行右移，右移指定位数之后，右边低位的数值被移出并丢弃，左边高位空出的位置用 0 补齐。
位右移语法格式为：
expr >> n
其中，n 指定值 expr 要移位的位数，n 必须为非负数。
例子：使用位右移运算符进行正数运算，SQL 语句如下：
mysql> SELECT 1>>1,16>>2;
+------+-------+
| 1>>1 | 16>>2 |
+------+-------+
| 0 | 4 |
+------+-------+
1 row in set (0.00 sec)
1 的补码为 0000 0001，右移 1 位之后变成 0000 0000，即整数 0；16 的补码为 0001 0000，右移两位之后变成 0000 0100，即整数 4。
例子：使用位右移运算符进行负数运算，SQL 语句如下：
mysql> SELECT -7>>2;
+---------------------+
| -7>>2 |
+---------------------+
| 4611686018427387902 |
+---------------------+
1 row in set (0.00 sec)
-7 的补码为 60 个‘1’加 1001，右移两位之后变成 0011 加 56 个‘1’加 1110，即整数 4611686018427387902。
位取反运算符 ~
位取反是将参与运算的数据按对应的补码进行反转，也就是做 NOT 操作，即 1 取反后变 0，0 取反后变为 1。
例子：下面看一个经典的取反例子，对 1 进行位取反运算，具体如下所示：
mysql> SELECT ~1,~18446744073709551614;
+----------------------+-----------------------+
| ~1 | ~18446744073709551614 |
+----------------------+-----------------------+
| 18446744073709551614 | 1 |
+----------------------+-----------------------+
1 row in set (0.00 sec)
常量 1 的补码为 63 个‘0‘加 1 个’1‘，位取反后就是 63 个’1‘加一个’0‘，转换为二进制后就是 18446744073709551614。
可以使用 BIN() 函数查看 1 取反之后的结果，BIN() 函数的作用是将一个十进制数转换为二进制数，SQL 语句如下:
mysql> SELECT BIN(~1);
+------------------------------------------------------------------+
| BIN(~1) |
+------------------------------------------------------------------+
| 1111111111111111111111111111111111111111111111111111111111111110 |
+------------------------------------------------------------------+
1 row in set (0.01 sec)
1 的补码表示为最右边位为 1，其他位均为 0，取反操作之后，除了最低位，其他位均变为 1。
例子：使用位取反运算符进行运算，SQL 语句如下：
mysql> SELECT 5 & ~1;
+--------+
| 5 & ~1 |
+--------+
| 4 |
+--------+
1 row in set (0.00 sec)
逻辑运算5&~1 中，由于位取反运算符‘~’的级别高于位与运算符‘&’，因此先对 1 进行取反操作，结果为 63 个‘1’加一个‘0’，然后再与整数 5 进行与运算，结果为 0100，即整数 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。
请看下面的 SQL 语句如下：
mysql> SELECT NULL IN (1,3,5,'thks'),10 IN (1,3,NULL,'thks');
+------------------------+-------------------------+
| NULL IN (1,3,5,'thks') | 10 IN (1,3,NULL,'thks') |
+------------------------+-------------------------+
| NULL | NULL |
+------------------------+-------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT NULL IN (1,3,5,'thks'),10 IN (1,10,NULL,'thks');
+------------------------+--------------------------+
| NULL IN (1,3,5,'thks') | 10 IN (1,10,NULL,'thks') |
+------------------------+--------------------------+
| NULL | 1 |
+------------------------+--------------------------+
1 row in set (0.00 sec)
NOT IN 恰好相反，当 NOT IN 运算符的两侧有一个为空值 NULL 时，如果找不到匹配项，则返回值为 NULL；如果找到了匹配项，则返回值为 0。
请看下面的 SQL 语句如下：
mysql> SELECT NULL NOT IN (1,3,5,'thks'),10 NOT IN (1,0,NULL,'thks');
+----------------------------+-----------------------------+
| NULL NOT IN (1,3,5,'thks') | 10 NOT IN (1,0,NULL,'thks') |
+----------------------------+-----------------------------+
| NULL | NULL |
+----------------------------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT NULL NOT IN (1,3,5,'thks'),10 NOT IN (1,10,NULL,'thks');
+----------------------------+------------------------------+
| NULL NOT IN (1,3,5,'thks') | 10 NOT IN (1,10,NULL,'thks') |
+----------------------------+------------------------------+
| NULL | 0 |
+----------------------------+------------------------------+
1 row in set (0.00 sec)
