比较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屋!