Database JUNKY

MySQL,MariaDBを中心としたブログです

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が登場します

MySQL なければINSERT、あればスキップ(IGNORE〜)

INSERT IGNORE INTO users (userid,username) VALUES (2,'swan'),(4,'bear') ;
---
Query OK, 1 row affected, 1 warning (0.02 sec)
Records: 2  Duplicates: 1  Warnings: 1

ワーニングは出ていますが、エラーにはなっていませんね。

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

いかかでしょうか?2,swanはスキップされていますが、4:bearはINSERTされているのがわかるかと思います

今回の例では、2件だけで検証しましたが、通常の運用では、何万件、もしくは、何十万件の一括処理(バッチ処理)で実行するケースがほとんどかと思います。例えばこんな感じの処理になるかな?

SELECT 〜 INSERT で一括挿入

INSERT INGORE INTO users
  (userid,username)
SELECT 
  userid,
  username
FROM
  users2 ;

用途によって、ON DUPLICATE KEY UPDATEのほうが良いケースもあるとは思いますが、大概の処理で、元のデータをぶっ潰すことはあまりしたくないのでは?と思うんですよね。そんな場合は、IGNOREを利用したほうが、断然効率がいいと思います。

データベースエンジニアの人は、SQLでデータを操作するケースが、プログラマーより断然多いと思いますので、これは是非とも覚えておいたほうがいいですよ。プログラマの助けにもなります。

おまけ (UPDATE INGORE )

IGNOREは、UPDATEでも使えます。

UPDATE IGNORE users
SET 
  userid = 1,
  username = 'tiger'
WHERE
  userid = 2
;
---
Query OK, 0 rows affected, 1 warning (0.19 sec)
Rows matched: 1  Changed: 0  Warnings: 1

こちらもワーニングにはなりますが、エラーにはなりません。

 SELECT * FROM users ;
+--------+----------+
| userid | username |
+--------+----------+
|      1 | panda    |
|      2 | dog      |
|      3 | cat      |
|      4 | bear     |
+--------+----------+
4 rows in set (0.00 sec)

UPDATE IGNOREの利点は、キー重複で、エラーにならない点と、元の値が保たれているところですね。こちらも一括処理に向いていますので、ぜひご活用ください。

f:id:hit10231023:20180309104332j:plain