全部教學 » MySQL nullif()函式

MySQL nullif()函式

2019-10-16 22:54:58

在本教學中，您將了解MySQL NULLIF函式以及如何使用它來防止查詢中的除以零錯誤。

MySQL NULLIF函式簡介

NULLIF函式是接受2個引數的控制流函式之一。如果第一個引數等於第二個引數，則NULLIF函式返回NULL，否則返回第一個引數。

NULLIF函式的語法如下：

NULLIF(expression_1,expression_2);

如果expression_1 = expression_2為true，則NULLIF函式返回NULL，否則返回expression_1 。

請注意，NULLIF函式與以下使用CASE的表示式類似：

CASE WHEN expression_1 = expression_2
   THEN NULL
ELSE
   expression_1
END;

請注意，不要將NULLIF函式與IFNULL函式混淆。

MySQL NULLIF範例

我們來看一下使用NULLIF函式來了解它的工作原理的一些例子。

範例-1

mysql> SELECT NULLIF(1,1);
+-------------+
| NULLIF(1,1) |
+-------------+
| NULL        |
+-------------+
1 row in set

範例-2

mysql> SELECT NULLIF(1,2);
+-------------+
| NULLIF(1,2) |
+-------------+
|           1 |
+-------------+
1 row in set

範例-3

mysql> SELECT NULLIF('MySQL NULLIF','MySQL NULLIF');
+---------------------------------------+
| NULLIF('MySQL NULLIF','MySQL NULLIF') |
+---------------------------------------+
| NULL                                  |
+---------------------------------------+
1 row in set

範例-4

mysql> SELECT NULLIF('MySQL NULLIF','MySQL IFNULL');
+---------------------------------------+
| NULLIF('MySQL NULLIF','MySQL IFNULL') |
+---------------------------------------+
| MySQL NULLIF                          |
+---------------------------------------+
1 row in set

範例-6

mysql> SELECT NULLIF(1,NULL);
+----------------+
| NULLIF(1,NULL) |
+----------------+
|              1 |
+----------------+
1 row in set

範例-7

mysql> SELECT NULLIF(NULL,1);
+----------------+
| NULLIF(NULL,1) |
+----------------+
| NULL           |
+----------------+
1 row in set

上面範例中的語句是如何工作的？

  • NULIF(1,1)返回NULL，因為1等於1。
  • NULLIF(1,2)返回1，這是第一個引數，因為1不等於2。
  • NULLIF('MySQL NULLIF'，'MySQL NULLIF')返回NULL，因為兩個引數是相同的字串。
  • NULLIF('MySQL NULLIF'，'MySQL NULLIF')返回MySQL NULLIF，因為兩個字串不相等。
  • NULLIF(1，NULL)返回1，因為1不等於NULL。
  • NULLIF(NULL，1)返回第一個引數，即NULL，因為NULL不等於1。

使用NULLIF函式來防止除零錯誤

我們經常使用NULLIF函式來阻止在查詢中除以零錯誤。如果MySQL伺服器啟用了ERROR_FOR_DIVISION_BY_ZERO模式，則當發生零除數時將發出錯誤。

見下列查詢語句：

SELECT 1/0; -- cause error

上面語句得到以下結果 - 

mysql> SELECT 1/0; 
+------+
| 1/0  |
+------+
| NULL |
+------+
1 row in set

在這種情況下，您可以使用NULLIF函式來阻止除以零，如下所示：

SELECT 1/NULLIF(0,0); -- return NULL

因為0等於0，所以NULLIF(0,0)表示式返回NULL。結果語句返回NULL。

我們來看看範例資料庫(yiibaidb)中的orders表，其結構如下所示 -

mysql> desc orders;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| orderNumber    | int(11)     | NO   | PRI | NULL    |       |
| orderDate      | date        | NO   |     | NULL    |       |
| requiredDate   | date        | NO   |     | NULL    |       |
| shippedDate    | date        | YES  |     | NULL    |       |
| status         | varchar(15) | NO   |     | NULL    |       |
| comments       | text        | YES  |     | NULL    |       |
| customerNumber | int(11)     | NO   | MUL | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
7 rows in set

首先，要獲取2013年6月建立的所有訂單，請使用以下查詢：

SELECT 
    orderNumber, orderdate, requiredDate, shippedDate, status
FROM
    orders
WHERE
    orderDate BETWEEN '2013-06-01' AND '2013-06-30';

執行上面查詢語句得到以下結果 - 

+-------------+------------+--------------+-------------+---------+
| orderNumber | orderdate  | requiredDate | shippedDate | status  |
+-------------+------------+--------------+-------------+---------+
|       10127 | 2013-06-03 | 2013-06-09   | 2013-06-06  | Shipped |
|       10128 | 2013-06-06 | 2013-06-12   | 2013-06-11  | Shipped |
|       10129 | 2013-06-12 | 2013-06-18   | 2013-06-14  | Shipped |
|       10130 | 2013-06-16 | 2013-06-24   | 2013-06-21  | Shipped |
|       10131 | 2013-06-16 | 2013-06-25   | 2013-06-21  | Shipped |
|       10132 | 2013-06-25 | 2013-07-01   | 2013-06-28  | Shipped |
|       10133 | 2013-06-27 | 2013-07-04   | 2013-07-03  | Shipped |
+-------------+------------+--------------+-------------+---------+
7 rows in set

第二，計算2013年6月發貨訂單數量/取消訂單數量，可使用SUM和IF函式。

SELECT SUM(IF(status = 'Shipped',1,0)) / 
       SUM(IF(status = 'Cancelled',1,0))
FROM orders
WHERE orderDate BETWEEN '2013-06-01' and '2013-06-30';

執行上面查詢語句，得到以下結果 - 

+------------------------------------------------------------------------+
| SUM(IF(status = 'Shipped',1,0)) /SUM(IF(status = 'Cancelled',1,0)) |
+------------------------------------------------------------------------+
| NULL                                                                       |
+------------------------------------------------------------------------+
1 row in set

MySQL發出錯誤，因為在2013年6月沒有建立取消訂單。 這意味著表示式SUM(IF(status ='Cancelled'，1,0))返回0。

第三，為了防止除0錯誤，您可以使用NULLIF函式，如下查詢：

SELECT 
    SUM(IF(status = 'Shipped', 1, 0)) /NULLIF(SUM(IF(status = 'Cancelled', 1, 0)), 0)
FROM
    orders
WHERE
    orderDate BETWEEN '2013-06-01' AND '2013-06-30';

執行上面查詢語句，得到以下結果 - 

+-------------------------------------------------------------------------+
| SUM(IF(status = 'Shipped', 1, 0)) /NULLIF(SUM(IF(status = 'Cancelled', 1, 0)), 0) |
+-------------------------------------------------------------------------+
| NULL                                                                              |
+-------------------------------------------------------------------------+
1 row in set

因為2013年6月沒有建立取消訂單，所以SUM(IF(status ='Cancelled'，1,0))返回0，這也使得NULLIF(SUM(IF(status ='Cancelled'，1,0) ，0)表示式返回NULL值。

在本教學中，我們向您介紹了NULLIF函式，這在某些情況下非常方便，例如阻止查詢中的零錯誤。