Node.js Windows服务中MERGE语句NULL值不更新问题排查与解决

问题背景 #

我在Node.js Windows服务中需要合并多个数据库表，于是写了一个生成MERGE SQL的方法createSQLString，代码如下：

function createSQLString(targetTable, sourceTable, columns){ 
  let sql = "MERGE " + targetTable + " AS TARGET USING " + sourceTable + " AS SOURCE \n";
  switch (sourceTable) { 
    //.....some other cases..... 
    case 'communications': 
      sql += "ON (" + " TARGET.[id] = SOURCE.[id]" + " AND TARGET.[business_partner_id] = SOURCE.[business_partner_id]" + " AND TARGET.[fiscalYear] = SOURCE.[fiscalYear]" + ") \n";
      break; 
    default: // other stuff 
  } 
  // if row was found and values are not equal 
  sql += "WHEN MATCHED AND ("; 
  columns.forEach(column => { 
    sql += "SOURCE.[" + column.COLUMN_NAME + "] <> TARGET.[" + column.COLUMN_NAME + "] OR "; 
  }) 
  sql = sql.substring(0, sql.length -4) + "\n"; 
  sql += ") THEN UPDATE SET "; 
  columns.forEach(column => { 
    sql += "TARGET.[" + column.COLUMN_NAME + "] = SOURCE.[" + column.COLUMN_NAME + "],";
  }) 
  sql = sql.substring(0, sql.length -1) + "\n"; 
  // if no match in target insert 
  sql += "WHEN NOT MATCHED BY TARGET THEN INSERT (";
  columns.forEach(column => { 
    sql += "[" + column.COLUMN_NAME + "], "; 
  }) 
  sql = sql.substring(0, sql.length -2) + "\n"; 
  sql += ") VALUES ("; 
  columns.forEach(column => { 
    sql += "SOURCE.[" + column.COLUMN_NAME + "], "; 
  }) 
  sql = sql.substring(0, sql.length -2) + "\n"; 
  sql += ")\n"; 
  // if there is a record in target but not in source 
  sql += "WHEN NOT MATCHED BY SOURCE \n THEN DELETE;"; 
  return sql; 
}

这个方法生成的SQL大部分情况运行正常，但遇到源表列值为NULL时，目标表不会被更新。生成的示例SQL如下：

MERGE communication AS TARGET USING communication_cache AS SOURCE 
ON ( TARGET.[id] = SOURCE.[id] AND TARGET.[fiscalYear] = SOURCE.[fiscalYear] AND TARGET.[clientId] = SOURCE.[clientId] ) 
WHEN MATCHED AND ( SOURCE.[id] <> TARGET.[id] OR SOURCE.[address_type] <> TARGET.[address_type] OR SOURCE.[is_correspondence_address] <> TARGET.[is_correspondence_address] OR SOURCE.[is_main_post_office_box_address] <> TARGET.[is_main_post_office_box_address] OR SOURCE.[is_main_street_address] <> TARGET.[is_main_street_address] OR SOURCE.[is_management_address] <> TARGET.[is_management_address] OR SOURCE.[business_partner_id] <> TARGET.[business_partner_id] OR SOURCE.[fiscalYear] <> TARGET.[fiscalYear] ) 
THEN UPDATE SET TARGET.[id] = SOURCE.[id], TARGET.[address_type] = SOURCE.[address_type], TARGET.[is_correspondence_address] = SOURCE.[is_correspondence_address], TARGET.[is_main_post_office_box_address] = SOURCE.[is_main_post_office_box_address], TARGET.[is_main_street_address] = SOURCE.[is_main_street_address], TARGET.[is_management_address] = SOURCE.[is_management_address], TARGET.[business_partner_id] = SOURCE.[business_partner_id], TARGET.[fiscalYear] = SOURCE.[fiscalYear] 
WHEN NOT MATCHED BY TARGET THEN INSERT ( [id], [address_type], [is_correspondence_address], [is_main_post_office_box_address], [is_main_street_address], [is_management_address], [business_partner_id], [fiscalYear] ) 
VALUES (SOURCE.[id], SOURCE.[address_type], SOURCE.[is_correspondence_address], SOURCE.[is_main_post_office_box_address], SOURCE.[is_main_street_address], SOURCE.[is_management_address], SOURCE.[business_partner_id], SOURCE.[fiscalYear] ) 
WHEN NOT MATCHED BY SOURCE THEN DELETE;

问题原因 #

这是SQL中处理NULL值的核心特性导致的：在SQL逻辑里，NULL代表“未知值”，所以任何与NULL的比较（包括<>, =, >等运算符）结果都是UNKNOWN，而非TRUE或FALSE。

