mysql中null（IFNULL，COALESCE和NULLIF）相关知识点总结

吾爱主题阅读：169 2024-04-05 16:19:38 评论：0

本文实例讲述了mysql中null（IFNULL，COALESCE和NULLIF）相关知识点。分享给大家供大家参考，具体如下：

在MySQL中，NULL值表示一个未知值，它不同于0或空字符串''，并且不等于它自身。

我们如果将NULL值与另一个NULL值或任何其他值进行比较，则结果为NULL，因为一个不知道是什么的值(NULL值)与另一个不知道是什么的值(NULL值)比较，其值当然也是一个不知道是什么的值(NULL值)。

然而我们通常，使用NULL值来表示数据丢失，未知或不适用的情况。例如，潜在客户的电话号码可能为NULL，并且可以稍后添加。所以我们创建表时，可以通过使用NOT NULL约束来指定列是否接受NULL值。接下来，我们来创建一张leads表，并且以此为依据来具体了解下：

1 2 3 4 5 6 7 8 CREATE TABLE leads ( id INT AUTO_INCREMENT PRIMARY KEY , first_name VARCHAR (50) NOT NULL , last_name VARCHAR (50) NOT NULL , source VARCHAR (255) NOT NULL , email VARCHAR (100), phone VARCHAR (25) );

我们可以看出来，id是主键列，它不接受任何NULL值，然后first_name，last_name和source列使用NOT NULL约束，因此，不能在这些列中插入任何NULL值，而email和phone列则可接受NULL值。

所以，我们可以在insert语句中使用NULL值来指定数据丢失。例如，以下语句将一行插入到线索表中。因为电话号码丢失，所以使用NULL值：

1 2	`INSERT` `INTO` `leads(first_name,last_name,source,email,phone)` `VALUE(` `'John'` `,` `'Doe'` `,` `'Web Search'` `,` `'john.doe@yiibai.com'` `,` `NULL` `);`

因为email列的默认值为NULL，可以按照以下方式在INSERT语句中省略电子邮件：

1 2 3 INSERT INTO leads(first_name,last_name,source,phone) VALUES ( 'Lily' , 'Bush' , 'Cold Calling' , '(408)-555-1234' ), ( 'David' , 'William' , 'Web Search' , '(408)-888-6789' );

完事如果我们要将列的值设置为NULL，可以使用赋值运算符(=)。例如，要将David William的手机(phone)更新为NULL，请使用以下UPDATE语句：

1 2 3 4 5 UPDATE leads SET phone = NULL WHERE id = 3;

但是如果使用order by子句按升序对结果集进行排序，则MySQL认为NULL值低于其他值，因此，它会首先显示NULL值。以下查询语句按照电话号码(phone)升序排列：

1 2 3 4 5 SELECT * FROM leads ORDER BY phone;

执行上面查询语句，结果如下：

如果使用ORDER BY DESC，NULL值将显示在结果集的最后：

1 2 3 4 5 SELECT * FROM leads ORDER BY phone DESC ;

执行上面查询语句，结果如下：

我们如果要在查询中测试NULL，可以在where子句中使用IS NULL或IS NOT NULL运算符。例如，要获得尚未提供电话号码的潜在客户，请使用IS NULL运算符，如下所示：

1 2 3 4 5 6 SELECT * FROM leads WHERE phone IS NULL ;

执行上面查询语句，结果如下：

我们还可以使用IS NOT运算符来获取所有提供电子邮件地址的潜在客户：

1 2 3 4 5 6 SELECT * FROM leads WHERE email IS NOT NULL ;

执行上面查询语句，结果如下：

然而，即使NULL不等于NULL，GROUP BY子句中视两个NULL值相等，来看下sql实例：

1 2 3 4 5 SELECT email, count (*) FROM leads GROUP BY email;

该查询只返回两行，因为其邮箱(email)列为NULL的行被分组为一行，结果如下所示：

1 2 3 4 5 6 7 + ---------------------+----------+ | email | count (*) | + ---------------------+----------+ | NULL | 2 | | john.doe@yiibai.com | 1 | + ---------------------+----------+ 2 rows in set

我们要知道在列上使用唯一约束或UNIQUE索引时，可以在该列中插入多个NULL值，在这种情况下，MySQL认为NULL值是不同的。接下来我们通过为phone列创建一个UNIQUE索引来验证这一点：

1	`CREATE` `UNIQUE` `INDEX` `idx_phone` `ON` `leads(phone);`

