一対多のテーブルで変更されたデータだけ追加/削除したい

例えば、何らかのサービスの会員登録で興味のあるジャンルをチェックボックスで選択するような場合で、それらが変更された場合、追加と削除が同時に発生するような機能。

賢いORMを使っていれば何となく雰囲気でやってくれそうだけど、自前で実装する必要がある場合どうするか？　みたいな。

テーブル構造は以下のような構造を想定（外部キーやインデックス指定は省略）。

CREATE TABLE `user_genles` (
    `id`       int(11) unsigned NOT NULL AUTO_INCREMENT,
    `user_id`  int(11) unsigned NOT NULL,
    `genle_id` int(11) unsigned NOT NULL,
    PRIMARY KEY (`id`)
);

プログラムからパラメータとして以下のようなuser_idとgenle_idのマッピングを表したリストが与えられると想定。

[
    {user_id: 1, genle_id:1,},
    {user_id: 1, genle_id:2,},
    {user_id: 1, genle_id:3,},
]

このような場合、まずはテーブルに登録されていないgenle_idを登録し、、、

INSERT INTO user_genles SELECT null, ?, ? FROM dual WHERE NOT EXISTS (SELECT * FROM user_genles WHERE genle_id=? AND user_id=?)

次に、リストに存在しないgenle_idを削除、、、

-- genle_id, genle_id, genle_idみたいな文字列をセット
DELETE FROM user_genles WHERE genle_id NOT IN (?) AND user_id = ?

みたいな流れになる。当然ながらこれらは同じトランザクションの中で行われなければならない。

どちらのSQLにもNOT〜が出てくるので多少混乱させられそう。

もうひとつの解決策は、user_id にマッチするレコードを豪快に全て削除＆インサートする方法。だたし、こちらは実装がシンプルになる反面、更新頻度やデータ量によってはid列がオーバーフローする可能性が高まることに注意が必要（なので、この手のテーブルではid列を持たないという選択もあり）。

SET型？　知らない子ですね...

(2018/08/21)

/note/tech

一対多のテーブルで変更されたデータだけ追加/削除したい