首頁 / CompScience / Database / MySQL / MySQL ISNULL

MySQL ISNULL

2013-05-22_090435

這是MySQL 5實力養成暨評量裡的5-03.『在運算式中,ISNULL函式可用來檢驗括號內的值是否為NULL,試問執行SELECT (1/0);結果為何?』

答案:(B) 1

這個運算應該是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函數和操作符」

以下例子說明了比較運算中將字串轉為數字的過程:

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。整數資料表示法不利於保存數值,因此它包括一個帶符號整數。

Print Friendly, PDF & Email
馬上成為工作達人的Fans

About ㄚ琪

工作達人Fun Taiwan的創辦者及總編,可以在這裡更認識他。

One comment

發表迴響

你的電子郵件位址並不會被公開。 Required fields are marked *

*

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料

Scroll To Top