这里我们要注意，如果使用BDB存储引擎的话，mysql会认为NULL值相等，因此我们不能将多个NULL值插入到具有唯一约束的列中。

既然知道了null的好处和坏处，我们就来看下在mysql中应该如何处理它吧。mysql一共提供了三个函数，分别是IFNULL，COALESCE和NULLIF。

我们来分别看下，首先，IFNULL函数接受两个参数。如果IFNULL函数不为NULL，则返回第一个参数，否则返回第二个参数。例如，如果不是NULL，则以下语句返回电话号码(phone)，否则返回N/A，而不是NULL。来看个实例：

1 2 3 4 SELECT id, first_name, last_name, IFNULL(phone, 'N/A' ) phone FROM leads;

执行上面查询语句，得到以下结果：

1 2 3 4 5 6 7 8 + ----+------------+-----------+----------------+ | id | first_name | last_name | phone | + ----+------------+-----------+----------------+ | 1 | John | Doe | N/A | | 2 | Lily | Bush | (408)-555-1234 | | 3 | David | William | N/A | + ----+------------+-----------+----------------+ 3 rows in set

完事就是COALESCE函数，它接受参数列表，并返回第一个非NULL参数。例如，可以使用COALESCE函数根据信息的优先级按照以下顺序显示线索的联系信息：phone, email和N/A。以下是案例：

1 2 3 4 5 6 7 SELECT id, first_name, last_name, COALESCE (phone, email, 'N/A' ) contact FROM leads;

执行上面查询语句，得到以下代码：

1 2 3 4 5 6 7 8 + ----+------------+-----------+---------------------+ | id | first_name | last_name | contact | + ----+------------+-----------+---------------------+ | 1 | John | Doe | john.doe@yiibai.com | | 2 | Lily | Bush | (408)-555-1234 | | 3 | David | William | N/A | + ----+------------+-----------+---------------------+ 3 rows in set

最后就是NULLIF函数了，它接受两个参数。如果两个参数相等，则NULLIF函数返回NULL。否则，它返回第一个参数。在列中同时具有NULL和空字符串值时，NULLIF函数很有用。例如，我们错误地将以下行插入到leads表中：

1 2	`INSERT` `INTO` `leads(first_name,last_name,source,email,phone)` `VALUE(` `'Thierry'` `,` `'Henry'` `,` `'Web Search'` `,` `'thierry.henry@yiibai.com'` `,` `''` `);`

因为phone是一个空字符串:''，而不是NULL。所以，如果我们想获得潜在客户的联系信息，则最终得到空phone，而不是电子邮件，如下所示：

1 2 3 4 5 6 7 SELECT id, first_name, last_name, COALESCE (phone, email, 'N/A' ) contact FROM leads;

执行上面查询语句，得到以下代码：

1 2 3 4 5 6 7 8 + ----+------------+-----------+---------------------+ | id | first_name | last_name | contact | + ----+------------+-----------+---------------------+ | 1 | John | Doe | john.doe@yiibai.com | | 2 | Lily | Bush | (408)-555-1234 | | 3 | David | William | N/A | | 4 | Thierry | Henry | | + ----+------------+-----------+---------------------+

我们如果要解决这个问题，就要使用NULLIF函数将电话与空字符串('')进行比较，如果相等，则返回NULL，否则返回电话号码：

1 2 3 4 5 6 7 SELECT id, first_name, last_name, COALESCE ( NULLIF (phone, '' ), email, 'N/A' ) contact FROM leads;

执行上面查询语句，得到以下代码：

1 2 3 4 5 6 7 8 9 + ----+------------+-----------+--------------------------+ | id | first_name | last_name | contact | + ----+------------+-----------+--------------------------+ | 1 | John | Doe | john.doe@yiibai.com | | 2 | Lily | Bush | (408)-555-1234 | | 3 | David | William | N/A | | 4 | Thierry | Henry | thierry.henry@yiibai.com | + ----+------------+-----------+--------------------------+ 4 rows in set

好啦，本次记录就到这里了。

希望本文所述对大家MySQL数据库计有所帮助。

原文链接：https://blog.csdn.net/luyaran/article/details/80943613

可以去百度分享获取分享代码输入这里。

声明

1.本站遵循行业规范，任何转载的稿件都会明确标注作者和来源；2.本站的原创文章，请转载时务必注明文章作者和来源，不尊重原创的行为我们将追究责任；3.作者投稿可能会经我们编辑修改或补充。