MySQL ISNULL

By:
On:
In: MySQL
Tagged: ,

2013-05-22_090435

这是MySQL 5实力养成暨评量里的5-03.‘在运算式中，ISNULL函式可用来检验括号内的值是否为NULL，试问执行SELECT ISNULL(1/0);结果为何？’

答案：(B) 1

[adsense][/adsense]

这个运算应该是NULL，所以要回答true，也就是1，不过一样是不够专注，就被骗了，但是其实可能也是有一点疑虑，所以就应该再复习一次手册才是。

MySQL 5.7 Reference Manual :: 12 Functions and Operators :: 12.3 Operators :: 12.3.2 Comparison Functions and Operators

或

MySQL 5.1参考手册 :: 12. 函数和操作符::12.1. 操作符::12.1.3. 比较函数和操作符

转译繁体中文如下：

比较运算产生的结果为1(TRUE)、0 (FALSE)或 NULL。这些运算可用于数字和字串。根据需要，字串可自动转换为数字，而数字也可自动转换为字串。

本章中的一些函数 (如LEAST()和GREATEST()) 的所得值不包括 1 (TRUE)、 0 (FALSE)和 NULL。然而，其所得值乃是基于按照下述规则运行的比较运算：

MySQL按照以下规则进行数值比较：

  • 若有一个或两个参数为 NULL，除非NULL-safe <=> 等算符，则比较运算的结果为NULL。
  • 若同一个比较运算中的两个参数都是字串，则按照字串进行比较。
  • 若两个参数均为整数，则按照整数进行比较。
  • 十六进制值在不需要作为数字进行比较时，则按照二进制字串进行处理。
  • 假如参数中的一个为 TIMESTAMP 或 DATETIME 列，而其它参数均为常数， 则在进行比较前将常数转为 timestamp。这样做的目的是为了使ODBC的进行更加顺利。 注意，这不适合IN()中的参数!为了更加可靠，在进行对比时通常使用完整的 datetime/date/time字串。
  • 在其它情况下，参数作为浮点数进行比较。

在预设状态下，字串比较不区分大小写，并使用现有字元编码(预设为cp1252 Latin1，同时对英语也适合)。

为了进行比较，可使用CAST()函数将某个值转为另外一种类型。 使用CONVERT()将字串值转为不同的字元编码。请参见12.8节，“Cast函数和操作符”。

以下例子说明了比较运算中将字串转为数字的过程：

mysql> SELECT 1 > ‘6x’;

-> 0

mysql> SELECT 7 > ‘6x’;

-> 1

mysql> SELECT 0 > ‘x6’;

-> 0

mysql> SELECT 0 = ‘x6’;

-> 1

注意，在将一个字串列同一个数字进行比较时， MySQL 不能使用列中的索引进行快速搜寻。假如str_col 是一个编入索引的字串列，则在以下语句中，索引不能执行搜寻功能：

SELECT * FROM tbl_name WHERE str_col=1;

其原因是许多不同的字串都可被转换为数值 1: ‘1’、 ‘ 1’、 ‘1a’、 ……

  • =

等于：

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

  • <=>

NULL-safe equal.这个操作符和=操作符执行相同的比较操作，不过在两个操作码均为NULL时，其所得值为1而不为NULL，而当一个操作码为NULL时，其所得值为0而不为NULL。

mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;

-> 1, 1, 0

mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;

-> 1, NULL, NULL

  • <> !=

不等于：

mysql> SELECT ‘.01’ <> ‘0.01’;

-> 1

mysql> SELECT .01 <> ‘0.01’;

-> 0

mysql> SELECT ‘zapp’ <> ‘zappp’;

-> 1

  • <=

小于或等于：

mysql> SELECT 0.1 <= 2;

-> 1

  • <

小于：

mysql> SELECT 2 < 2;

-> 0

  • >=

大于或等于：

mysql> SELECT 2 >= 2;

-> 1

  • >

大于：

mysql> SELECT 2 > 2;

-> 0

  • IS boolean_value IS NOT boolean_value

根据一个布尔值来检验一个值，在这里，布尔值可以是TRUE、FALSE或UNKNOWN。

mysql> SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN;

-> 1, 1, 1

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

-> 1, 1, 0

  • IS NULL IS NOT NULL

