MySQL :: MySQL 9.0 参考手册 :: 14.4.2 比较函数和运算符

版本 9.0

8.4 当前
8.0
5.7

8.0 日语

MySQL 9.0 参考手册 / ... / 比较函数和运算符

14.4.2 比较函数和运算符

表 14.4 比较运算符

名称	描述
`>`	大于运算符
`>=`	大于或等于运算符
`<`	小于运算符
`<>`, `!=`	不等于运算符
`<=`	小于或等于运算符
`<=>`	空值安全的等于运算符
`=`	等于运算符
`BETWEEN ... AND ...`	值是否在值的范围内
`COALESCE()`	返回第一个非 NULL 参数
`GREATEST()`	返回最大的参数
`IN()`	值是否在一组值中
`INTERVAL()`	返回小于第一个参数的参数的索引
`IS`	根据布尔值测试值
`IS NOT`	根据布尔值测试值
`IS NOT NULL`	非 NULL 值测试
`IS NULL`	NULL 值测试
`ISNULL()`	测试参数是否为 NULL
`LEAST()`	返回最小的参数
`LIKE`	简单模式匹配
`NOT BETWEEN ... AND ...`	值是否不在值的范围内
`NOT IN()`	值是否不在一组值中
`NOT LIKE`	简单模式匹配的否定
`STRCMP()`	比较两个字符串

比较操作的结果值为 1 (TRUE)、0 (FALSE) 或 NULL。这些操作适用于数字和字符串。字符串会根据需要自动转换为数字，数字也会转换为字符串。

以下关系比较运算符不仅可以用于比较标量操作数，还可以用于比较行操作数：

=  >  <  >=  <=  <>  !=

本节稍后将详细介绍这些运算符如何处理行操作数。有关在行子查询上下文中进行行比较的其他示例，请参阅第 15.2.15.5 节“行子查询”。

本节中的一些函数返回的值不是 1 (TRUE)、0 (FALSE) 或 NULL。例如 LEAST() 和 GREATEST() 函数；第 14.3 节“表达式求值中的类型转换” 介绍了这些函数和类似函数在执行比较操作以确定其返回值时所遵循的规则。

注意

在以前版本的 MySQL 中，当评估包含 LEAST() 或 GREATEST() 的表达式时，服务器会尝试猜测使用该函数的上下文，并将函数的参数强制转换为整个表达式的的数据类型。例如，LEAST("11", "45", "2") 的参数将作为字符串进行评估和排序，因此该表达式返回 "11"。

该函数使用提供的参数执行，仅当参数的类型不完全相同时，才会对一个或多个参数执行数据类型转换。现在，在函数执行后，将执行任何由使用返回值的表达式强制执行的类型强制转换。这意味着 LEAST("11", "45", "2") + 0 将评估为 "11" + 0，因此为整数 11。

要将值转换为特定类型以进行比较，可以使用 CAST() 函数。可以使用 CONVERT() 将字符串值转换为不同的字符集。请参阅第 14.10 节“强制转换函数和运算符”。

默认情况下，字符串比较不区分大小写，并使用当前字符集。默认值为 utf8mb4。

=

等于

mysql> SELECT 1 = 0;
        -> 0
mysql> SELECT '0' = 0;
        -> 1
mysql> SELECT '0.0' = 0;
        -> 1
mysql> SELECT '0.01' = 0;
        -> 0
mysql> SELECT '.01' = 0.01;
        -> 1

对于行比较，(a, b) = (x, y) 等价于

(a = x) AND (b = y)

<=>
NULL 安全等于。此运算符执行类似于 = 运算符的相等比较，但如果两个操作数均为 NULL，则返回 1 而不是 NULL，如果一个操作数为 NULL，则返回 0 而不是 NULL。
<=> 运算符等价于标准 SQL IS NOT DISTINCT FROM 运算符。
```
mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
        -> 1, 1, 0
mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;
        -> 1, NULL, NULL
```
对于行比较，(a, b) <=> (x, y) 等价于
```
(a <=> x) AND (b <=> y)
```

<>, !=

不等于

mysql> SELECT '.01' <> '0.01';
        -> 1
mysql> SELECT .01 <> '0.01';
        -> 0
mysql> SELECT 'zapp' <> 'zappp';
        -> 1

对于行比较，(a, b) <> (x, y) 和 (a, b) != (x, y) 等价于

(a <> x) OR (b <> y)

<=

小于或等于

mysql> SELECT 0.1 <= 2;
        -> 1

对于行比较，(a, b) <= (x, y) 等价于

