在本教程中,您将了解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')
返回MySQLNULLIF
,因为两个字符串不相等。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
函数,这在某些情况下非常方便,例如阻止查询中的零错误。