ifnull 和 isnull 的用法 以及其平替方法

ifnull 和 isnull 的用法

ifnull 和 isnull 都是 MySQL 的函数，用于对 NULL 值进行处理。在 SQL 查询中，遇到 NULL 值会导致计算错误或者返回空值，因此需要使用 ifnull 和 isnull 对 NULL 值进行检测和替换。ifnull 函数可以用于替换空值，isnull 函数则用于判断给定的表达式是否为 NULL 值。ifnull 和 isnull 的语法如下：

IFNULL(expr1, expr2); ISNULL(expr); 

其中，

expr1

是需要检测的表达式，

expr2

是用于替换 NULL 值的值。使用 ifnull 和 isnull 时需要注意以下几点：

• ifnull 函数返回第一个非 NULL 值，如果都是 NULL，则返回 expr2。
• isnull 函数返回一个布尔值，如果表达式为 NULL，则返回 1，否则返回 0。
• ifnull 和 isnull 都可以在 SELECT、INSERT、UPDATE 和 REPLACE 语句中使用。

假设我们有一个存储用户信息的表

users

，其中包含了用户 ID、用户名和手机号三个字段。但是有些用户可能没有手机号，因此手机号列可能会出现 NULL 值。我们现在需要查询所有用户的 ID 和手机号，如果手机号为空，则使用默认值 000-0000-0000。可以使用 ifnull 函数如下：

SELECT user_id, IFNULL(phone_number, '000-0000-0000') AS phone_number FROM users; 

在上面的示例中，我们使用了 IFNULL 函数检测手机号列是否为 NULL 值，并根据检测结果用默认值替换了 NULL 值。

如果不需要替换 NULL 值，而只是需要判断表达式是否为 NULL，则可以使用 isnull 函数。例如，我们现在需要查询所有手机号为空的用户信息，可以使用 isnull 函数如下：

SELECT user_id, phone_number FROM users WHERE ISNULL(phone_number); 

在上面的示例中，我们使用了 ISNULL 函数判断手机号列是否为 NULL 值，并在 WHERE 子句中过滤了为空的结果。

其他与 ifnull 和 isnull 类似的函数

除了 ifnull 和 isnull，还有一些与这两个函数类似的函数可以用于对 NULL 值进行处理。其中比较常见的包括以下几种：

coalesce

coalesce 函数是一个通用的函数，可以用于检测多个表达式是否为 NULL 值，并返回第一个非 NULL 值。其语法如下：

COALESCE(expr1, expr2, ..., expr_n); 

在上面的语法中，

expr1

是需要检测的第一个表达式，

expr2

到

expr_n

是需要检测的其他表达式。如果所有表达式都为 NULL，则返回 NULL 值。

假设我们有一个存储用户信息的表

users

，其中包含了用户 ID、用户名和手机号三个字段。但是有些用户可能没有手机号或者用户名，因此这些列可能会出现 NULL 值。我们现在需要查询所有用户的 ID、用户名和手机号，如果这些列为空，则使用默认值。可以使用 coalesce 函数如下：

SELECT user_id, COALESCE(username, 'Unknown') AS username, COALESCE(phone_number, '000-0000-0000') AS phone_number FROM users; 

在上面的示例中，我们使用了 COALESCE 函数检测用户名和手机号列是否为 NULL 值，并根据检测结果用默认值替换了 NULL 值。

nullif

nullif 函数用于比较两个表达式，如果这两个表达式相等，则返回 NULL 值。主要用于在避免除以零或其他无效操作时使用。其语法如下：

NULLIF(expr1, expr2); 

在上面的语法中，

expr1

和

expr2

是需要比较的表达式。如果这两个表达式相等，则返回 NULL 值。

假设我们有一个存储商品信息的表

products

，其中包含了商品名称、价格和数量三个字段。我们现在需要计算每个商品的单价，但是有些商品数量为 0，因此需要使用 nullif 函数避免除以零。可以使用 nullif 函数如下：

SELECT product_name, price / NULLIF(quantity, 0) AS unit_price FROM products; 

在上面的示例中，我们使用了 NULLIF 函数比较数量列是否为 0，如果是则返回 NULL 值，避免了除以零的错误。

结论

ifnull 和 isnull 是 MySQL 中常用的函数，用于处理 NULL 值。ifnull 用于替换 NULL 值，isnull 用于判断表达式是否为 NULL 值。除了这两个函数之外，还有一些与 ifnull 和 isnull 类似的函数可用于对 NULL 值进行处理，包括 coalesce 和 nullif 等。在使用这些函数时，需要根据实际需求选择合适的语句，以达到最佳的查询效果。

示例代码

以下是用于演示 ifnull、isnull、coalesce 和 nullif 的 SQL 语句以及相应的结果：

ifnull 示例

SELECT user_id, IFNULL(phone_number, '000-0000-0000') AS phone_number FROM users; 

user_idphone_number1111-1111-11112222-2222-22223000-0000-00004NULL

isnull 示例

SELECT user_id, phone_number FROM users WHERE ISNULL(phone_number); 

user_idphone_number3NULL4NULL

coalesce 示例

SELECT user_id, COALESCE(username, 'Unknown') AS username, COALESCE(phone_number, '000-0000-0000') AS phone_number FROM users; 

user_idusernamephone_number1Alice111-1111-11112Bob222-2222-22223Unknown000-0000-00004Unknown000-0000-0000

nullif 示例

SELECT product_name, price / NULLIF(quantity, 0) AS unit_price FROM products; 

product_nameunit_priceApple1.00Banana0.50OrangeNULLMango2.00