mysql 两表比对_比较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?

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

I get an error code:

[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?

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

解决方案

INTERSECT needs to be emulated in 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

)