MySQL比较运算符详解

比较运算符用于比较运算，包括大于（>）、小于（<）、等于（=）、大于或等于（>=）、小于或等于（<=）、不等于（!=），以及IN、BETWEEN AND、IS NULL、GREATEST、LEAST、LIKE、REGEXP等。

一个比较运算符的结果总是 1、0 或者是 NULL。比较运算符经常在 SELECT 的查询条件子句中使用，用来查询满足指定条件的记录。

下表罗列了 MySQL 中的比较运算符。

表 1 MySQL中的比较运算符

运算符	作用
=	等于
<=>	安全等于
<>、!=	不等于
<=	小于或等于
>=	大于或等于
>	大于
IS NULL	判断一个值是否为 NULL
IS NOT NULL	判断一个值是否不为 NULL
LEAST	在有两个或多个参数时，返回最小值
GREATEST	当有两个或多个参数时，返回最大值
BETWEEN AND	判断一个值是否落在两个值之间
ISNULL	与 IS NULL 作用相同
IN	判断一个值是 IN 列表中的任意一个值
NOT IN	判断一个值不是 IN 列表中的任意一个值
LIKE	通配符匹配
REGEXP	正则表达式匹配

下面分别讨论不同比较运算符的使用方法。

1、等于运算符（=）

等号（=）用来判断数字、字符串和表达式是否相等：如果相等，返回值为 1，否则返回值为 0。

【实例】使用“=”进行相等判断，SQL 语句如下：

mysql> SELECT 1=0, '2'=2, 2=2,'0.02'=0, 'b'='b', (1+3) = (2+2),NULL=NULL;
+-----+-----+-----+----------+---------+-----------------+-------------+
| 1=0 |'2'=2| 2=2 | '0.02'=0 | 'b'='b' | (1+3) = (2+2)   | NULL=NULL   |
+-----+-----+-----+----------+---------+-----------------+-------------+
|   0 |  1  |  1  |     0    |    1    |      1          |    NULL     |
+-----+-----+-----+----------+---------+-----------------+-------------+

由结果可以看到，在进行判断时，2=2 和 '2'=2 的返回值相同，都为 1。因为在进行判断时，MySQL 自动进行了转换，把字符 '2' 转换成了数字 2；'b'='b' 为相同的字符比较，所以返回值为 1；表达式 1+3 和表达式 2+2 的结果都为 4，因此结果相等，返回值为 1；由于“=”不能用于空值 NULL 的判断，所以返回值为 NULL。

数值比较时有如下规则：

• 若有一个或两个参数为 NULL，则比较运算的结果为 NULL。
• 若同一个比较运算中的两个参数都是字符串，则按照字符串进行比较。
• 若两个参数均为整数，则按照整数进行比较。
• 若用字符串和数字进行相等判断，则 MySQL 可以自动将字符串转换为数字。

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

这个操作符和 = 操作符执行相同的比较操作，不过 <=> 可以用来判断 NULL 值。在两个操作数均为 NULL 时，其返回值为 1，而不为 NULL；当一个操作数为 NULL 时，其返回值为 0，而不为 NULL。

【实例】使用“<=>”进行相等的判断，SQL 语句如下：

mysql> SELECT 1<=>0, '2'<=>2, 2<=>2,'0.02'<=>0, 'b'<=>'b', (1+3)<=> (2+1),NULL<=>NULL;
+-----+-------+-----+----------+---------+---------------+------------+
|1<=>0|'2'<=>2|2<=>2|'0.02'<=>0|'b'<=>'b'|(1+3) <=> (2+1)| NULL<=>NULL|
+-----+-------+-----+----------+---------+---------------+------------+
|   0 |     1|    1 |        0 |       1 |             0 |          1 |
+-----+-------+-----+----------+---------+---------------+------------+

由结果可以看到，“<=>”在执行比较操作时和“=”的作用相似，唯一的区别是“<=>”可以用来对 NULL 进行判断，两者都为 NULL 时返回值为 1。

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

“<>”或者“!=”用于判断数字、字符串、表达式是否相等：如果不相等，返回值为 1，否则返回值为 0。这两个运算符不能用于判断空值 NULL。

【实例】使用“>”和“!=”进行不相等的判断，SQL 语句如下：

mysql> SELECT 'good'<>'god', 1<>2, 4!=4, 5.5!=5, (1+3)!=(2+1),NULL<>NULL;
+---------------+------+------+--------+--------------+------------+
| 'good'<>'god' | 1<>2 | 4!=4 | 5.5!=5 | (1+3)!=(2+1) | NULL<>NULL |
+---------------+------+------+--------+--------------+------------+
|          1    |    1 |  0   |      1 |         1    |     NULL   |
+---------------+------+------+--------+--------------+------------+

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

4、小于或等于运算符（<=）

