同 oracle diff: how to compare two tables? mysql除外。

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 中相应行的数据不同

看来我不能使用相交或减号。当我尝试时

SELECT * FROM robot intersect 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

我在做一些语法错误吗？如果没有，我可以使用其他查询吗？

编辑:另外，我正在通过免费版本的 DbVisualizer 进行查询。不确定这是否是一个因素。

最佳答案

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
        )

https://stackoverflow.com/questions/950414/