NULLIF()和COALESCE()如此强大！轻松比较空值（null）或者比较空字符串(‘‘)

一、需求

开发中，我们通常要写一个sql来完成修改或者插入，比如：比较表里的几个业务主键的字段和传入的值是否相同，如果相等就修改，否则就插入。
当表中字段为null，并且传入的值也为null时，我们就要考虑null和null的比较了，或者空字符串和空字符串的比较。

空字符串是单引号的，它其实是不占空间的。
MySQL 中 null 其实是占空间的，官网文档里有说明。

二、NULLIF ()函数

2.1语法

NULLIF ( expression , expression )

2.2解释

如果两个指定的表达式相等，则返回空值。如果两个表达式不相等，则 NULLIF 返回第一个表达式。

2.3举例

NULLIF(4,4) //因为4=4，所以返回空值；
NULLIF(5,7)//因为5!=7,所以返回5；
NULLIF(’’,’’)//因为’’=’’,所以返回空值；
注： 在实际情况中，我们需要注意的是比较的值是null或是’’。

三、COALESCE()函数

3.1语法

COALESCE ( expression1, expression2, … expression-n )

3.2解释

可以包含n个表达式，表示如果第一个不为空取第一个，否则判断下一个，以此类推，如果全部为空，则返回null值。

3.3举例

COALESCE (null, 1.1 )//因为第一个值为null，所以返回1.1；
COALESCE (null, ‘1.1’)//因为第一个值为null，所以返回’1.1’；
COALESCE (90, 5 )//因为第一个值不为空，所以返回90。
注： 在实际情况中，我们需要注意的是当前字段的类型是什么，比如字符串，就返回’’。

四、联合使用

4.1比较空字符串

SELECT name,name_id,age FROM person p WHERRE COALESCE (NULLIF(p.nameId,’’), ‘1.2’ )= COALESCE (NULLIF(#{NAME_ID},’’), ‘1.2’ ) AND p.name =#{NAME};
解析：
1.person表中name_id存在为空字符串(’’)的情况，类型且为字符串；
2.传入的#{NAME_ID}也可能存在为空字符串(’’)的情况；
3.当两个比较的值都为空字符串时，就先将两个值变成’1.2’,最后再比较；
4.NULLIF(p.nameId,’’),因为’’=’’,所以返回null，COALESCE(null,‘1.2’)，所以返回’1.2’。

4.2比较NULL

SELECT name,name_id,age FROM person p WHERRE COALESCE (NULLIF(p.nameId,null), 1 )= COALESCE (NULLIF(#{NAME_ID},null), 1 ) AND p.name =#{NAME};
解析：
1.person表中name_id存在为null的情况；
2.传入的#{NAME_ID}也可能存在为null的情况；
3.当两个比较的值都为null时，就先将两个值变成1后再比较；
4.因为NULLIF(null,null),所以返回null；因为COALESCE(null,1)，所以返回1；