欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

mysql中null(IFNULL,COALESCE和NULLIF)相关知识点总结

程序员文章站 2022-05-24 15:01:26
本文实例讲述了mysql中null(ifnull,coalesce和nullif)相关知识点。分享给大家供大家参考,具体如下: 在mysql中,null值表示一个未知值,它不同于0或...

本文实例讲述了mysql中null(ifnull,coalesce和nullif)相关知识点。分享给大家供大家参考,具体如下:

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

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

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

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值:

insert into leads(first_name,last_name,source,email,phone)
value('john','doe','web search','john.doe@yiibai.com',null);

因为email列的默认值为null,可以按照以下方式在insert语句中省略电子邮件:

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语句:

update leads 
set 
  phone = null
where
  id = 3;

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

select 
  *
from
  leads
order by phone;

执行上面查询语句,结果如下:

+----+------------+-----------+--------------+---------------------+----------------+
| id | first_name | last_name | source    | email        | phone     |
+----+------------+-----------+--------------+---------------------+----------------+
| 1 | john    | doe    | web search  | john.doe@yiibai.com | null      |
| 3 | david   | william  | web search  | null        | null      |
| 2 | lily    | bush   | cold calling | null        | (408)-555-1234 |
+----+------------+-----------+--------------+---------------------+----------------+

如果使用order by desc,null值将显示在结果集的最后:

select 
  *
from
  leads
order by phone desc;

执行上面查询语句,结果如下:

+----+------------+-----------+--------------+---------------------+----------------+
| id | first_name | last_name | source    | email        | phone     |
+----+------------+-----------+--------------+---------------------+----------------+
| 2 | lily    | bush   | cold calling | null        | (408)-555-1234 |
| 1 | john    | doe    | web search  | john.doe@yiibai.com | null      |
| 3 | david   | william  | web search  | null        | null      |
+----+------------+-----------+--------------+---------------------+----------------+
3 rows in set

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

select 
  *
from
  leads
where
  phone is null;

执行上面查询语句,结果如下:

+----+------------+-----------+------------+---------------------+-------+
| id | first_name | last_name | source   | email        | phone |
+----+------------+-----------+------------+---------------------+-------+
| 1 | john    | doe    | web search | john.doe@yiibai.com | null |
| 3 | david   | william  | web search | null        | null |
+----+------------+-----------+------------+---------------------+-------+
2 rows in set

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

select 
  *
from
  leads
where
  email is not null;

执行上面查询语句,结果如下:

+----+------------+-----------+------------+---------------------+-------+
| id | first_name | last_name | source   | email        | phone |
+----+------------+-----------+------------+---------------------+-------+
| 1 | john    | doe    | web search | john.doe@yiibai.com | null |
+----+------------+-----------+------------+---------------------+-------+
1 row in set

然而,即使null不等于null,group by子句中视两个null值相等,来看下sql实例:

select 
  email, count(*)
from
  leads
group by email;

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

+---------------------+----------+
| email        | count(*) |
+---------------------+----------+
| null        |    2 |
| john.doe@yiibai.com |    1 |
+---------------------+----------+
2 rows in set

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

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。来看个实例:

select 
  id, first_name, last_name, ifnull(phone, 'n/a') phone
from
  leads;

执行上面查询语句,得到以下结果:

+----+------------+-----------+----------------+
| 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。以下是案例:

select 
  id,
  first_name,
  last_name,
  coalesce(phone, email, 'n/a') contact
from
  leads;

执行上面查询语句,得到以下代码:

+----+------------+-----------+---------------------+
| 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表中:

insert into leads(first_name,last_name,source,email,phone)
value('thierry','henry','web search','thierry.henry@yiibai.com','');

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

select 
  id,
  first_name,
  last_name,
  coalesce(phone, email, 'n/a') contact
from
  leads;

执行上面查询语句,得到以下代码:

+----+------------+-----------+---------------------+
| 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,否则返回电话号码:

select 
  id,
  first_name,
  last_name,
  coalesce(nullif(phone, ''), email, 'n/a') contact
from
  leads;

执行上面查询语句,得到以下代码:

+----+------------+-----------+--------------------------+
| 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

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