MySQL中对比两张表是否有不同数据

54 阅读 0 评论 36 点赞

我是靠谱客的博主温婉发夹，最近开发中收集的这篇文章主要介绍MySQL中对比两张表是否有不同数据，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

1.使用union all关联两张表，完事使用临时表或者说派生表的方式来进行数据对比。

SELECT t1.pk, t1.c1
FROM t1
UNION ALL
SELECT t2.pk, t2.c1
FROM t2

使用派生表的方式来对比数据
例如：

SELECT id,title
FROM (
    SELECT id, title FROM t1
    UNION ALL
    SELECT id,title FROM t2
) tbl
GROUP BY id, title
HAVING count(*) = 1
ORDER BY id;

今天在公司，领导让我把两个数据库中的5张表进行对比，找出不同的地方，记录出来

我的sql语句：

select * from
( select * from chengle_30.config_standard_quality_control_item
union all
select * from chengle_41.config_standard_quality_control_item
)tb1
GROUP BY id,defect_id,defect_name,defect_desc,type_id,is_done,update_time,is_loss_doc,is_invalid,implement_mod,status
HAVING count(*) = 1
ORDER BY id;

select * from
( select * from chengle_30.config_standard_return
union all
select * from chengle_41.config_standard_return
)tb1
GROUP BY id,return_code,return_value,is_done,standard_defect_no
HAVING count(*) = 1
ORDER BY id;

select * from
( select * from chengle_30.instrument_to_standard_control_item
union all
select * from chengle_41.instrument_to_standard_control_item
)tb1
GROUP BY id,standard_doc_path,doc_type,standard_doc_name,standard_defect_no
HAVING count(*) = 1
ORDER BY id;

select * from
( select * from chengle_30.standard_defect_classification
union all
select * from chengle_41.standard_defect_classification
)tb1
GROUP BY id,classification_name,sort,parent_id,is_parent
HAVING count(*) = 1
ORDER BY id;

select * from
( select * from chengle_30.standard_not_real_doc
union all
select * from chengle_41.standard_not_real_doc
)tb1
GROUP BY id,emr_type,standard_doc_name,option_time
HAVING count(*) = 1
ORDER BY id;