摘要:在本教程中,您将了解MySQL NULLIF函数以及如何使用它来防止查询中出现除零错误。
MySQL NULLIF函数简介
NULLIF
函数是接受 2 个参数的控制流函数之一。如果第一个参数等于第二个参数,则NULLIF
函数返回NULL
,否则返回第一个参数。
NULLIF
函数的语法如下:
NULLIF(expression_1,expression_2);
Code language: SQL (Structured Query Language) (sql)
如果expression_1 = expression_2
为true
,则NULLIF
函数返回NULL
,否则返回expression_1
请注意, NULLIF
函数类似于以下使用CASE 表达式的表达式:
CASE WHEN expression_1 = expression_2
THEN NULL
ELSE
expression_1
END;
Code language: SQL (Structured Query Language) (sql)
请注意,不应将NULLIF
函数与称为IFNULL 函数的类似函数混淆。
MySQL NULLIF 示例
让我们看一些使用NULLIF
函数的示例来了解它的工作原理。
SELECT NULLIF(1,1); -- return NULL
Code language: SQL (Structured Query Language) (sql)
SELECT NULLIF(1,2); -- return 1
Code language: SQL (Structured Query Language) (sql)
SELECT NULLIF('MySQL NULLIF','MySQL NULLIF'); -- return NULL
Code language: SQL (Structured Query Language) (sql)
SELECT NULLIF('MySQL NULLIF','MySQL IFNULL'); -- return MySQL NULLIF
Code language: SQL (Structured Query Language) (sql)
SELECT NULLIF(1,NULL); -- return 1 because 1 <=> NULL
Code language: SQL (Structured Query Language) (sql)
SELECT NULLIF(NULL,1); -- return NULL the first argument
Code language: SQL (Structured Query Language) (sql)
语句如何工作。
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
Code language: SQL (Structured Query Language) (sql)
在这种情况下,您可以使用NULLIF
函数来防止被零除,如下所示:
SELECT 1/NULLIF(0,0); -- return NULL
Code language: SQL (Structured Query Language) (sql)
由于零等于零,因此表达式NULLIF(0,0)
返回 NULL。结果,该语句返回 NULL。
让我们看一下示例数据库中的orders
表。
首先,要获取 2003 年 6 月创建的所有订单,请使用以下查询:
SELECT
orderNumber, orderdate, requiredDate, shippedDate, status
FROM
orders
WHERE
orderDate BETWEEN '2003-06-01' AND '2003-06-30';
Code language: SQL (Structured Query Language) (sql)
其次,计算2003年6月的发货订单数/取消订单数,使用SUM和IF函数。
SELECT SUM(IF(status = 'Shipped',1,0)) /
SUM(IF(status = 'Cancelled',1,0))
FROM orders
WHERE orderDate BETWEEN '2003-06-01' and '2003-06-30';
Code language: SQL (Structured Query Language) (sql)
MySQL 发出错误,因为 2003 年 6 月没有创建取消的订单。这意味着表达式SUM(IF(status = 'Cancelled',1,0))
返回零。
第三,为了防止被零除错误,可以使用NULLIF
函数作为以下查询:
SELECT
SUM(IF(status = 'Shipped', 1, 0)) /
NULLIF(SUM(IF(status = 'Cancelled', 1, 0)), 0)
FROM
orders
WHERE
orderDate BETWEEN '2003-06-01' AND '2003-06-30';
Code language: SQL (Structured Query Language) (sql)
由于 2003 年 6 月没有创建已取消的订单,因此SUM(IF(status = 'Cancelled',1,0)
表达式返回零,这也使得NULLIF(SUM(IF(status = 'Cancelled',1,0),0)
表达式返回NULL
值。
在本教程中,我们向您介绍了NULLIF
函数,该函数在某些情况下非常方便,例如防止查询中出现除零错误。