“<=”用来判断左边的操作数是否小于或等于右边的操作数：如果小于或等于，返回值为 1，否则返回值为 0。“<=”不能用于判断空值 NULL。

【实例】使用“<=”进行比较判断，SQL 语句如下：

mysql>SELECT 'good'<='god', 1<=2, 4<=4, 5.5<=5, (1+3)<=(2+1),NULL<=NULL;
+---------------+------+------+--------+----------------+------------+
| 'good'<='god' | 1<=2 | 4<=4 | 5.5<=5 | (1+3) <= (2+1) | NULL<=NULL |
+---------------+------+------+--------+----------------+------------+
|          0    |    1 |  1   |      0 |          0     |      NULL  |
+---------------+------+------+--------+----------------+------------+

由结果可以看到，左边操作数小于或等于右边时，返回值为 1，例如 4<=4；当左边操作数大于右边时，返回值为 0，例如 'good'<='god'（'good' 第 3 个位置的“o”字符在字母表中的顺序大于'god' 中第 3 个位置的“d”字符，因此返回值为 0）；比较两个 NULL 值时将返回 NULL。

5、小于运算符（<）

“<”运算符用来判断左边的操作数是否小于右边的操作数：如果是，则返回值为 1，否则返回值为 0。“<”不能用于判断空值 NULL。

【实例】使用“<”进行比较判断，SQL 语句如下：

mysql> SELECT 'good'<'god', 1<2, 4<4, 5.5<5, (1+3) < (2+1),NULL<NULL;
+--------------+-----+-----+-------+---------------+-----------+
| 'good'<'god' | 1<2 | 4<4 | 5.5<5 | (1+3) < (2+1) | NULL<NULL |
+--------------+-----+-----+-------+---------------+-----------+
|         0    |   1 |  0  |    0  |          0    |    NULL   |
+--------------+-----+-----+-------+---------------+-----------+

由结果可以看到，当左边操作数小于右边时，返回值为 1，例如 1<2；当左边操作数大于右边时，返回值为0，例如 'good'<'god'（'good' 第 3 个位置的“o”字符在字母表中的顺序大于 'god' 中第 3 个位置的“d”字符，因此返回值为 0）；比较两个 NULL 值时将返回 NULL。

6、大于或等于运算符（>=）

“>=”运算符用来判断左边的操作数是否大于或等于右边的操作数：如果是，则返回值为 1；否则返回值为 0。“>=”不能用于判断空值 NULL。

【实例】使用“>=”进行比较判断，SQL 语句如下：

MySQL> SELECT 'good'>='god', 1>=2, 4>=4, 5.5>=5, (1+3) >= (2+1),NULL>=NULL;
+---------------+------+------+--------+----------------+------------+
| 'good'>='god' | 1>=2 | 4>=4 | 5.5>=5 | (1+3) >= (2+1) | NULL>=NULL |
+---------------+------+------+--------+----------------+------------+
|          1    |   0  |   1  |     1  |           1    |     NULL   |
+---------------+------+------+--------+----------------+------------+

由结果可以看到，左边操作数大于或等于右边时，返回值为 1，例如 4>=4；当左边操作数小于右边时，返回值为 0，例如 1>=2；比较两个 NULL 值时将返回 NULL。

7、大于运算符（>）

“>”运算符用来判断左边的操作数是否大于右边的操作数：如果是，则返回值为 1；否则返回值为 0。“>”不能用于判断空值 NULL。

【实例】使用“>”进行比较判断，SQL 语句如下：

mysql> SELECT 'good'>'god', 1>2, 4>4, 5.5>5, (1+3) > (2+1),NULL>NULL;
+--------------+-----+-----+-------+---------------+-----------+
| 'good'>'god' | 1>2 | 4>4 | 5.5>5 | (1+3) > (2+1) | NULL>NULL |
+--------------+-----+-----+-------+---------------+-----------+
|       1      |   0 |  0  |    1  |          1    |     NULL  |
+--------------+-----+-----+-------+---------------+-----------+

由结果可以看到，左边操作数大于右边时，返回值为 1，例如 5.5>5；左边操作数小于右边时，返回 0，例如 1>2；比较两个 NULL 值时将返回 NULL。

8、IS NULL（ISNULL）和IS NOT NULL运算符

IS NULL 和 ISNULL 检验一个值是否为 NULL：如果为 NULL，返回值为 1，否则返回值为 0。IS NOT NULL 检验一个值是否为非 NULL：如果是非 NULL，返回值为 1，否则返回值为 0。

【实例】使用 IS NULL、ISNULL 和 IS NOT NULL 判断 NULL 值和非 NULL 值，SQL 语句如下：

