mysql中null(IFNULL,COALESCE和NULLIF)相关知识点总结
本文实例讲述了mysql中null(IFNULL,COALESCE和NULLIF)相关知识点。分享给大家供大家参考,具体如下:
在MySQL中,NULL值表示一个未知值,它不同于0或空字符串'',并且不等于它自身。
我们如果将NULL值与另一个NULL值或任何其他值进行比较,则结果为NULL,因为一个不知道是什么的值(NULL值)与另一个不知道是什么的值(NULL值)比较,其值当然也是一个不知道是什么的值(NULL值)。
然而我们通常,使用NULL值来表示数据丢失,未知或不适用的情况。例如,潜在客户的电话号码可能为NULL,并且可以稍后添加。所以我们创建表时,可以通过使用NOTNULL约束来指定列是否接受NULL值。接下来,我们来创建一张leads表,并且以此为依据来具体了解下:
CREATETABLEleads( idINTAUTO_INCREMENTPRIMARYKEY, first_nameVARCHAR(50)NOTNULL, last_nameVARCHAR(50)NOTNULL, sourceVARCHAR(255)NOTNULL, emailVARCHAR(100), phoneVARCHAR(25) );
我们可以看出来,id是主键列,它不接受任何NULL值,然后first_name,last_name和source列使用NOTNULL约束,因此,不能在这些列中插入任何NULL值,而email和phone列则可接受NULL值。
所以,我们可以在insert语句中使用NULL值来指定数据丢失。例如,以下语句将一行插入到线索表中。因为电话号码丢失,所以使用NULL值:
INSERTINTOleads(first_name,last_name,source,email,phone) VALUE('John','Doe','WebSearch','john.doe@yiibai.com',NULL);
因为email列的默认值为NULL,可以按照以下方式在INSERT语句中省略电子邮件:
INSERTINTOleads(first_name,last_name,source,phone) VALUES('Lily','Bush','ColdCalling','(408)-555-1234'), ('David','William','WebSearch','(408)-888-6789');
完事如果我们要将列的值设置为NULL,可以使用赋值运算符(=)。例如,要将DavidWilliam的手机(phone)更新为NULL,请使用以下UPDATE语句:
UPDATEleads SET phone=NULL WHERE id=3;
但是如果使用orderby子句按升序对结果集进行排序,则MySQL认为NULL值低于其他值,因此,它会首先显示NULL值。以下查询语句按照电话号码(phone)升序排列:
SELECT * FROM leads ORDERBYphone;
执行上面查询语句,结果如下:
+----+------------+-----------+--------------+---------------------+----------------+ |id|first_name|last_name|source|email|phone| +----+------------+-----------+--------------+---------------------+----------------+ |1|John|Doe|WebSearch|john.doe@yiibai.com|NULL| |3|David|William|WebSearch|NULL|NULL| |2|Lily|Bush|ColdCalling|NULL|(408)-555-1234| +----+------------+-----------+--------------+---------------------+----------------+
如果使用ORDERBYDESC,NULL值将显示在结果集的最后:
SELECT * FROM leads ORDERBYphoneDESC;
执行上面查询语句,结果如下:
+----+------------+-----------+--------------+---------------------+----------------+ |id|first_name|last_name|source|email|phone| +----+------------+-----------+--------------+---------------------+----------------+ |2|Lily|Bush|ColdCalling|NULL|(408)-555-1234| |1|John|Doe|WebSearch|john.doe@yiibai.com|NULL| |3|David|William|WebSearch|NULL|NULL| +----+------------+-----------+--------------+---------------------+----------------+ 3rowsinset
我们如果要在查询中测试NULL,可以在where子句中使用ISNULL或ISNOTNULL运算符。例如,要获得尚未提供电话号码的潜在客户,请使用ISNULL运算符,如下所示:
SELECT * FROM leads WHERE phoneISNULL;
执行上面查询语句,结果如下:
+----+------------+-----------+------------+---------------------+-------+ |id|first_name|last_name|source|email|phone| +----+------------+-----------+------------+---------------------+-------+ |1|John|Doe|WebSearch|john.doe@yiibai.com|NULL| |3|David|William|WebSearch|NULL|NULL| +----+------------+-----------+------------+---------------------+-------+ 2rowsinset
我们还可以使用ISNOT运算符来获取所有提供电子邮件地址的潜在客户:
SELECT * FROM leads WHERE emailISNOTNULL;
执行上面查询语句,结果如下:
+----+------------+-----------+------------+---------------------+-------+ |id|first_name|last_name|source|email|phone| +----+------------+-----------+------------+---------------------+-------+ |1|John|Doe|WebSearch|john.doe@yiibai.com|NULL| +----+------------+-----------+------------+---------------------+-------+ 1rowinset
然而,即使NULL不等于NULL,GROUPBY子句中视两个NULL值相等,来看下sql实例:
SELECT email,count(*) FROM leads GROUPBYemail;
该查询只返回两行,因为其邮箱(email)列为NULL的行被分组为一行,结果如下所示:
+---------------------+----------+ |email|count(*)| +---------------------+----------+ |NULL|2| |john.doe@yiibai.com|1| +---------------------+----------+ 2rowsinset
我们要知道在列上使用唯一约束或UNIQUE索引时,可以在该列中插入多个NULL值,在这种情况下,MySQL认为NULL值是不同的。接下来我们通过为phone列创建一个UNIQUE索引来验证这一点:
CREATEUNIQUEINDEXidx_phoneONleads(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| +----+------------+-----------+----------------+ 3rowsinset
完事就是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| +----+------------+-----------+---------------------+ 3rowsinset
最后就是NULLIF函数了,它接受两个参数。如果两个参数相等,则NULLIF函数返回NULL。否则,它返回第一个参数。在列中同时具有NULL和空字符串值时,NULLIF函数很有用。例如,我们错误地将以下行插入到leads表中:
INSERTINTOleads(first_name,last_name,source,email,phone) VALUE('Thierry','Henry','WebSearch','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| +----+------------+-----------+--------------------------+ 4rowsinset
好啦,本次记录就到这里了。
更多关于MySQL相关内容感兴趣的读者可查看本站专题:《MySQL查询技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》、《MySQL数据库锁相关技巧汇总》及《MySQL常用函数大汇总》
希望本文所述对大家MySQL数据库计有所帮助。
声明:本文内容来源于网络,版权归原作者所有,内容由互联网用户自发贡献自行上传,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任。如果您发现有涉嫌版权的内容,欢迎发送邮件至:czq8825#qq.com(发邮件时,请将#更换为@)进行举报,并提供相关证据,一经查实,本站将立刻删除涉嫌侵权内容。