比较MySQL中两个表之间的差异 [英] compare differences between two tables in mysql

问题描述

与 oracle差异:如何比较两个表?(在mysql中除外)相同.

Same as oracle diff: how to compare two tables? except in mysql.

假设我有两个表t1和t2，它们的布局相同，但是可能包含不同的数据.

Suppose I have two tables, t1 and t2 which are identical in layout but which may contain different data.

区分这两个表的最佳方法是什么?

What's the best way to diff these two tables?

更准确地说，我试图找出一个简单的SQL查询，该查询告诉我t1中某一行的数据是否与t2中相应行的数据不同

To be more precise, I'm trying to figure out a simple SQL query that tells me if data from one row in t1 is different from the data from the corresponding row in t2

看来我不能使用相交也不能使用负号.当我尝试

It appears I cannot use the intersect nor minus. When I try

SELECT * FROM robot intersect SELECT * FROM tbd_robot

我得到一个错误代码:

[错误代码:1064，SQL状态:42000]您的SQL错误 句法;检查与您的MySQL服务器版本相对应的手册 在第1行的"SELECT * FROM tbd_robot"附近使用正确的语法

[Error Code: 1064, SQL State: 42000] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM tbd_robot' at line 1

我在语法上做错了吗?如果没有，还有其他查询可以使用吗?

Am I doing something syntactically wrong? If not, is there another query I can use?

另外，我正在通过免费版本DbVisualizer进行查询.不知道这可能是一个因素.

Also, I'm querying through a free version DbVisualizer. Not sure if that might be a factor.

推荐答案

INTERSECT需要在MySQL中进行仿真:

SELECT  'robot' AS `set`, r.*
FROM    robot r
WHERE   ROW(r.col1, r.col2, …) NOT IN
        (
        SELECT  col1, col2, ...
        FROM    tbd_robot
        )
UNION ALL
SELECT  'tbd_robot' AS `set`, t.*
FROM    tbd_robot t
WHERE   ROW(t.col1, t.col2, …) NOT IN
        (
        SELECT  col1, col2, ...
        FROM    robot
        )

这篇关于比较MySQL中两个表之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！