mysql> SELECT NULL IS NULL, ISNULL(NULL),ISNULL(10), 10 IS NOT NULL;
+--------------+--------------+------------+----------------+
| NULL IS NULL | ISNULL(NULL) | ISNULL(10) | 10 IS NOT NULL |
+--------------+--------------+------------+----------------+
|          1   |         1    |       0    |           1    |
+--------------+--------------+------------+----------------+

由结果可以看到，IS NULL 和 ISNULL 的作用相同，只是格式不同。ISNULL 和 IS NOT NULL 的返回值正好相反。

9、BETWEEN…AND…运算符

语法格式为：

expr BETWEEN min AND max

假如 expr 大于等于 min 且小于等于 max，则 BETWEEN 的返回值为 1，否则返回值为 0。

【实例】使用 BETWEEN…AND… 进行值区间判断，输入 SQL 语句如下：

mysql> SELECT 4 BETWEEN 2 AND 5, 4 BETWEEN 4 AND 6,12 BETWEEN 9 AND 10;
+-------------------+-------------------+---------------------+
| 4 BETWEEN 2 AND 5 | 4 BETWEEN 4 AND 6 | 12 BETWEEN 9 AND 10 |
+-------------------+-------------------+---------------------+
|              1    |             1     |               0     |
+-------------------+-------------------+---------------------+
mysql> SELECT  'x' BETWEEN 'f' AND 'g', 'b' BETWEEN 'a' AND 'c';
+-------------------------+-------------------------+
| 'x' BETWEEN 'f' AND 'g' | 'b' BETWEEN 'a' AND 'c' |
+-------------------------+-------------------------+
|                   0     |               1         |
+-------------------------+-------------------------+

由结果可以看到，4 在端点值区间内或者等于其中一个端点值时，BETWEEN…AND… 表达式返回值为 1；12 并不在指定区间内，因此返回值为 0；对于字符串类型的比较，按字母表中字母顺序进行比较，“x”不在指定的字母区间内，因此返回值为 0，而“b”位于指定字母区间内，因此返回值为 1。

10、LEAST运算符

语法格式为：

LEAST(值1,值2,...,值n)

其中，“值n”表示参数列表中有 n 个值。在有两个或多个参数的情况下，返回最小值。假如任意一个自变量为 NULL，则 LEAST() 的返回值为 NULL。

【实例】使用 LEAST 运算符进行大小判断，SQL 语句如下：

mysql> SELECT least(2,0), least(20.0,3.0,100.5), least('a','c','b'),least(10,NULL);
+----------+---------------------+------------------+--------------+
|least(2,0)|least(20.0,3.0,100.5)|least('a','c','b')|least(10,NULL)|
+----------+---------------------+------------------+--------------+
|        0 |               3.0   | a                |         NULL |
+----------+---------------------+------------------+--------------+

由结果可以看到，当参数是整数或者浮点数时，LEAST 将返回其中最小的值；当参数为字符串时，返回字母表中顺序最靠前的字符；当比较值列表中有 NULL 时，不能判断大小，返回值为 NULL。

11、GREATEST (value1,value2,...)

语法格式为：

GREATEST(值1, 值2,...,值n)

其中，n 表示参数列表中有 n 个值。当有两个或多个参数时，返回值为最大值。假如任意一个自变量为 NULL，则 GREATEST() 的返回值为 NULL。

【实例】使用 GREATEST 运算符进行大小判断，SQL 语句如下：

mysql> SELECT greatest(2,0), greatest(20.0,3.0,100.5), greatest('a','c','b'), greatest(10,NULL);
+-------------+------------------------+---------------------+------------------+
|greatest(2,0)|greatest(20.0,3.0,100.5)|greatest('a','c','b')|greatest(10,NULL) |
+-------------+------------------------+---------------------+------------------+
|          2  |             100.5      | c                   |         NULL     |
+-------------+------------------------+---------------------+------------------+

由结果可以看到，当参数中是整数或者浮点数时，GREATEST 将返回其中最大的值；当参数为字符串时，返回字母表中顺序最靠后的字符；当比较值列表中有 NULL 时，不能判断大小，返回值为 NULL。

12、IN、NOT IN运算符

IN 运算符用来判断操作数是否为 IN 列表中的其中一个值：如果是，返回值为 1，否则返回值为 0。

NOT IN 运算符用来判断表达式是否为 IN 列表中的其中一个值：如果不是，返回值为 1，否则返回值为 0。

【实例】使用 IN、NOT IN 运算符进行判断，SQL 语句如下：

mysql> SELECT 2 IN (1,3,5,'thks'), 'thks' IN (1,3,5,'thks');
+---------------------+--------------------------+
| 2 IN (1,3,5,'thks') | 'thks' IN (1,3,5,'thks') |
+---------------------+--------------------------+
|               0     |                    1     |
+---------------------+--------------------------+
mysql> SELECT 2 NOT IN (1,3,5,'thks'), 'thks' NOT IN (1,3,5,'thks');
+-------------------------+------------------------------+
| 2 NOT IN (1,3,5,'thks') | 'thks' NOT IN (1,3,5,'thks') |
+-------------------------+------------------------------+
|                   1     |                        0     |
+-------------------------+------------------------------+

