关键词

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 字符串匹配,它可以使用更多的通配符类型,查询结果更加灵活。读者可以参考相关的书籍或资料,深入学习正则表达式的写法,在这里就不详细介绍了。

本文链接:http://task.lmcjl.com/news/14857.html

展开阅读全文