create table base (
id int not null primary key,
name text not null
);
insert into base values (1, 'A'),(2, 'B'),(3, 'C');
create table compare (
id int not null primary key,
name text not null
);
insert into compare values (1, 'A'),(2, 'BB'),(4, 'D');
外部結合がサポートされているPostgreSQLだと簡単にできます。
(PostgreSQL 18.1)
select
base.id as base_id,
base.name as base_name,
compare.id as compare_id,
compare.name as compare_name
from
base full outer join compare on base.id = compare.id
where
base.id is null or compare.id is null
or
base.id != compare.id or base.name != compare.name
;
base_id | base_name | compare_id | compare_name
---------+-----------+------------+-------------
2 | B | 2 | BB
3 | C | NULL | NULL
NULL | NULL | 4 | D
magentadesk での以前の実装は以下のとおりでした。
これはMariaDBでは問題ありませんでした。当然PostgreSQLでも問題なかったです。
ただし、MySQL 8.0系だと、正しくない結果でした。（結果的にはwhere句を追加して対応）
with
only_base as (
select * from base
except
select * from compare
),
only_compare as (
select * from compare
except
select * from base
),
merge_left as (
select
only_base.id as base_id,
only_base.name as base_name,
only_compare.id as compare_id,
only_compare.name as compare_name
from only_base left outer join only_compare on only_base.id = only_compare.id
),
merge_right as (
select
only_base.id as base_id,
only_base.name as base_name,
only_compare.id as compare_id,
only_compare.name as compare_name
from only_base right outer join only_compare on only_base.id = only_compare.id
),
merge_full as (
select * from merge_left
union
select * from merge_right
)
select
*
from
merge_full
;
(MariaDB 12.1)
+---------+-----------+------------+-------------+
| base_id | base_name | compare_id | compare_name |
+---------+-----------+------------+-------------+
| 2 | B | 2 | BB |
| 3 | C | NULL | NULL |
| NULL | NULL | 4 | D |
+---------+-----------+------------+-------------+
(PostgreSQL 18.1)
base_id | base_name | compare_id | compare_name
---------+-----------+------------+-------------
3 | C | NULL | NULL
2 | B | 2 | BB
NULL | NULL | 4 | D
(MySQL 8.0.44)
+---------+-----------+------------+-------------+
| base_id | base_name | compare_id | compare_name |
+---------+-----------+------------+-------------+
| 2 | B | 2 | BB |
| 3 | C | NULL | NULL |
| 1 | A | 1 | A |
| NULL | NULL | 4 | D |
+---------+-----------+------------+-------------+
このクエリーはMySQL 8.4 だと動きません。
そのため、次の形に変更しました。
with
only_base as (
select id, MD5(IFNULL(name, '')) as row_hash from base
except
select id, MD5(IFNULL(name, '')) as row_hash from compare
),
only_compare as (
select id, MD5(IFNULL(name, '')) as row_hash from compare
except
select id, MD5(IFNULL(name, '')) as row_hash from base
),
diff_keys as (
select id from only_base
union
select id from only_compare
),
merge_full as (
select
base.id as base_id,
base.name as base_name,
compare.id as compare_id,
compare.name as compare_name
from
diff_keys
left outer join base on diff_keys.id = base.id
left outer join compare on diff_keys.id = compare.id
)
select
*
from
merge_full
;
ポイントは、
EXCEPTの比較時はハッシュ化しておく
UNIONでは主キーのみとする
最後の外部結合で情報を取得する
といったところで、ハッシュ化する負荷はあるもののトータルで見れば軽くなった形となります。
これで、MariaDBも、MySQL 8.0および8.4 でも同じ結果を得ることができました。
さすがにわからかなったので、Geminiにも聞きながらやりました。
とはいえ、結構Geminiも間違えていたので、なかなか厄介な問題なのかなと思っています。
magentadesk での以前の実装のクエリーの問題点は、
merge_full as (
select * from merge_left
union
select * from merge_right
)