(a < x) OR ((a = x) AND (b <= y))

<

小于

mysql> SELECT 2 < 2;
        -> 0

对于行比较，(a, b) < (x, y) 等价于

(a < x) OR ((a = x) AND (b < y))

>=

大于或等于

mysql> SELECT 2 >= 2;
        -> 1

对于行比较，(a, b) >= (x, y) 等价于

(a > x) OR ((a = x) AND (b >= y))

>

大于

mysql> SELECT 2 > 2;
        -> 0

对于行比较，(a, b) > (x, y) 等价于

(a > x) OR ((a = x) AND (b > y))

expr BETWEEN min AND max
如果 expr 大于或等于 min 并且 expr 小于或等于 max，则 BETWEEN 返回 1，否则返回 0。如果所有参数类型相同，则这等效于表达式 (min <= expr AND expr <= max)。否则，将根据第 14.3 节“表达式求值中的类型转换” 中描述的规则进行类型转换，但应用于所有三个参数。
```
mysql> SELECT 2 BETWEEN 1 AND 3, 2 BETWEEN 3 and 1;
        -> 1, 0
mysql> SELECT 1 BETWEEN 2 AND 3;
        -> 0
mysql> SELECT 'b' BETWEEN 'a' AND 'c';
        -> 1
mysql> SELECT 2 BETWEEN 2 AND '3';
        -> 1
mysql> SELECT 2 BETWEEN 2 AND 'x-3';
        -> 0
```
为了在对日期或时间值使用 BETWEEN 时获得最佳结果，请使用 CAST() 将值显式转换为所需的数据类型。示例：如果将 DATETIME 与两个 DATE 值进行比较，则将 DATE 值转换为 DATETIME 值。如果在与 DATE 的比较中使用字符串常量（如 '2001-1-1'），则将字符串转换为 DATE。
expr NOT BETWEEN min AND max
这与 NOT (expr BETWEEN min AND max) 相同。
COALESCE(value,...)
返回列表中的第一个非 NULL 值，如果不存在非 NULL 值，则返回 NULL。
COALESCE() 的返回类型是参数类型的聚合类型。
```
mysql> SELECT COALESCE(NULL,1);
        -> 1
mysql> SELECT COALESCE(NULL,NULL,NULL);
        -> NULL
```
GREATEST(value1,value2,...)
对于两个或多个参数，返回最大（最大值）参数。使用与 LEAST() 相同的规则比较参数。
```
mysql> SELECT GREATEST(2,0);
        -> 2
mysql> SELECT GREATEST(34.0,3.0,5.0,767.0);
        -> 767.0
mysql> SELECT GREATEST('B','A','C');
        -> 'C'
```
如果任何参数为 NULL，则 GREATEST() 返回 NULL。
expr IN (value,...)
如果 expr 等于 IN() 列表中的任何值，则返回 1（真），否则返回 0（假）。
类型转换根据第 14.3 节“表达式求值中的类型转换” 中描述的规则进行，应用于所有参数。如果 IN() 列表中的值不需要类型转换，则它们都是相同类型的非 JSON 常量，并且 expr 可以作为相同类型的值（可能在类型转换后）与每个值进行比较，则会进行优化。列表中的值将进行排序，并使用二进制搜索完成对 expr 的搜索，这使得 IN() 操作非常快。
```
mysql> SELECT 2 IN (0,3,5,7);
        -> 0
mysql> SELECT 'wefwf' IN ('wee','wefwf','weg');
        -> 1
```
IN() 可用于比较行构造函数
```
mysql> SELECT (3,4) IN ((1,2), (3,4));
        -> 1
mysql> SELECT (3,4) IN ((1,2), (3,5));
        -> 0
```
您永远不应该在 IN() 列表中混合使用带引号和不带引号的值，因为带引号的值（例如字符串）和不带引号的值（例如数字）的比较规则不同。因此，混合类型可能会导致不一致的结果。例如，不要像这样编写 IN() 表达式
```
SELECT val1 FROM tbl1 WHERE val1 IN (1,2,'a');
```
而是像这样编写
```
SELECT val1 FROM tbl1 WHERE val1 IN ('1','2','a');
```
隐式类型转换可能会产生违反直觉的结果
```
mysql> SELECT 'a' IN (0), 0 IN ('b');
        -> 1, 1
```
在这两种情况下，比较值都将转换为浮点值，在每种情况下都产生 0.0，比较结果为 1（真）。
IN() 列表中的值的数量仅受 max_allowed_packet 值的限制。
为了符合 SQL 标准，IN() 不仅在左侧的表达式为 NULL 时返回 NULL，而且在列表中找不到匹配项且列表中的一个表达式为 NULL 时也返回 NULL。
IN() 语法也可以用于编写某些类型的子查询。请参阅第 15.2.15.3 节“带有 ANY、IN 或 SOME 的子查询”。
expr NOT IN (value,...)
这与 NOT (expr IN (value,...)) 相同。
INTERVAL(N,N1,N2,N3,...)
如果 N ≤ N1，则返回 0；如果 N ≤ N2，则返回 1，依此类推；如果 N 为 NULL，则返回 -1。所有参数均视为整数。要使此函数正常工作，需要 N1 ≤ N2 ≤ N3 ≤ ... ≤ Nn。这是因为使用了二进制搜索（速度非常快）。
```
mysql> SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200);
        -> 3
mysql> SELECT INTERVAL(10, 1, 10, 100, 1000);
        -> 2
mysql> SELECT INTERVAL(22, 23, 30, 44, 200);
        -> 0
```
IS boolean_value
根据布尔值测试值，其中 boolean_value 可以是 TRUE、FALSE 或 UNKNOWN。
```
mysql> SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN;
        -> 1, 1, 1
```
IS NOT boolean_value
根据布尔值测试值，其中 boolean_value 可以是 TRUE、FALSE 或 UNKNOWN。
```
mysql> SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN;
        -> 1, 1, 0
```
IS NULL
测试值是否为 NULL。
```
mysql> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;
        -> 0, 0, 1
```
为了与 ODBC 程序良好配合，MySQL 在使用 IS NULL 时支持以下额外功能
- 如果 sql_auto_is_null 变量设置为 1，则在成功插入自动生成的 AUTO_INCREMENT 值的语句之后，可以通过发出以下形式的语句来查找该值
```
SELECT * FROM tbl_name WHERE auto_col IS NULL
```
  如果该语句返回一行，则返回的值与调用 LAST_INSERT_ID() 函数时相同。有关详细信息（包括多行插入后的返回值），请参阅第 14.15 节“信息函数”。如果没有成功插入 AUTO_INCREMENT 值，则 SELECT 语句不返回任何行。
  可以通过设置 sql_auto_is_null = 0 来禁用使用 IS NULL 比较检索 AUTO_INCREMENT 值的行为。请参阅第 7.1.8 节“服务器系统变量”。
  sql_auto_is_null 的默认值为 0。