由结果可以看到，IN 和 NOT IN 的返回值正好相反。

在左侧表达式为 NULL 的情况下，或是表中找不到匹配项并且表中一个表达式为 NULL 的情况下，IN的返回值均为 NULL。

【实例】存在 NULL 值时的 IN 查询，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     |
+------------------------+-------------------------+

IN() 语法也可用于在 SELECT 语句中进行嵌套子查询，在后面的章节中将会讲到。

13、LIKE

LIKE 运算符用来匹配字符串，语法格式为：

expr LIKE 匹配条件

如果 expr 满足匹配条件，则返回值为 1（True）；如果不匹配，则返回值为 0（False）。expr 或匹配条件中任何一个为 NULL，则结果为 NULL。

LIKE 运算符在进行匹配时，可以使用下面的两种通配符：

• “%”：匹配任何数目的字符，甚至包括零字符。
• “_”：只能匹配一个字符。

【实例】使用运算符 LIKE 进行字符串匹配运算，SQL 语句如下：

mysql> SELECT 'stud' LIKE 'stud', 'stud' LIKE 'stu_','stud' LIKE '%d','stud' LIKE 't_ _ _', 's' LIKE NULL;
+------------------+------------------+---------------+--------------------+--------------+
|'stud' LIKE 'stud'|'stud' LIKE 'stu_'|'stud' LIKE '%d'|'stud' LIKE 't_ _ _'| 's' LIKE NULL |
+------------------+------------------+---------------+--------------------+--------------+
|             1    |              1   |            1  |              0     | NULL         |
+------------------+------------------+---------------+--------------------+--------------+

由结果可以看到，指定匹配字符串为“stud”。“stud”表示直接匹配“stud”字符串，满足匹配条件，返回 1；“stu_”表示匹配以 stu 开头的长度为 4 个字符的字符串，“stud”正好是 4 个字符，满足匹配条件，因此匹配成功，返回 1；“%d”表示匹配以字母“d”结尾的字符串，“stud”满足匹配条件，匹配成功，返回 1；“t _ _ _”表示匹配以“t”开头的长度为 4 个字符的字符串，“stud”不满足匹配条件，因此返回 0；当字符“s”与 NULL 匹配时，结果为 NULL。

14、REGEXP

REGEXP 运算符用来匹配字符串，语法格式为：

expr REGEXP 匹配条件

如果 expr 满足匹配条件，则返回 1；如果不满足，则返回 0。若 expr 或匹配条件任意一个为 NULL，则结果为 NULL。

REGEXP运算符在进行匹配时，常用的有下面几种通配符：

• “^”：匹配以该字符后面的字符开头的字符串。
• “$”：匹配以该字符后面的字符结尾的字符串。
• “.”：匹配任何一个单字符。
• “[...]”：匹配在方括号内的任何字符。例如：“[abc]”匹配“a”“b”或“c”。为了命名字符的范围，使用一个“-”。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字。
• “*”：匹配零个或多个在它前面的字符。例如：“x*”匹配任何数量的“x”字符，“[0-9]*”匹配任何数量的数字，而“*”匹配任何数量的任何字符。

【实例】使用运算符 REGEXP 进行字符串匹配运算，SQL 语句如下：

mysql> SELECT 'ssky' REGEXP '^s', 'ssky' REGEXP 'y$', 'ssky' REGEXP '.sky', 'ssky' REGEXP '[ab]';
+------------------+------------------+--------------------+--------------------+
|'ssky' REGEXP '^s'|'ssky' REGEXP 'y$'|'ssky' REGEXP '.sky'|'ssky' REGEXP '[ab]'|
+------------------+------------------+--------------------+--------------------+
|            1     |            1     |             1      |                0   |
+------------------+------------------+--------------------+--------------------+

由结果可以看到，指定匹配字符串为“ssky”：

• “^s”表示匹配任何以字母“s”开头的字符串，因此满足匹配条件，返回 1；
• “y$”表示任何以字母“y”结尾的字符串，因此满足匹配条件，返回 1；
• “.sky”匹配任何以“sky”结尾、字符长度为 4 的字符串，满足匹配条件，返回 1；
• “[ab]”匹配任何包含字母“a”或者“b”的字符串，指定字符串中既没有字母“a”也没有字母“b”，因此不满足匹配条件，返回 0。

正则表达式是一个可以进行复杂查询的强大工具。相对于 LIKE 字符串匹配，它可以使用更多的通配符类型，查询结果更加灵活。读者可以参考相关的书籍或资料，深入学习正则表达式的写法，在这里就不详细介绍了。