跨表数据校验：如何高效检测两表间的数据变更

假设id是两张表的主键，以下是几种实用高效的检测方案：

1. 主键关联+字段直接对比 #

这是最直观的方式，通过主键关联两张表，逐个校验目标字段的差异：

SELECT
    COALESCE(a.id, b.id) AS id,
    CASE
        WHEN a.id IS NULL THEN '新增'
        WHEN b.id IS NULL THEN '删除'
        ELSE '修改'
    END AS change_type,
    a.name AS old_name, b.name AS new_name,
    a.age AS old_age, b.age AS new_age
FROM Table_a a
FULL OUTER JOIN Table_b b ON a.id = b.id
WHERE
    a.id IS NULL -- 表b存在但表a不存在的新增行
    OR b.id IS NULL -- 表a存在但表b不存在的删除行
    OR a.name <> b.name -- name字段变更
    OR a.age <> b.age; -- age字段变更

针对你的示例数据，这个查询会返回id=222的修改记录，以及id=333的新增记录。如果只关注已存在行的字段修改，可以替换为INNER JOIN并去掉新增/删除的判断条件。

2. 行哈希值对比 #

如果表的字段较多，逐个对比过于繁琐，可以对每行的目标字段计算哈希值，通过哈希差异判断行数据是否变更：

WITH Hash_a AS (
    SELECT id, MD5(CONCAT(COALESCE(name, ''), '|', COALESCE(age::TEXT, ''))) AS row_hash 
    FROM Table_a
),
Hash_b AS (
    SELECT id, MD5(CONCAT(COALESCE(name, ''), '|', COALESCE(age::TEXT, ''))) AS row_hash 
    FROM Table_b
)
SELECT
    COALESCE(ha.id, hb.id) AS id,
    CASE
        WHEN ha.id IS NULL THEN '新增'
        WHEN hb.id IS NULL THEN '删除'
        ELSE '修改'
    END AS change_type
FROM Hash_a ha
FULL OUTER JOIN Hash_b hb ON ha.id = hb.id
WHERE ha.row_hash <> hb.row_hash OR ha.id IS NULL OR hb.id IS NULL;

注意用COALESCE处理NULL值，避免哈希计算出错。如果数据量较大，还可以将哈希值预存在表中（插入/更新时自动计算），进一步提升查询效率。

3. 集合差集对比（适用于支持的数据库） #

PostgreSQL、SQL Server等数据库支持EXCEPT/INTERSECT语法，可以直接对比两张表的行集合：

-- 找出表a有但表b没有的行（删除/修改前的状态）
SELECT id, name, age FROM Table_a
EXCEPT
SELECT id, name, age FROM Table_b
UNION ALL
-- 找出表b有但表a没有的行（新增/修改后的状态）
SELECT id, name, age FROM Table_b
EXCEPT
SELECT id, name, age FROM Table_a;

示例中这个查询会返回id=222的新旧两行数据，以及id=333的新增行。

性能优化要点 #

• 确保id字段有主键或唯一索引，加速关联查询
• 大数据量表可以分批次对比，或基于update_time等字段只校验最近更新的行
• 哈希对比场景下，预存哈希值能避免重复计算的开销

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