比较两个具有相同结构的表格中数据差异的方法

Question

我正在处理一个典型的问题，需要比较两个具有完全相同模式的表格中的数据差异。假设数据库为MS SQL或ORACLE。

更加精确地说，这是我要实现的目标：

1. 我有一张包含一些数据的表格ORG。 2. 我正在创建表格ORG的备份副本BACKUP。 3. 现在我想要更新表格ORG中某些特定行的某些特定列。

何为最简单和有效的方法来查找表格BACKUP和ORG之间的差异？

我看到了一些选项，如使用UNIONS、PIVOT、UNPIVOT等等。但是我很困惑并需要一些指导以便选择最佳的方案。

谢谢。

- Krishna

---

你是想生成一个差异报告吗？还是要从源表更新目标表？而且你实际使用的是哪个数据库？SQL可能会不同，update绝对会不同。 - Justin Cave

如果表具有主键或唯一键约束条件，将会很有帮助。 - Rene

嗨，这个表没有任何主键。我尝试使用JOIN，它似乎涵盖了我感兴趣的大多数情况.. 这是我做的：SELECT count(*) FROM ORG x INNER JOIN BACKUP q on p.city = q.city - Krishna

如果表没有任何主键，那么您也应该关注重复行。 - Frank Ockenfuss

你的选择与 inner join 仅比较 city 列。其他列有何不同？如果有多个列被更新，你将如何加入？ - Frank Ockenfuss

Answer 1

根据你想要的内容，你可以使用SET运算符MINUS/INTERSECT，来获取两个表格之间的不同或匹配。

如果要获取不同之处，请使用MINUS：

SELECT <here_goes_your_column_list> FROM org
MINUS
SELECT <here_goes_your_column_list> FROM backup

要获取匹配项，请使用INTERSECT：

SELECT <here_goes_your_column_list> FROM org
INTERSECT
SELECT <here_goes_your_column_list> FROM backup

请参阅文档获取更多细节。

- Lalit Kumar B

---

谢谢。我尝试了join函数，似乎可以工作。如果需要进一步改进，我会研究您提供的选项。 - Krishna

@Krishna 没问题。如果这个答案有帮助，请标记为已回答，这也会对其他人有所帮助！ - Lalit Kumar B

我在MSSQL上尝试了这个解决方案，并且得到结论：Oracle中的Minus与MSSQL中的except相同。我需要具有不同数据的列，与上述相同的东西。这在MSSQL上可能吗？ - Varun Jain

Answer 2

如果问题是要检查两个表的身份，即答案为是 - 表包含相同的数据或否 - 存在差异，则有另一种方法，对于关系数据库来说不太常见，但对于文件来说是最先进的方法。

这与使用哈希代码检查已下载文件的“正确性”（即与原始文件的身份）相同。如果您看到相同的哈希代码，则答案是是，您拥有正确的文件。

如何将此方法转换为关系数据库？

Oracle提供了函数standard_hash，可为字符串计算MD5哈希代码。

 select standard_hash('foo bar', 'MD5') hash_code from dual; 

 HASH_CODE                      
 --------------------------------
 327B6F07435811239BC47E1544353273 

因此，您可以比较列值。 函数standard_hash也适用于CLOB，因此（理论上）您可以连接列和行并计算整个表的哈希码。但这不是正确的方法，要记住文件的哈希码是通过组合行的哈希码来逐步计算的。 以下是演示如何使用Java类java.security.MessageDigest计算MD5哈希码的方法。我正在使用Groovy脚本，因为在PL/SQL中不可能实现。

 MessageDigest digest = MessageDigest.getInstance("MD5") 
 byte[] md5hash 

 groovyCon.eachRow ('select txt from MY_TABLE order by id') 
    {
       digest.update(it.txt.getBytes(StandardCharsets.UTF_8))
    }   

 md5hash = digest.digest();
 println md5hash.encodeHex().toString()

该脚本初始化哈希码，然后遍历行并更新哈希码，最后将其设置为字符串。这是在处理文件时的典型方法，其中行的顺序很重要。在关系表中，顺序未定义。您认为具有（A，B，C）和（C，B，A）的表是相同的。

请参见此处讨论如何使用XOR以无序的方式组合哈希码。

以下是两个字符串哈希码组合的示例。

 select UTL_RAW.BIT_XOR(standard_hash('foo', 'MD5'), standard_hash('bar', 'MD5')) hash_code from dual;

 HASH_CODE                      
 --------------------------------
 9B0805C206B7EBB8B6B9931D83E9F52A 

这种方法有一个很大的优点，就是可以使用PL/SQL实现。在这里看一个实现PL/SQL 聚合函数 MD5_XOR 的例子，计算整个表的MD5哈希码。

 select   MD5_XOR(txt) hash_code, count(*) cnt 
 from MY_TABLE;

 HASH_CODE                          CNT
 --------------------------------   -------
 173F1F8F85F1A154044B7629A23E949C   102 

当然，您可以将参数连接起来以计算整个表的哈希码。

 select MD5_XOR(to_char(id)||COL_TXT|| to_char(COL_DATE,'dd.mm.yyyy hh24:mi:ss')) md5  from  MY_TABLE;

或者您可以使用GROUP BY将表的部分分组，以查看哪些组是相同的，哪些是不同的。

如果这个聚合函数被Oracle原生实现，性能将会非常好，比使用基于集合操作的SQL比较要好得多（需要对表进行排序）。XOR组合不需要排序，具有O(N)复杂度，但用户实现会受到上下文切换的影响。

请参见这里的想法，使其成为Oracle本地实现。

- Marmite Bomber

Answer 3

尝试这个。

-- compare tables data and data2
with data as
 (select 1 as id, 'A' as val
    from dual
  union
  select 2, 'B' from dual),
data2 as
 (select 1 as id, 'A' as val
    from dual
  union
  select 2, 'BC' from dual)

 -- data not in data2 and data2 not in data:
 (select *
    from data2
  minus
  select * from data) union all
 (select *
    from data
  minus
  select * from data2);

- Frank Ockenfuss

---

谢谢。我尝试了join，它似乎可以工作。如果需要进一步改进，我会探索您提供的选项。 - Krishna