概要

アプリのデータベース更新系処理を改修したときに、
改修前後で、更新結果のテーブルデータがきちんと完全一致しているか調べる機会があったので、
手順とコピペ用SQLを纏めた。

前提

MySQLであること。

手順とSQL

1. 更新対象のテーブルを、コピーして別名で保存しておく。

CREATE TABLE initial_table LIKE target_table;
INSERT INTO initial_table SELECT * FROM target_table;

※likeをすると、インデックスもコピーされる（今回は特に意味ないかも）

2. アプリで改修前の更新処理を実行

masterブランチで実行することになるかと。

3. 2で更新されたテーブルをリネーム

ALTER TABLE target_table RENAME TO before_table;

4. 1で保存しておいたテーブルを、本来の名前でコピー

CREATE TABLE target_table LIKE initial_table;
INSERT INTO target_table SELECT * FROM initial_table;

5. アプリで改修後の更新処理を実行

改修ブランチで実行することになるかと。

6. 5で更新されたテーブルをコピー

CREATE TABLE after_table LIKE target_table;
INSERT INTO after_table SELECT * FROM target_table;

ここまでで、アプリ改修前後の更新結果テーブルがそれぞれ存在することになる。

before_table （改修前の更新結果）
after_table （改修後の更新結果）

7. アプリ改修前後の更新結果テーブルを比較

比較1,2を両方行い、どちらも結果が0件ならOK.

比較1 (RIGHT JOIN)

SELECT
    *
FROM 
    after_table AS a
RIGHT JOIN
    before_table AS b
    ON
        a.プライマリーキー = b.プライマリーキー
    AND
        a.更新したカラム1 = b.更新したカラム1
    AND
        a.更新したカラム2 = b.更新したカラム2
    AND
        〜〜〜以下、更新したカラムを全てANDで記述する
WHERE
    a.プライマリーキー IS NULL

比較2 (LEFT JOIN)

SELECT
    *
FROM 
    after_table AS a
LEFT JOIN
    before_table AS b
    ON
        a.プライマリーキー = b.プライマリーキー
    AND
        a.更新したカラム1 = b.更新したカラム1
    AND
        a.更新したカラム2 = b.更新したカラム2
    AND
        〜〜〜以下、更新したカラムを全てANDで記述する
WHERE
    b.プライマリーキー IS NULL

※片方だけの実行だと、「before_tableにあるがafter_tableに無いレコード、およびその逆が検出できない。

※JOIN句で指定するカラムは、アプリの更新処理で対象となっている全カラム。
（つまり、更新ではなくテーブル作成の場合は、テーブルの全カラムとなる。。）
（場合によってはプライマリーキーは指定しない方が良い）

※JOIN句で指定するカラムがNULLを許容しており、かつaもbもNULLの場合は一致させたい場合、
下記のようにNULLの場合空文字に変換すればOK.
IFNULL(a.更新したカラム1,'') = IFNULL(b.更新したカラム1,'')
→参考：SQLでNULLとNULLは「＝」ではありません。

※WHERE句で指定するカラムは「NULLになることがありえないカラム」推奨。

※JOINではなくUNIONで比較する方法もあるが、レコード数が多い場合はUNIONだと極端に遅くなる。

【MySQL】アプリ改修前後のテーブルデータ比較手順（コピペ用）

概要

前提