SQL NULL值比较陷阱
起因:在与其他系统对接时,他们要下发数据到table表,下发逻辑为如果A字段的值不是“XXX”和“YYY”就不会下发这条数据,因为之前是没有下发A字段,所以A字段的值一直都是NULL,但是查了数据库还存在"“的,也就是A字段的值现在是存在NULL、”"、“XXX”、“YYY"四种情况,下端的业务逻辑根据下发的字段值要进行部分调整,如果A的值不是"XXX"和"YYY"就不能进行保存,所以在后端要进行逻辑判断,查询当前工单A字段的值是否为"XXX"和"YYY”,
笔者的做法是:
SELECT 1 FROM table WHERE A <> 'XXX' AND A <> 'YYY'
如果查到数据为空就正常保存,如果查出来有数据就就进行报错之后回滚。
但是明明为A字段为NULL 的数据在保存的时候正常保存了,于是对SQL单独执行之后发现了问题,通过百度大法得知这个现象学名是NULL值陷阱,如果字段值W为空字符串就不会出现此问题,要规避此问题最简单的就是加上<>NULL的判断了,于是把SQL改成
SELECT 1 FROM table WHERE (A IS NOT NULL AND A <> 'XXX' AND A <> 'YYY') OR A IS NULL
当然这个是最简单粗暴的方法,看到有其他网友的解决方法还存在
SELECT * FROM some_table WHERE nvl(field1, null_if_value)=nvl(field2, null_if_value)
NVL函数是Oracle的一个空值转换函数,相当于mysql的IFNULL
NVL(表达式1,表达式2)
如果表达式1为空值,NVL返回值为表达式2的值,否则返回表达式1的值。
原理就是用一个控制转换函数把NULL转换成别的字符串来和目标值进行比较
NVL()是oracle数据库中的函数
IFNULL()是mysql数据库中的函数
mysql版本就是
SELECT 1 FROM table WHERE IFNULL(A,'') <>'XXX' AND IFNULL(A,'') <> 'YYY'
希望大家在开发中尽量规避这些问题