UK違反でエラーになると困るケース
主キーやユニークキーを付けていれば、重複したデータをINSERTしようとするとエラーが発生する。
特に以下のような場合にエラーが投げられると困る。
- 複数行をBULK INSERTしたく、一部でもエラーになると全部INSERTできない
- PostgreSQLはExceptionが投げられると必ずROLLBACKしなければいけない
- アプリケーションから発行するSQLでエラーを無視するようなことはしたくない
そのため、データがないときだけINSERTする方法を考える。
INSERT ... SELECTとNOT EXISTSを組み合わせた解法
PostgreSQLでもMySQLでも使える方法として以下のような書き方がある。
INSERT INTO table_a (column_1, column2)
SELECT 'X1', 'Y1' WHERE NOT EXISTS (SELECT 1 FROM table_a WHERE column_1 = 'X1');column1が'X1'のデータがあるかどうかをWHERE句にして、なければINSERTしている。あればSELECT文は何も返さないため、INSERTも行われない。
複数行を同時にINSERTしたければUNION ALLと組み合わせるといい。
INSERT INTO table_a (column_1, column2)
SELECT 'X1', 'Y1' WHERE NOT EXISTS (SELECT 1 FROM table_a WHERE column_1 = 'X1')
UNION ALL
SELECT 'X2', 'Y2' WHERE NOT EXISTS (SELECT 1 FROM table_a WHERE column_1 = 'X2')
;DELETE & INSERT
ナイーブな方法として、既に存在するデータを削除してから登録するというものがある。
DBで発番するサロゲートキーがある場合はサロゲートキーが変わることに加えて、サロゲートキーがAUTO_INCREMENTの場合は無駄にAUTO_INCREMENT値が進む。また2文を同一トランザクション内で実行しなければならない。
以下を満たす場合にしか使いたくない。
- サロゲートキーを利用していない
- データ量が少なく、性能要件も緩い
ただ、以下にあげる例のようにデータの洗い替え的な用途では使われるのを目にすることがある。
例えば、ユーザーは複数の権限を持つことができ、画面上でユーザーの権限を複数個選択/選択解除した上で登録ボタンを押すことで永続化するというような要件があったとする。この要件を実装する場合、該当ユーザーの既に登録されている権限を一括DELETEしてから画面で選択されている権限を一括INSERTするというような使い方が考えられる。既に登録されている権限のうち画面で選択されてる権限に含まれないものをDELETEし、次に画面で選択されている権限をデータがないときだけINSERTするよりもシンプルに書けるからだろう。
以下は結構複雑なSQLになる。
-- 画面で選択した権限ID: 1, 2, 3
-- 既にDBに登録されている権限ID: 1, 4
-- 1, 2, 3に含まれないものを削除
DELETE FROM users_roles WHERE user_id = 99 AND role_id NOT IN (1, 2, 3);
-- 1は重複のためスキップし、2, 3だけ登録
INSERT INTO users_roles (user_id, role_id)
SELECT 99, 1 WHERE NOT EXISTS (SELECT 1 FROM users_roles WHERE user_id = 99 AND role_id = 1)
UNION ALL
SELECT 99, 2 WHERE NOT EXISTS (SELECT 1 FROM users_roles WHERE user_id = 99 AND role_id = 2)
UNION ALL
SELECT 99, 3 WHERE NOT EXISTS (SELECT 1 FROM users_roles WHERE user_id = 99 AND role_id = 3);対してナイーブに全件DELETEするのは簡単。
DELETE FROM users_roles WHERE user_id = 99;
INSERT INTO users_roles (user_id, role_id) VALUES
(99, 1),
(99, 2),
(99, 3);MySQLの他の解法
サロゲートキーとデメリット
DELETE & INSERTでも同じだったが、以下に記載するON DUPLICATE KEY UPDATEもREPLACEもサロゲートキーをつけている場合にデメリットが出てくる。
サロゲートキーがないテーブルであれば、むしろ積極的に使いたい。
ON DUPLICATE KEY UPDATE
MySQLであればON DUPLICATE KEY UPDATEが使える。しかし既にデータが存在する場合にもAUTO_INCREMENT値が進んでしまう問題がある。実際のデータ量は少ないからといってサロゲートキーをINT型にしていると、更新が激しい場合は容易にINT型の上限に達してしまう。
REPLACE
REPLACEはDELETE & INSERTと同じだが、常にDELETEするわけではなくてPK違反/UK違反が発生したときにDELETEしてくれる。
ナイーブとは言えないまでも、「サロゲートキーをAUTO_INCREMENTで使用している場合はサロゲートキーが変わる」問題は同様に抱えることになる。
INSERT IGNORE INTOの問題点
INSERT IGNORE INTOはPK違反/UK違反を無視してくれるだけでなく他のINSERTステートメントの実行中に発生したエラーを無視してしまうため使うべきではない。