高效mysql 两张表数据比较差异

-- 找出表A中有而表B中没有的记录
SELECT * FROM tableA
EXCEPT
SELECT * FROM tableB;

-- 找出表B中有而表A中没有的记录
SELECT * FROM tableB
EXCEPT
SELECT * FROM tableA;

-- 找出表A和表B中不一致的记录
SELECT A.*
FROM tableA A
JOIN tableB B ON A.id = B.id
WHERE A.column1 <> B.column1 OR A.column2 <> B.column2;

-- 找出表A中有而表B中没有的记录
SELECT * FROM tableA A
WHERE NOT EXISTS (SELECT 1 FROM tableB B WHERE A.id = B.id);

-- 找出表B中有而表A中没有的记录
SELECT * FROM tableB B
WHERE NOT EXISTS (SELECT 1 FROM tableA A WHERE B.id = A.id);

-- 计算每条记录的哈希值并比较
SELECT A.id, MD5(CONCAT(A.column1, A.column2)) AS hashA,
       B.id, MD5(CONCAT(B.column1, B.column2)) AS hashB
FROM tableA A
JOIN tableB B ON A.id = B.id
WHERE MD5(CONCAT(A.column1, A.column2)) <> MD5(CONCAT(B.column1, B.column2));