你的WHEN MATCHED AND (...)条件中，当源表某列是NULL时，SOURCE.[column] <> TARGET.[column]会返回UNKNOWN，而SQL逻辑判断中UNKNOWN会被当作FALSE处理。这就导致即使其他列存在差异，只要有一个列的比较返回UNKNOWN，整个触发更新的条件就不成立，最终目标表不会执行更新。

举个实际场景：如果SOURCE.[address_type]是NULL，而TARGET.[address_type]是'HOME'，那么SOURCE.[address_type] <> TARGET.[address_type]的结果是UNKNOWN，这个OR子句不会被判定为TRUE；如果其他列的比较结果都是FALSE，整个条件就无法满足，更新操作也就不会触发。

解决方案 #

你需要修改生成列比较逻辑的代码，针对性处理NULL值的情况。常用的两种可行方案如下：

方案1：明确判断NULL的三种差异场景 #

对于每个列，比较逻辑需要覆盖三种值不相等的情况：

• 源和目标的值都不为NULL且不相等
• 源是NULL但目标不是
• 目标是NULL但源不是

修改columns.forEach生成比较条件的代码段：

columns.forEach(column => { 
  const colName = column.COLUMN_NAME;
  sql += `(SOURCE.[${colName}] <> TARGET.[${colName}] OR SOURCE.[${colName}] IS NULL AND TARGET.[${colName}] IS NOT NULL OR SOURCE.[${colName}] IS NOT NULL AND TARGET.[${colName}] IS NULL) OR `; 
}) 

方案2：使用COALESCE统一NULL的比较逻辑 #

如果你的业务数据中不会出现某个特定的标记值（比如'<<NULL_PLACEHOLDER>>'），可以用COALESCE函数将NULL替换为这个标记值，这样就能用普通的不等运算符完成比较：

columns.forEach(column => { 
  const colName = column.COLUMN_NAME;
  // 选择一个业务中绝不会出现的字符串作为占位符
  sql += `COALESCE(SOURCE.[${colName}], '<<NULL_PLACEHOLDER>>') <> COALESCE(TARGET.[${colName}], '<<NULL_PLACEHOLDER>>') OR `; 
}) 

修改后的完整示例代码 #

这里基于方案1修改你的createSQLString方法：

function createSQLString(targetTable, sourceTable, columns){ 
  let sql = "MERGE " + targetTable + " AS TARGET USING " + sourceTable + " AS SOURCE \n";
  switch (sourceTable) { 
    //.....some other cases..... 
    case 'communications': 
      sql += "ON (" + " TARGET.[id] = SOURCE.[id]" + " AND TARGET.[business_partner_id] = SOURCE.[business_partner_id]" + " AND TARGET.[fiscalYear] = SOURCE.[fiscalYear]" + ") \n";
      break; 
    default: // other stuff 
  } 
  // if row was found and values are not equal 
  sql += "WHEN MATCHED AND ("; 
  columns.forEach(column => { 
    const colName = column.COLUMN_NAME;
    sql += `(SOURCE.[${colName}] <> TARGET.[${colName}] OR SOURCE.[${colName}] IS NULL AND TARGET.[${colName}] IS NOT NULL OR SOURCE.[${colName}] IS NOT NULL AND TARGET.[${colName}] IS NULL) OR `; 
  }) 
  sql = sql.substring(0, sql.length -4) + "\n"; 
  sql += ") THEN UPDATE SET "; 
  columns.forEach(column => { 
    sql += "TARGET.[" + column.COLUMN_NAME + "] = SOURCE.[" + column.COLUMN_NAME + "],";
  }) 
  sql = sql.substring(0, sql.length -1) + "\n"; 
  // if no match in target insert 
  sql += "WHEN NOT MATCHED BY TARGET THEN INSERT (";
  columns.forEach(column => { 
    sql += "[" + column.COLUMN_NAME + "], "; 
  }) 
  sql = sql.substring(0, sql.length -2) + "\n"; 
  sql += ") VALUES ("; 
  columns.forEach(column => { 
    sql += "SOURCE.[" + column.COLUMN_NAME + "], "; 
  }) 
  sql = sql.substring(0, sql.length -2) + "\n"; 
  sql += ")\n"; 
  // if there is a record in target but not in source 
  sql += "WHEN NOT MATCHED BY SOURCE \n THEN DELETE;"; 
  return sql; 
}

修改后生成的SQL就能正确处理源表列值为NULL的场景，只要源和目标列的值存在差异（包括NULL和非NULL的差异），就会触发更新操作。

内容的提问来源于stack exchange，提问作者Tobias