■ はじめに
システムのマイグレーションで テーブル差分を調査する必要ができたので そのやり方を調べてみた
目次
【0】データ準備 1)使用データ 【1】方法1:差集合 EXCEPT / MINUS 1)サンプル 2)使用上の注意 【2】方法2:LEFT OUTER JOIN 1)サンプル 2)使用上の注意
【0】データ準備
* 試すDBがなければ、以下のサイトで行うといいかも、、、 * PostgreSQL15を想定
SQL Fiddle
https://www.db-fiddle.com/
1)使用データ
CREATE TABLE IF NOT EXISTS member_old ( id INT, name VARCHAR(50), gender VARCHAR(1), country_code VARCHAR(3), class VARCHAR(10), updated_date DATE ); CREATE TABLE IF NOT EXISTS member_new ( id INT, name VARCHAR(50), gender VARCHAR(1), country_code VARCHAR(3), class VARCHAR(10), updated_date DATE ); -- INSERT member_old INSERT INTO member_old (id,name,gender,country_code,class,updated_date) VALUES (1,'Mike','m','USA','bronze','2024-09-14'), (2,'Tom',NULL,'GBR','gold','2024-09-14'), (3,'Smith','f',NULL,'silver','2024-09-14'), (4,'Ken',NULL,'JPN','gold','2024-09-14'), (5,'Jack','m','UKR','gold','2024-09-14'), (6,'Henry','m','AUS','bronze','2024-09-14'), (7,'Sophia','f','JPN','platinum','2024-09-14'), (8,'George','m','USA',NULL,'2024-09-14'), (9,'Lily','f','JPN','bronze','2024-09-14'), (10,'Daisy','f','USA','platinum','2024-09-14') ; -- INSERT member_new INSERT INTO member_new (id,name,gender,country_code,class,updated_date) VALUES (1,'Mike','m','USA','bronze','2024-09-14'), (2,'Tom','m','GBR','platinum','2024-09-15'), -- ★変更 (3,'Smith','f',NULL,'silver','2024-09-14'), (4,'Ken','m','JPN','gold','2024-09-15'), -- ★変更 (5,'Jack','m','UKR','gold','2024-09-14'), (6,'Henry','m','AUS','silver','2024-09-15'), -- ★変更 (7,'Sophia','f','JPN','platinum','2024-09-14'), (8,'George','m','USA','silver','2024-09-15'), -- ★変更 (9,'Lily','f','JPN','bronze','2024-09-14'), (10,'Daisy','f','USA','platinum','2024-09-14'), (11,'Leo','m',NULL,NULL,'2024-09-15'), -- ★追加 (12,'Sam',NULL,'AUS','bronze','2024-09-15') -- ★追加 ;
【1】方法1:差集合 EXCEPT / MINUS
* 差集合で差分を抽出する => 差集合の詳細は、以下の関連記事を参照のこと
差集合 ~ EXCEPT / MINUS etc ~
https://dk521123.hatenablog.com/entry/2021/05/26/142059
1)サンプル
SELECT id, name, gender, country_code, class, updated_date FROM member_new -- ★注目★ EXCEPT SELECT id, name, gender, country_code, class, updated_date FROM member_old ORDER BY id ;
id | name | gender | country_code | class | updated_date |
---|---|---|---|---|---|
2 | Tom | m | GBR | platinum | 2024-09-15T00:00:00.000Z |
4 | Ken | m | JPN | gold | 2024-09-15T00:00:00.000Z |
6 | Henry | m | AUS | silver | 2024-09-15T00:00:00.000Z |
8 | George | m | USA | silver | 2024-09-15T00:00:00.000Z |
11 | Leo | m | 2024-09-15T00:00:00.000Z | ||
12 | Sam | AUS | bronze | 2024-09-15T00:00:00.000Z |
2)使用上の注意
[1] あくまで MINUS(差)なので、TABLE_A MINUS TABLE_B だとしたら TABLE_B の方にTABLE_A にない項目があった場合、検知できない
例
-- INSERT member_old TRUNCATE TABLE member_old; INSERT INTO member_old (id,name,gender,country_code,class,updated_date) VALUES (1,'Mike','m','USA',NULL,'2024-09-14'), --(2,'Tom',NULL,'GBR',NULL,'2024-09-14'), -- ★コメントアウト★ (3,'Smith','f',NULL,NULL,'2024-09-14'), (4,'Ken',NULL,'JPN',NULL,'2024-09-14') ; -- INSERT member_new TRUNCATE TABLE member_new; INSERT INTO member_new (id,name,gender,country_code,class,updated_date) VALUES (1,'Mike','m','USA',NULL,'2024-09-14'), (2,'Tom',NULL,'GBR',NULL,'2024-09-14'), --(3,'Smith','f',NULL,NULL,'2024-09-14'), -- ★コメントアウト★ (4,'Ken',NULL,'JPN',NULL,'2024-09-14') ;
出力結果
* id = 2 が追加されたことを検知できるが id=3を検知できる訳じゃない
id | name | gender | country_code | class | updated_date |
---|---|---|---|---|---|
2 | Tom | GBR | 2024-09-14 |
id = 3 を検知するには
SELECT id, name, gender, country_code, class, updated_date FROM -- member_new -- ★コメントアウト member_old -- ★逆にした EXCEPT SELECT id, name, gender, country_code, class, updated_date FROM -- member_old -- ★コメントアウト member_new -- ★逆にした ;
id | name | gender | country_code | class | updated_date |
---|---|---|---|---|---|
3 | Smith | f | 2024-09-14 |
【2】方法2:LEFT OUTER JOIN
新規データ差分抽出
* LEFT OUTER JOIN すると 整合性を保つために該当なし部分に関しては nullで処理されるという性質があるので それを利用して「WHERE <項目> IS NULL」すると 差分が取得できる
変更データ差分抽出
* 各データが異なるかをWHERE句で追加する
1)サンプル
新規データ差分抽出
SELECT * FROM member_old AS old LEFT OUTER JOIN member_new AS new ON old.id = new.id -- ★ここに注目★ WHERE old.id IS NULL ;
id | name | gender | country_code | class | updated_date | id | name | gender | country_code | class | updated_date |
---|---|---|---|---|---|---|---|---|---|---|---|
11 | Leo | m | 2024-09-15 | ||||||||
12 | Sam | AUS | bronze | 2024-09-15 |
変更データ差分抽出
SELECT * FROM member_new AS new LEFT OUTER JOIN member_old AS old ON old.id = new.id WHERE old.name <> new.name OR old.gender <> new.gender OR old.country_code <> new.country_code OR old.class <> new.class OR old.updated_date <> new.updated_date ;
id | name | gender | country_code | class | updated_date | id | name | gender | country_code | class | updated_date |
---|---|---|---|---|---|---|---|---|---|---|---|
2 | Tom | m | GBR | platinum | 2024-09-15 | 2 | Tom | GBR | gold | 2024-09-14 | |
4 | Ken | m | JPN | gold | 2024-09-15 | 4 | Ken | JPN | gold | 2024-09-14 | |
6 | Henry | m | AUS | silver | 2024-09-15 | 6 | Henry | m | AUS | bronze | 2024-09-14 |
8 | George | m | USA | silver | 2024-09-15 | 8 | George | m | USA | 2024-09-14 |
2)使用上の注意
[1] どの差分をみたいかによって、差分抽出できなくなる
-- サンプル SELECT * FROM member_old AS old -- 逆にした LEFT OUTER JOIN member_new AS new -- 逆にした ON old.id = new.id WHERE new.id IS NULL ; -- データが出ない(当たり前なのだが)
[2] 削除は検知できない
-- 「【1】方法1:差集合 EXCEPT / MINUS」の -- 「2)使用上の注意」のサンプルを実行 SELECT * FROM member_old AS new LEFT OUTER JOIN member_old AS old ON old.id = new.id WHERE old.name <> new.name OR old.gender <> new.gender OR old.country_code <> new.country_code OR old.class <> new.class OR old.updated_date <> new.updated_date ; -- 何も検知されない
参考文献
https://style.potepan.com/articles/26163.html
* 以下は、今回紹介した方法以外にも紹介されている
https://note.com/shuma__rrrrr/n/nd637b713106b
関連記事
SQL ~ 関係演算 / 集合論 ~
https://dk521123.hatenablog.com/entry/2023/01/16/000000
SQL ~ 集合演算の応用 ~
https://dk521123.hatenablog.com/entry/2024/09/16/223305
Snowflake ~ 関係演算 / 集合論 ~
https://dk521123.hatenablog.com/entry/2024/09/12/003814
テーブルの結合 ~完全結合・Union / Union All ~
https://dk521123.hatenablog.com/entry/2010/03/28/162247
差集合 ~ EXCEPT / MINUS etc ~
https://dk521123.hatenablog.com/entry/2021/05/26/142059
SQL ~ INTERSECT ~
https://dk521123.hatenablog.com/entry/2024/09/14/040940
Window関数 ~ LAG / LEAD ~
https://dk521123.hatenablog.com/entry/2021/09/10/092850
WITH句 ~ 共通テーブル式 ~
https://dk521123.hatenablog.com/entry/2012/07/26/013620