あけましておめでとうございます。(22日遅れ)
はじめに
こんな感じの単純なテーブルを想定。(MySQL 8.0)
CREATE TABLE `t_xxx` (
`id` int NOT NULL AUTO_INCREMENT,
`data` varchar(45) NOT NULL,
PRIMARY KEY (`id`)
)
(MySQLは付け焼き刃程度の知識しかないので、内容が不正確な場合があります...)
IGNORE句
MySQLのINSERT
で
「既に同じキーのレコードが存在する場合はスキップ」
みたいな処理がやりたいときに、ネットで調べると
INSERT IGNORE INTO `t_xxx` (`id`, `data`) VALUES (10, "hoge");
とIGNORE
句を付加するという方法がよく出てくる。
でも、さらに調べると IGNORE
句は重複エラーを無視するだけで、副作用として他のエラーも握り潰しちゃうことが分かる・・・(「IGNORE」だから当然といえば当然)
重複してたときにワーニングという形で返ってくるのも気持ち悪い。
しかし、さらにさらに調べても「じゃあどうすれば?」という解決策は見つけられない・・・
IGNORE
じゃなくてON DUPLICATE KEY UPDATE
でやるのはどうだろう?
本来は、存在しない場合はINSERT
、既に存在する場合はUPDATE
を行う機能(いわゆるUPSERT
)だけど、この更新対象をPKにしちゃう。
INSERT INTO `t_xxx` (`id`, `data`) VALUES (10, "hoge") ON DUPLICATE KEY UPDATE `id` = `id`;
すると、既に存在したときは、DUPLICATEした(同じ値だった)要素を更新することになり、必然的にどの値も変化せずスキップしたのと同じことになる。
これなら重複してもワーニングが返ってこないし、重複以外のエラーは通常のINSERT
句のまま返ってくるから、IGNORE
句より良い気がする・・・どうだろう・・・
ちゃんとした理屈が分かっていないけど、重複していた場合にAUTO_INCREMENT
が勝手に進んだりもしない。
(そもそもPKがオートインクリメントするテーブルで「なければ追加」みたいな処理をしたい場面はあまり無いような・・・)
おわりに
このやり方とても単純な話なのに、なぜか探しても同様の方法が出てこなかったから、とりあえず記事にしてみたものの、なにか重大な見落としがあるのかも・・・
でも、問題点を何も書かずにIGNORE
句のやり方を示す記事も何だかな。。
複雑なテーブルやクエリにまで使えるかは分からないけど、少なくとも今回のような単純なテーブルで重複スキップのINSERT
をやるだけならIGNORE
より安全なはず。
ちなみにUPSERTをやりたくてON DUPLICATE KEY UPDATE
を試行錯誤してるときに間違ってPKを更新対象にしてしまい、
「追加はできるのに更新ができない・・・エラーも返ってこない・・・」
と少しハマってたときに、この動作に気づいたのは内緒・・・笑