MySQL なければINSERT、あればスキップ（IGNORE〜）のおすすめ

かなり前に、なければINSERT、あればUPDATEというの書きました。

hit.hateblo.jp

しかしながら、現状で運用でもっとも活躍できるのは、IGNOREの方だと思います

どのようなケースで活用できるついて、サンプルを含め説明します

プライマリーキーを更新

以下のテーブルをサンプルにプライマリーキーを更新するSQLの挙動がどうなるか考えてみましょう

users

CREATE TABLE `users` (
  `userid` bigint(20) NOT NULL,
  `username` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

プライマリーキーは、userid

データ

userテーブルの内容は以下の通り

> SELECT * FROM users ;
+--------+----------+
| userid | username |
+--------+----------+
|      1 | panda    |
|      2 | dog      |
|      3 | cat      |
+--------+----------+

更新のパターン

通常のINSERT/UPDATE

INSERT

userid= 2としてINSERT してみます

INSERT INTO users (userid,username) values (2,'swan') ;
---
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

キーが重複するので、もちろんエラーです

UPDATE

userid = 3のデータを、2に更新し、結果がどうなるか確認します。プライマリーキーを更新する際、2のキーがすでに存在します

UPDATE users SET userid = 2 , username = 'swan' WHERE userid = 3 ;
---
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

キーが重複するので、もちろんエラーです

なければINSERT、あればUPDATE（ON DUPLICATE KEY UPDATE）

INSERT

ちょっと過去をおさらいして、ON DUPLICATE KEY UPDATEでやるとどうなるかみてみましょう

INSERT INTO users (userid,username) values (2,'swan') 
ON DUPLICATE KEY UPDATE userid=2,username='swan') ;
---
Query OK, 2 rows affected (0.04 sec)

上記結果

（プライマリキーが）あればINSERTなければUPDATEなので、このような結果になるは当然です

SELECT * FROM users ;
+--------+----------+
| userid | username |
+--------+----------+
|      1 | panda    |
|      2 | swan     |
|      3 | cat      |
+--------+----------+

でも、dog消えちゃいましたよね。。。dogは残して、swanの更新はなかったことにしたい場合はどうましょう？

なければINSERT、あればスキップ（INSERT IGNORE）

dogは残して、swanの更新はなかったことにしたい場合は通常のINSERT/UPDATEすればいいんじゃない？もちろん、そうなのですが（笑）更新対象のレコードが複数存在する場合は、ここでいちいちエラーが発生するのはけっこううざいです。例えば、以下のように、

2,'swan'はすでにプライマリキーが存在するので、エラー
4,'bear'は新規データなのでINSERT

とやりたいと思っても、バルクインサート(BULK-INSERT)で行った場合全てがエラーになり、ROOLBACKします

INSERT INTO users (userid,username) VALUES (2,'swan'),(4,'bear') ;
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

2,swanもないですが、4,bearもないですよね？

SELECT * FROM users ;
+--------+----------+
| userid | username |
+--------+----------+
|      1 | panda    |
|      2 | dog      |
|      3 | cat      |
+--------+----------+

では、前述の

2,'swan'はすでにプライマリキーが存在するので、エラー
4,'bear'は新規データなのでINSERT

を実現したい場合は、どうすればいいのかという時に、INSERT IGNOREが登場します