如何获取两个近似重复SQL表之间的差异

一、绕过主键约束冲突的临时方案 #

如果坚持使用合并到第三张表的思路，可以按以下方式处理：

• 移除中间表的主键/唯一约束：创建第三张表时，仅复制原表的字段结构，不要添加主键或唯一键约束，额外增加一个来源标记字段（比如source_table，取值为'table1'或'table2'），这样导入数据时就不会触发约束冲突。
• 导入后统计差异：全量导入两张表的数据后，通过主键字段分组统计：

SELECT 
    主键字段, 
    COUNT(*) AS record_count, 
    GROUP_CONCAT(source_table) AS source_list
FROM 中间表
GROUP BY 主键字段
HAVING record_count != 2 OR source_list NOT LIKE '%table1%table2%';

通过结果可以快速定位三类差异：仅存在于表1的记录（record_count=1且source_list='table1'）、仅存在于表2的记录（record_count=1且source_list='table2'）、主键相同但字段值有变化的记录（record_count=2但实际字段内容不一致，需额外对比字段）。

二、更高效的直接对比方案（无需中间表） #

不需要创建中间表，直接用SQL的JOIN或集合操作就能完成对比，效率更高：

1. 找出仅在单张表中存在的记录 #

-- 仅表1存在的记录
SELECT '仅表1存在' AS diff_type, t1.*
FROM table1 t1
LEFT JOIN table2 t2 ON t1.主键字段 = t2.主键字段
WHERE t2.主键字段 IS NULL

UNION ALL

-- 仅表2存在的记录
SELECT '仅表2存在' AS diff_type, t2.*
FROM table2 t2
LEFT JOIN table1 t1 ON t2.主键字段 = t1.主键字段
WHERE t1.主键字段 IS NULL;

2. 找出主键相同但字段值有差异的记录 #

假设表字段为id, col1, col2, col3，可以逐个字段对比：

SELECT 
    '字段差异' AS diff_type,
    t1.id,
    CASE WHEN t1.col1 IS NOT DISTINCT FROM t2.col1 THEN NULL ELSE CONCAT('表1:', t1.col1, ' | 表2:', t2.col1) END AS col1_diff,
    CASE WHEN t1.col2 IS NOT DISTINCT FROM t2.col2 THEN NULL ELSE CONCAT('表1:', t1.col2, ' | 表2:', t2.col2) END AS col2_diff,
    CASE WHEN t1.col3 IS NOT DISTINCT FROM t2.col3 THEN NULL ELSE CONCAT('表1:', t1.col3, ' | 表2:', t2.col3) END AS col3_diff
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id
WHERE 
    NOT (t1.col1 IS NOT DISTINCT FROM t2.col1 
         AND t1.col2 IS NOT DISTINCT FROM t2.col2 
         AND t1.col3 IS NOT DISTINCT FROM t2.col3);

如果字段数量较多，可通过哈希函数简化对比（注意：哈希值相同不代表绝对一致，需验证极端情况）：

SELECT 
    '字段差异' AS diff_type,
    t1.id
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id
WHERE MD5(CONCAT_WS('|', t1.col1, t1.col2, t1.col3)) != MD5(CONCAT_WS('|', t2.col1, t2.col2, t2.col3));

3. 用EXCEPT/INTERSECT（适用于PostgreSQL、SQL Server等支持该语法的数据库） #

-- 表1有但表2没有的记录
SELECT * FROM table1
EXCEPT
SELECT * FROM table2;

-- 表2有但表1没有的记录
SELECT * FROM table2
EXCEPT
SELECT * FROM table1;

三、注意事项 #

• 若表数据量极大，直接JOIN可能性能不足，建议按主键范围分批对比，或确保主键字段已建立索引。
• 处理NULL值时，不要直接用!=判断，需用IS NOT DISTINCT FROM（部分数据库支持）或(t1.col IS NULL AND t2.col IS NOT NULL) OR (t1.col IS NOT NULL AND t2.col IS NULL) OR t1.col != t2.col逻辑。

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