MySQL比較兩個表不同的資料

2019-10-16 22:56:45

在本教學中,您將學習如何比較兩個表以找到不匹配的記錄。

在資料遷移中,我們經常需要比較兩個表,以便在一個表中標識另一個表中沒有相應記錄的記錄。

例如,我們有一個新的資料庫,其架構與舊資料庫不同。我們的任務是將所有資料從舊資料庫遷移到新資料庫,並驗證資料是否正確遷移。

要檢查資料,我們必須比較兩個表,一個在新資料庫中,一個在舊資料庫中,並標識不匹配的記錄。

假設有兩個表:t1t2。使用以下步驟比較兩個表,並確定不匹配的記錄:

首先,使用UNION語句來組合兩個表中的行; 僅包含需要比較的列。返回的結果集用於比較。

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

第二步,根據需要比較的主鍵和列分組記錄。如果需要比較的列中的值相同,則COUNT(*)返回2,否則COUNT(*)返回1

請參閱以下查詢:

SELECT pk, c1
FROM
 (
   SELECT t1.pk, t1.c1
   FROM t1
   UNION ALL
   SELECT t2.pk, t2.c1
   FROM t2
)  t
GROUP BY pk, c1
HAVING COUNT(*) = 1
ORDER BY pk

如果比較中涉及的列中的值相同,則不返回任何行。

MySQL比較兩個表的例子

我們來看一個模擬上述步驟的例子。

首先,建立具有相似結構的2個表:

USE testdb;
CREATE TABLE t1(
 id int auto_increment primary key,
    title varchar(255) 
);

CREATE TABLE t2(
 id int auto_increment primary key,
    title varchar(255),
    note varchar(255)
);

其次,在t1t2表中插入一些資料:

INSERT INTO t1(title)
VALUES('row 1'),('row 2'),('row 3');

INSERT INTO t2(title,note)
SELECT title, 'data migration'
FROM t1;

第三,比較兩個表的idtitle列的值:

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;

沒有行返回,因為沒有不匹配的記錄。

第四,在t2表中插入一行:

INSERT INTO t2(title,note)
VALUES('new row 4','new');

沒有行返回,因為沒有不匹配的記錄。

第五步,執行查詢以再次比較兩個表中的title列的值。新行是不匹配的行將會返回。

mysql> 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;
+----+-----------+
| id | title     |
+----+-----------+
|  4 | new row 4 |
+----+-----------+
1 row in set

在本教學中,您已經學習了如何根據特定列比較兩個表以找到不匹配的記錄。