mysql中IFNULL,IF,CASE的區別介紹

2020-01-18 23:54:44

字體：大中小

來源：轉載

供稿：網友

假設有一數據表的狀態字段設計為varchar類型，有以下值：NULL,pending,pending refund,refund,cancel.
我們知道查詢狀態為cancel的訂單，SQL語句可以這樣寫：SELECT o.oid,o.moneyreceipt,o.moneyget,o.thecurrency,o.status FROM qorder o WHERE o.status = 'cancel'
SQL語句能查詢出正確的數據，但是當我們想查詢狀態為非cancel的訂單時，可能會出麻煩，因為status字段沒有設置NOT NULL，所以大部分訂單的status值都是NULL，這樣的話，用'<>'查詢出來的數據不正確，只有status除了cancel之外的非空數據查詢出來了，而為NULL的沒有查詢出來。SELECT o.oid,o.moneyreceipt,o.moneyget,o.thecurrency,o.status FROM qorder o WHERE o.status <>'cancel'原因：NULL值操作
NULL值可能令人感到奇怪直到你習慣它。概念上，NULL意味著“沒有值”或“未知值”，且它被看作與眾不同的值。為了測試NULL，你不能使用算術比較操作符例如=、<或!=。為了說明它，試試下列查詢：mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
| NULL | NULL | NULL | NULL |
+----------+-----------+----------+----------+
很顯然你不能通過這些比較得到有意義的結果。相反使用IS NULL和IS NOT NULL操作符：mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
| 0 | 1 |
+-----------+---------------+
請注意在MySQL中，0或 NULL意味著假,而其它值意味著真。布爾運算的默認真值是1。根據以上的NULL值操作結果，最終使用此種方式解決：SELECT o.oid,o.moneyreceipt,o.moneyget,o.thecurrency,o.status FROM qorder o WHERE IFNULL(o.status,'pending') <>'cancel'學習：IFNULL(expr1,expr2)
如果expr1不是NULL，IFNULL()返回expr1，否則它返回expr2。IFNULL()返回一個數字或字符串值，取決于它被使用的上下文環境。

復制代碼代碼如下:

 
mysql> select IFNULL(1,0); ->1 
mysql> select IFNULL(0,10); ->0 
mysql> select IFNULL(1/0,10); ->10.0000 
mysql> select IFNULL(1/0,'yes'); ->'yes'IF(expr1,expr2,expr3)如果expr1是TRUE(expr1<>0且expr1<>NULL)，那么IF()返回 expr2，否則它返回expr3。IF()返回一個數字或字符串值，取決于它被使用的上下文。 
mysql> select IF(1>2,2,3); -> 3 
mysql> select IF(1<2,'yes','no'); -> 'yes' 

(1) CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END //這個我還沒想到咋用，知道的可以留言交流，
(2) CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END //這個面試時被人問到了,可以用作條件判斷
第(1)個返回 result，其中value=compare-value。第(2)個中如果第一個條件為真，返回result。如果沒有匹配的result值，那么結果在ELSE后的result被返回。如果沒有ELSE部分，那么NULL被返回。

復制代碼代碼如下:

 
mysql> SELECT CASE 1 WHEN 1 THEN "one" WHEN 2 THEN "two" ELSE "more" END; -> "one" 
mysql> SELECT CASE WHEN 1>0 THEN "true" ELSE "false" END; -> "true" 
mysql> SELECT CASE BINARY "B" when "a" then 1 when "b" then 2 END; -> NULL 

上面的例子本人在mysql5.2.6上測試通過=====================================================================================PS:以上是轉載的內容，覺得很不錯就轉過來了，其中的CASE...WHEN...THEN、IF、IFNULL都是常用到的函數eg:SELECT id,username,CASE gender WHEN 1 THEN "男" WHEN 2 THEN "女" END FROM t_people; SELECT b.id,b.book_name,CASE b.type WHEN 1 THEN "計算機" WHEN 2 THEN "會計" ELSE "其它" END FROM book b;

上一篇：數據庫管理中文件的使用教程

下一篇：mysql 事務處理及表鎖定深入簡析