检验一个值是否为 NULL。

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

-> 0, 0, 1

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

-> 1, 1, 0

为了能够顺利的使用ODBC 程式工作，在使用IS NULL时，MySQL支援一下额外特性：

    • 在一个值产生后，立即运行一个以下格式的语句，从而找到最新AUTO_INCREMENT 值的所在行：

o                     SELECT * FROM tbl_name WHERE auto_col IS NULL

当设置SQL_AUTO_IS_NULL=0时，这项操作无法运行。请参见13.5.3节，“SET语法”。

    • 对于命名为NOT NULL 的DATE 和DATETIME列，可通过使用如下的 语句找到特定日期 ‘0000-00-00’：

o                     SELECT * FROM tbl_name WHERE date_column IS NULL

运行这一步需要使用一些 ODBC 应用软件，因为 ODBC本身不支援 一个 ‘0000-00-00’的时间值。

  • expr BETWEEN min AND max

假如expr大于或等于 min 且expr 小于或等于max, 则BETWEEN 的返回值为1,或是0。若所有参数都是同一类型，则上述关系相当于资料表达式   (min <= expr AND expr <= max)。其它类型的转换根据本章开篇所述规律进行，且适用于3种参数中任意一种。

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

  • expr NOT BETWEEN min AND max

这相当于NOT(expr BETWEEN min AND max)。

·         COALESCE(value,…)

返回值为列资料表当中的第一个非 NULL值，在没有非NULL 值得情况下返回值为 NULL 。

mysql> SELECT COALESCE(NULL,1);

-> 1

mysql> SELECT COALESCE(NULL,NULL,NULL);

-> NULL

·         GREATEST(value1,value2,…)

当有2或多个参数时，返回值为最大(最大值的)参数。比较参数所依据的规律同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。假如所有的值都是常数，则其计算和分类根据 expr 的类型进行。这时，使用二分搜索来搜索讯息。如IN值列资料表全部由常数组成，则意味着IN 的速度非常之快。如expr 是一个区分大小写的字串资料表达式，则字串比较也按照区分大小写的方式进行。

mysql> SELECT 2 IN (0,3,5,’wefwf’);

-> 0

mysql> SELECT ‘wefwf’ IN (0,3,5,’wefwf’);

-> 1

IN 列资料表中所列值的个数仅受限于 max_allowed_packet 值。

为了同SQL 标准相一致，在左侧资料表达式为NULL的情况下，或是资料表中找不到匹配项或是资料表中一个资料表达式为NULL 的情况下，IN的返回值均为NULL。

IN() 语构也可用书写某些类型的子查询。请参见13.2.8.3节，“使用ANY、IN和SOME进行子查询”。

·         expr NOT IN (value,…)

这与NOT (expr IN (value,…))相同。

  • ISNULL(expr)

如expr 为NULL，那么ISNULL() 的返回值为 1，否则返回值为 0。

mysql> SELECT ISNULL(1+1);

-> 0

mysql> SELECT ISNULL(1/0);

-> 1

使用= 的NULL 值对比通常是错误的。

ISNULL() 函数同 IS NULL比较操作符具有一些相同的特性。请参见有关IS NULL 的说明。

·         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

·         LEAST(value1,value2,…)

在有两个或多个参数的情况下， 返回值为最小 (最小值) 参数。用一下规则将自变数进行对比：

    • 假如返回值被用在一个 INTEGER 语境中，或是所有参数均为整数值，则将其作为整数值进行比较。
    • 假如返回值被用在一个 REAL语境中，或所有参数均为实值，则 将其作为实值进行比较。
    • 假如任意一个参数是一个区分大小写的字串，则将参数按照区分大小写的字串进行比较。
    • 在其它情况下，将参数作为区分大小写的字串进行比较。

假如任意一个自变数为NULL，则 LEAST()的返回值为NULL 。

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’

注意，上面的转换规则在一些边界情形中会产生一些奇特的结果：

mysql> SELECT CAST(LEAST(3600, 9223372036854775808.0) as SIGNED);

-> -9223372036854775808

发生这种情况的原因是MySQL在整数语境中读取9223372036854775808.0。整数资料表示法不利于保存数值，因此它包括一个带符号整数。

Comments are closed.