データ移行やバッチの修正のあとで、新旧2つのテーブルの中身が同じかを確かめたい場面があります。件数を数えるだけでは、行が入れ替わっていたり値が変わっていたりしても気づけません。片方にしかない行(追加・削除)と値が変わった行は、LEFT JOIN と NULL どうしも比べられる演算子 <=> を組み合わせると、1回のクエリでまとめて出せます。
<> で比べると、NULL から値に変わった行を見落とします。大文字と小文字だけが変わった行も、MySQL の初期設定のままでは差分に出てきません。この2つの落とし穴も、実行結果で確かめます。列が多いときに便利な UNION ALL と GROUP BY の書き方と、MySQL 8.0.31 から使える EXCEPT の書き方も紹介します。
以下の結果は MySQL 8.0 で実際に実行したものです。
このシナリオで考える
商品マスタを新しいテーブルへ移したとします。移行前が products_old、移行後が products_new です。列は同じで、category(分類)だけ NULL を入れられます。
CREATE TABLE products_old (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
price INT NOT NULL,
category VARCHAR(20)
);
CREATE TABLE products_new (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
price INT NOT NULL,
category VARCHAR(20)
);
INSERT INTO products_old VALUES
(1, 'りんご', 120, '果物'),
(2, 'みかん', 80, '果物'),
(3, 'にんじん', 60, NULL),
(4, 'キャベツ', 150, '野菜'),
(5, '牛乳', 200, NULL),
(7, 'USBケーブル', 980, '雑貨');
INSERT INTO products_new VALUES
(1, 'りんご', 120, '果物'),
(2, 'みかん', 90, '果物'),
(3, 'にんじん', 60, '野菜'),
(5, '牛乳', 200, NULL),
(6, '卵', 250, '食品'),
(7, 'usbケーブル', 980, '雑貨');
2つのテーブルには、わざと次の違いを入れてあります。id 1 と id 5 は同じ内容です(id 5 は分類がどちらも NULL)。
| id | 違い | 見つけたい区分 |
|---|---|---|
| 2 | 価格が 80 から 90 に変わった | 変更 |
| 3 | 分類が NULL から「野菜」に変わった | 変更 |
| 4 | 移行後のテーブルにない | 削除 |
| 6 | 移行後のテーブルにだけある | 追加 |
| 7 | 名前の大文字・小文字が変わった | 変更 |
まず件数を比べます。
SELECT
(SELECT COUNT(*) FROM products_old) AS old_count,
(SELECT COUNT(*) FROM products_new) AS new_count;
+-----------+-----------+
| old_count | new_count |
+-----------+-----------+
| 6 | 6 |
+-----------+-----------+
どちらも6件です。件数が合っていても、中身は5か所違います。
LEFT JOINで片方にしかない行を出す
追加と削除は、主キー(id)で LEFT JOIN して、相手側の id が NULL になった行を取れば出せます。相手のテーブルに同じ id がないと、相手側の列はすべて NULL で埋まるためです。
-- 移行後にしかない行(追加)
SELECT n.*
FROM products_new AS n
LEFT JOIN products_old AS o ON o.id = n.id
WHERE o.id IS NULL;
+----+------+-------+----------+
| id | name | price | category |
+----+------+-------+----------+
| 6 | 卵 | 250 | 食品 |
+----+------+-------+----------+
-- 移行前にしかない行(削除)
SELECT o.*
FROM products_old AS o
LEFT JOIN products_new AS n ON n.id = o.id
WHERE n.id IS NULL;
+----+--------------+-------+----------+
| id | name | price | category |
+----+--------------+-------+----------+
| 4 | キャベツ | 150 | 野菜 |
+----+--------------+-------+----------+
WHERE で見る列は、相手側の主キーにしてください。NULL を入れられる列(ここでは category)で判定すると、もとから NULL の行まで「片方にしかない行」に混ざります。
値が変わった行は<=>で比べる
両方にある行のうち、どこかの列が違う行を探します。素直に書くと、列ごとに <> で比べて OR でつなぐ形になります。
SELECT o.id, o.price AS old_price, n.price AS new_price,
o.category AS old_category, n.category AS new_category
FROM products_old AS o
JOIN products_new AS n ON n.id = o.id
WHERE o.name <> n.name
OR o.price <> n.price
OR o.category <> n.category;
+----+-----------+-----------+--------------+--------------+
| id | old_price | new_price | old_category | new_category |
+----+-----------+-----------+--------------+--------------+
| 2 | 80 | 90 | 果物 | 果物 |
+----+-----------+-----------+--------------+--------------+
分類が NULL から「野菜」に変わった id 3 が出てきません。NULL との比較は、= でも <> でも結果が NULL(不明)になるからです。WHERE は結果が真の行だけを残すので、NULL になった行は捨てられます。
SELECT NULL = NULL, NULL <> '野菜', NULL <=> NULL, NULL <=> '野菜';
+-------------+------------------+---------------+-------------------+
| NULL = NULL | NULL <> '野菜' | NULL <=> NULL | NULL <=> '野菜' |
+-------------+------------------+---------------+-------------------+
| NULL | NULL | 1 | 0 |
+-------------+------------------+---------------+-------------------+
MySQL の <=>(NULL 安全等価演算子)は、両方 NULL なら 1、片方だけ NULL なら 0 を返します。NULL が返ることはありません。この演算子で「全列が等しい」を作り、NOT で反転させると、変わった行だけが残ります。
SELECT o.id, o.price AS old_price, n.price AS new_price,
o.category AS old_category, n.category AS new_category
FROM products_old AS o
JOIN products_new AS n ON n.id = o.id
WHERE NOT (o.name <=> n.name
AND o.price <=> n.price
AND o.category <=> n.category);
+----+-----------+-----------+--------------+--------------+
| id | old_price | new_price | old_category | new_category |
+----+-----------+-----------+--------------+--------------+
| 2 | 80 | 90 | 果物 | 果物 |
| 3 | 60 | 60 | NULL | 野菜 |
+----+-----------+-----------+--------------+--------------+
id 3 が出るようになりました。分類が両方 NULL の id 5 は、同じ内容として扱われて出てきません。列が多いときは、行の形 (列1, 列2, ...) でまとめて比べることもできます。MySQL のリファレンスには、(a, b) <=> (x, y) は (a <=> x) AND (b <=> y) と同じだと書かれています。
SELECT o.id
FROM products_old AS o
JOIN products_new AS n ON n.id = o.id
WHERE NOT ((o.name, o.price, o.category) <=> (n.name, n.price, n.category));
+----+
| id |
+----+
| 2 |
| 3 |
+----+
<=> は MySQL 独自の書き方です。PostgreSQL では、標準 SQL の IS NOT DISTINCT FROM で同じ比較を書きます。NULL の比較のしくみは「SQLのNULLと三値論理:IS NULL・COALESCEの使い分け」で説明しています。
大文字と小文字の違いを見落とさない
ここまでの結果に、名前が「USBケーブル」から「usbケーブル」に変わった id 7 が出ていません。MySQL 8.0 の初期設定の照合順序(文字の比べ方の決まり)は utf8mb4_0900_ai_ci です。末尾の ci は大文字と小文字を区別しないという意味で、この2つの名前は「等しい」と判定されます。
SELECT 'USBケーブル' = 'usbケーブル' AS ci_equal,
'USBケーブル' COLLATE utf8mb4_bin = 'usbケーブル' AS bin_equal;
+----------+-----------+
| ci_equal | bin_equal |
+----------+-----------+
| 1 | 0 |
+----------+-----------+
文字を1つずつ区別して比べたい列には、COLLATE utf8mb4_bin を付けます。
SELECT o.id, o.name AS old_name, n.name AS new_name
FROM products_old AS o
JOIN products_new AS n ON n.id = o.id
WHERE NOT (o.name COLLATE utf8mb4_bin <=> n.name
AND o.price <=> n.price
AND o.category <=> n.category);
+----+-----------------+-----------------+
| id | old_name | new_name |
+----+-----------------+-----------------+
| 2 | みかん | みかん |
| 3 | にんじん | にんじん |
| 7 | USBケーブル | usbケーブル |
+----+-----------------+-----------------+
移行で文字コードや照合順序を変えた場合は、名前やメールアドレスの列に付けておくと安心です。照合順序で結果が変わるほかの例は「MySQLの照合順序とutf8mb4:検索結果が変わる理由と設定」にまとめています。
追加・削除・変更を1回のクエリで出す
3つの結果を UNION ALL でつなぐと、1つの差分一覧になります。前半は移行前のテーブルから LEFT JOIN して「削除」と「変更」を出し、後半で「追加」を出します。MySQL には FULL OUTER JOIN(両側の外部結合)がないので、この組み立てで代わりにします。
SELECT o.id,
CASE
WHEN n.id IS NULL THEN '削除'
ELSE '変更'
END AS diff,
o.name AS old_name, n.name AS new_name,
o.price AS old_price, n.price AS new_price,
o.category AS old_category, n.category AS new_category
FROM products_old AS o
LEFT JOIN products_new AS n ON n.id = o.id
WHERE n.id IS NULL
OR NOT (o.name COLLATE utf8mb4_bin <=> n.name
AND o.price <=> n.price
AND o.category <=> n.category)
UNION ALL
SELECT n.id, '追加',
NULL, n.name, NULL, n.price, NULL, n.category
FROM products_new AS n
LEFT JOIN products_old AS o ON o.id = n.id
WHERE o.id IS NULL
ORDER BY id;
+----+--------+-----------------+-----------------+-----------+-----------+--------------+--------------+
| id | diff | old_name | new_name | old_price | new_price | old_category | new_category |
+----+--------+-----------------+-----------------+-----------+-----------+--------------+--------------+
| 2 | 変更 | みかん | みかん | 80 | 90 | 果物 | 果物 |
| 3 | 変更 | にんじん | にんじん | 60 | 60 | NULL | 野菜 |
| 4 | 削除 | キャベツ | NULL | 150 | NULL | 野菜 | NULL |
| 6 | 追加 | NULL | 卵 | NULL | 250 | NULL | 食品 |
| 7 | 変更 | USBケーブル | usbケーブル | 980 | 980 | 雑貨 | 雑貨 |
+----+--------+-----------------+-----------------+-----------+-----------+--------------+--------------+
最初に入れた5つの違いが、区分つきですべて出ました。前半と後半で同じ id が出ることはないので、重複を消す UNION にする必要はありません。変更前と変更後の値が横に並ぶため、どの列が変わったかもひと目でわかります。
列が多いときはUNION ALLとGROUP BYかEXCEPT
列が数十あると、<=> の条件を全部書くのは手間です。そのときは、2つのテーブルを縦に積んで全列で GROUP BY し、1件しかない組み合わせを残す書き方が使えます。
SELECT id, name, price, category, MAX(src) AS src
FROM (
SELECT 'old' AS src, id, name, price, category FROM products_old
UNION ALL
SELECT 'new' AS src, id, name, price, category FROM products_new
) AS t
GROUP BY id, name, price, category
HAVING COUNT(*) = 1
ORDER BY id, src DESC;
+----+--------------+-------+----------+------+
| id | name | price | category | src |
+----+--------------+-------+----------+------+
| 2 | みかん | 80 | 果物 | old |
| 2 | みかん | 90 | 果物 | new |
| 3 | にんじん | 60 | NULL | old |
| 3 | にんじん | 60 | 野菜 | new |
| 4 | キャベツ | 150 | 野菜 | old |
| 6 | 卵 | 250 | 食品 | new |
+----+--------------+-------+----------+------+
GROUP BY は NULL どうしを同じグループにまとめるので、<=> を書かなくても id 5 は出ません。同じ id が old と new の2行で出たら変更、1行だけなら削除か追加です。各テーブルの中に同じ行が2件ない前提なので、主キーのあるテーブルで使ってください。
MySQL 8.0.31 以降なら、EXCEPT(差集合)でも書けます。上の結果から移行後にしかない行の組み合わせ、下の結果から移行前にしかない行の組み合わせがわかります。
SELECT id, name, price, category FROM products_new
EXCEPT
SELECT id, name, price, category FROM products_old
ORDER BY id;
+----+--------------+-------+----------+
| id | name | price | category |
+----+--------------+-------+----------+
| 2 | みかん | 90 | 果物 |
| 3 | にんじん | 60 | 野菜 |
| 6 | 卵 | 250 | 食品 |
+----+--------------+-------+----------+
SELECT id, name, price, category FROM products_old
EXCEPT
SELECT id, name, price, category FROM products_new
ORDER BY id;
+----+--------------+-------+----------+
| id | name | price | category |
+----+--------------+-------+----------+
| 2 | みかん | 80 | 果物 |
| 3 | にんじん | 60 | NULL |
| 4 | キャベツ | 150 | 野菜 |
+----+--------------+-------+----------+
EXCEPT は何も付けないと DISTINCT として動き、重複した行を1件にまとめます。3つの書き方の違いは次のとおりです。
| 書き方 | NULL の扱い | 出せるもの | 使える版 |
|---|---|---|---|
| LEFT JOIN と <=> | <=> を書けば同じ値として扱う | 追加・削除・変更の区分と、変更前後の値を横に並べた一覧 | MySQL 5.x から |
| UNION ALL と GROUP BY | 何もしなくても同じ値として扱う | 違う行(old・new の印つき) | MySQL 5.x から |
| EXCEPT | 何もしなくても同じ値として扱う | 片方にしかない行(変更された行を含む) | MySQL 8.0.31 から |
よくある落とし穴
GROUP BY と EXCEPT の結果には、id 7 の大文字・小文字の変更が出ていません。どちらも列の照合順序で同じかどうかを決めるので、utf8mb4_0900_ai_ci の列では「USBケーブル」と「usbケーブル」が同じ行とみなされます。大文字と小文字の違いも拾いたいときは、LEFT JOIN と <=> の書き方で、名前の列に COLLATE utf8mb4_bin を付けてください。
結合に使うキーにも気をつけます。移行のときに id を振り直していると、同じ商品でも id が合わないため、全行が「削除」と「追加」に出ます。その場合は、商品コードのような移行の前後で変わらない列で結合してください。
何百万行もあるテーブルでは、1回で全行を比べるとクエリが長く動き続けます。結合に使う列にインデックスがあることを確かめ、WHERE o.id BETWEEN 1 AND 100000 のように id の範囲で分けて比べると、1回あたりの負荷を抑えられます。
まとめ
2つのテーブルの差分は、主キーで LEFT JOIN して相手側の id が NULL の行を取れば追加と削除が、<=> で全列を比べれば変更がわかります。<> で比べると NULL から値に変わった行を落とすので、NULL を含む列では必ず <=> を使います。大文字と小文字の違いまで見るなら COLLATE utf8mb4_bin を付けてください。列が多くて条件を書くのが手間なときは、UNION ALL と GROUP BY、または MySQL 8.0.31 以降の EXCEPT が使えます。
もっと学ぶなら【PR】
- 達人に学ぶSQL徹底指南書 第2版:CASE 式やウィンドウ関数など、一歩進んだ書き方がわかる中級者向けの本
※Amazonのアソシエイトとして、当サイトは適格販売により収入を得ています。
参考リンク
- MySQL 8.0 Reference Manual: Comparison Functions and Operators
- MySQL 8.0 Reference Manual: EXCEPT Clause
- MySQL 8.0 Reference Manual: Character Sets and Collations in MySQL
アイキャッチ画像: Photo by Alessandro Venturi on Unsplash
