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_id	phone_number
1	111-1111-1111
2	222-2222-2222
3	000-0000-0000
4	NULL

isnull 示例

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

user_id	phone_number
3	NULL
4	NULL

coalesce 示例

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

user_id	username	phone_number
1	Alice	111-1111-1111
2	Bob	222-2222-2222
3	Unknown	000-0000-0000
4	Unknown	000-0000-0000

nullif 示例

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

product_name	unit_price
Apple	1.00
Banana	0.50
Orange	NULL
Mango	2.00