mysql中null（IFNULL，COALESCE和NULLIF）相关知识点总结
本文实例讲述了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
好啦，本次记录就到这里了。