- 对于声明为 NOT NULL 的 DATE 和 DATETIME 列，可以使用如下语句查找特殊日期 '0000-00-00'
```
SELECT * FROM tbl_name WHERE date_column IS NULL
```
  这是使某些 ODBC 应用程序正常工作所必需的，因为 ODBC 不支持 '0000-00-00' 日期值。
  请参阅获取自动增量值，以及连接器/ODBC 连接参数中对 FLAG_AUTO_IS_NULL 选项的描述。

IS NOT NULL

测试值是否不为 NULL。

mysql> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
        -> 1, 1, 0

ISNULL(expr)
如果 expr 为 NULL，则 ISNULL() 返回 1，否则返回 0。
```
mysql> SELECT ISNULL(1+1);
        -> 0
mysql> SELECT ISNULL(1/0);
        -> 1
```
可以使用 ISNULL() 代替 = 来测试值是否为 NULL。（使用 = 将值与 NULL 进行比较始终会产生 NULL。）
ISNULL() 函数与 IS NULL 比较运算符共享一些特殊行为。请参阅 IS NULL 的描述。
LEAST(value1,value2,...)
对于两个或多个参数，返回最小（最小值）参数。使用以下规则比较参数
- 如果任何参数为 NULL，则结果为 NULL。无需比较。
- 如果所有参数都是整数值，则将它们作为整数进行比较。
- 如果至少一个参数是双精度值，则将它们作为双精度值进行比较。否则，如果至少一个参数是 DECIMAL 值，则将它们作为 DECIMAL 值进行比较。
- 如果参数包含数字和字符串的混合，则将它们作为字符串进行比较。
- 如果任何参数是非二进制（字符）字符串，则将参数作为非二进制字符串进行比较。
- 在所有其他情况下，参数都作为二进制字符串进行比较。
LEAST() 的返回类型是比较参数类型的聚合类型。
```
mysql> SELECT LEAST(2,0);
        -> 0
mysql> SELECT LEAST(34.0,3.0,5.0,767.0);
        -> 3.0
mysql> SELECT LEAST('B','A','C');
        -> 'A'
```