首页 > 代码库 > NULL的陷阱:Merge

NULL的陷阱:Merge

    

NULL表示unknown,不确定值,所以任何值(包括null值)和NULL值比较都是不可知的,在on子句,where子句,Merge或case的when子句中,任何值和null比较的结果都是false,这就是NULL设下的陷阱,我被坑过。

有一次,我使用Merge同步数据,由于target表中存在null值,虽然在source表中对null值做过处理,但是忽略了target表中的null值,导致数据merge失败。

step1,创建示例数据

技术分享

--create source tablecreate table dbo.dt_source
(
id int null,
code int null)on [primary]with(data_compression=page)--create target tablecreate table dbo.dt_target
(
id int null,
code int null)on [primary]with(data_compression=page)

技术分享

step2,插入示例数据

示例数据中,Source表和Target表中都存在null值,不管是在Source表,还是在Target表,都要避免和null值进行比较。

技术分享

--insert data into tableinsert into dbo.dt_source(id,code)values(1,1),(2,2),(3,null)insert into dbo.dt_target(id,code)values(1,1),(2,null)

技术分享


step3,错误写法:只处理Source表中的null,而忽略Target表中的null

技术分享

-- -1 stand for unknwon valuemerge dbo.dt_target t
using dbo.dt_source s    on t.id=s.idwhen matched and( t.code<>isnull(s.code,-1))    then update
        set t.code=s.codewhen not matched    then insert(id,code)    values(s.id,s.code);

技术分享

查看Target和Srouce表中的数据,数据不同步,不同步的原因是when matched子句之后的and 条件, t.code中存在null值,null值和任何值(包括null值)比较的结果都是unknown,在when子句中视为false。

技术分享

正确写法1,不管是在target表,还是在source表,只要存在null值,必须进行处理,避免出现和null进行比较的情况。

处理的方式是使用一个值来表示unknwon,如果ID列有效值不可能是负值,那么可以使用-1来代替unknown。因为-1和-1 是相等的,逻辑上就将null值和null值视为相同。

技术分享

-- -1 stand for unknwon valuemerge dbo.dt_target t
using dbo.dt_source s    on t.id=s.idwhen matched and( isnull(t.code,-1)<>isnull(s.code,-1))    then update
        set t.code=s.codewhen not matched    then insert(id,code)    values(s.id,s.code);

技术分享

正确写法2,在条件子句中,使用is null或 is not null来处理null值。

Tsql 使用is null和is not null来确实是,不是 null。 null is null 的逻辑值是true,other_value is null 为false, other_value is not null 为true。

技术分享

merge dbo.dt_target t
using dbo.dt_source s    on t.id=s.idwhen matched and( t.code<>s.code or t.code is null or s.code is null)    then update
        set t.code=s.codewhen not matched    then insert(id,code)    values(s.id,s.code);

技术分享

 


NULL的陷阱:Merge