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

r囧r小猫 2022-10-12 00:42 136阅读 0赞

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjQ2MDg0Mw_size_16_color_FFFFFF_t_70]

# 一、需求 #

开发中，我们通常要写一个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；

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjQ2MDg0Mw_size_16_color_FFFFFF_t_70]: /images/20221005/62bd067a4e2d4832b8dc430d039bf348.png