MySQL的函数和运算符 - 运算符 - 比较函数和运算符

数据库杂货铺 2021-06-04

618

比较函数和运算符

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

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

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

=  >  <  >=  <=  <>  !=

本节后面对这些运算符的描述详细介绍了它们如何处理行操作数。

本节中的某些函数返回的值不是 1 (TRUE)、0 (FALSE) 或 NULL。例如 LEAST() 和 GREATEST() 函数。

注意

在 MySQL 的以前版本中，在计算包含 LEAST() 或 GREATEST() 的表达式时，服务器试图猜测函数使用的上下文，并将函数的参数强制为整个表达式的数据类型。例如，对 LEAST("11", "45", "2") 的参数以字符串进行求值并排序，因此该表达式返回 "11"。在 MySQL 8.0.3 和更早版本中，当计算表达式 LEAST("11", "45", "2") + 0 时，服务器在对参数进行排序之前将参数转换为整数（预期整数 0 与结果相加），从而返回 2。

从 MySQL 8.0.4 开始，服务器不再试图以这种方式推断上下文。相反，函数使用提供的参数执行，当且仅当参数不是同一类型时，才对它们执行数据类型转换。任何使用返回值的表达式强制转换现在都是在函数执行之后执行的。这意味着，在 MySQL 8.0.4 及更高版本中，LEAST("11", "45", "2") + 0 的计算结果为 "11" + 0，因此为整数 11。

要将值转换为特定类型以进行比较，可以使用 CAST() 函数。可以使用 CONVERT() 将字符串值转换为其他字符集。

默认情况下，字符串比较不区分大小写，并使用当前字符集。默认值为 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)。

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 值。如果使用字符串常量（如 '2001-1-1'）与 DATE 比较，请将字符串转换为 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 (true)，否则返回 0 (false)。

类型转换根据“表达式求值中的类型转换”中描述的规则进行，适用于所有参数。如果 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（true）。

IN() 列表中的值数受 max_allowed_packet 值限制。

为了符合 SQL 标准，IN() 不仅在左侧的表达式为 NULL 时返回 NULL，而且如果列表中找不到匹配项，并且列表中的一个表达式为空，则 IN() 返回 NULL。

IN() 语法还可以用于编写某些类型的子查询。

● 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 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() 函数时的值相同。如果没有成功插入 AUTO_INCREMENT 值，SELECT 语句将不返回任何行。

通过使用 IS NULL 比较来检索 AUTO_INCREMENT 值的行为可以通过设置 sql_auto_is_null = 0 来禁用。

sql_auto_is_null 的默认值为 0。

■ 对于声明为 NOT NULL 的 DATE 和 DATETIME 列，使用以下语句会发现特殊日期 '0000-00-00'：

SELECT * FROM tbl_name WHERE date_column IS NULL

因为 ODBC 不支持 '0000-00-00' 日期值，所以需要这样做才能使某些 ODBC 应用程序正常工作。

● 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 值。）

● 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'

官方文档：

https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html

mysql

文章转载自数据库杂货铺，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL的函数和运算符 - 运算符 - 比较函数和运算符

评论