【MySQL】重複レコードを避けたいときにINSERT IGNOREを使うのはあぶない

以前先輩に「このSQL文流してください🙋‍♀️」とお願いした中で、重複レコードを避けるためINSERT IGNOREを入れていました。後からかなり危ないコマンドだったことを知り反省したので、備忘録として残します。

概要

MySQLにて便利なコマンド「INSERT IGNORE」について、以下の項目に沿って話します。

• INSERT IGNOREとは何か
• なぜ危ないのか
• 重複を避けたい時の代替SQL文

INSERT IGNOREとは何か

レコード挿入のためのINSERT句につけることができる修飾子です。
名の通り、レコードを登録する際に「無視をする」という働きを持っています。
何を無視するのかというと、次のようなことです。

INSERT ステートメントの実行中に発生する無視可能なエラーは無視されます
MySQL 8.0 リファレンスマニュアル 13.2.6 INSERT ステートメント

では、INSERT IGNOREにおける「無視可能なエラー」とは何でしょうか？

無視されるエラー一覧

ER_BAD_NULL_ERROR
ER_DUP_ENTRY
ER_DUP_ENTRY_WITH_KEY_NAME
ER_DUP_KEY
ER_NO_PARTITION_FOR_GIVEN_VALUE
ER_NO_PARTITION_FOR_GIVEN_VALUE_SILENT
ER_NO_REFERENCED_ROW_2
ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET
ER_ROW_IS_REFERENCED_2
ER_SUBQUERY_NO_1_ROW
ER_VIEW_CHECK_FAILED

結構色々なエラーを無視してくれます。「DUP（重複）系」は今回の例でもあったように意図して無視してほしいエラーになりそうです。

実際に使ってみましょう！

INSERT IGNOREを使ったSQL文の流れ

INSERT IGNORE成功サンプル

-- メールアドレスにUNIQUE制約を貼ったテーブルを作成
mysql> CREATE TABLE sample_table (
    ->   ID INTEGER NOT NULL AUTO_INCREMENT,
    ->   名前 VARCHAR(20) NOT NULL,
    ->   メールアドレス VARCHAR(30) UNIQUE NULL,
    ->   PRIMARY KEY (ID)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO sample_table (名前, メールアドレス)
    -> VALUES ('太郎', 'tarou@email'),
    -> ('花子', 'hanako@email');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM sample_table;
+----+--------+-----------------------+
| ID | 名前   | メールアドレス            |
+----+--------+-----------------------+
|  1 | 太郎   | tarou@email           |
|  2 | 花子   | hanako@email          |
+----+--------+-----------------------+
2 rows in set (0.00 sec)

-- 重複するメールアドレスが入ったレコードを作成しようとするとエラーになる
mysql> INSERT INTO sample_table (名前, メールアドレス) 
    -> VALUES ('三郎', 'saburou@email'),
    -> ('サブ太郎', 'tarou@email');
ERROR 1062 (23000): Duplicate entry 'tarou@email' for key 'メールアドレス'

-- 重複のない三郎のレコードも作成されていない
mysql> SELECT * FROM sample_table;
+----+--------+-----------------------+
| ID | 名前   | メールアドレス        　　　　　　　　|
+----+--------+-----------------------+
|  1 | 太郎   | tarou@email           |
|  2 | 花子   | hanako@email          |
+----+--------+-----------------------+
2 rows in set (0.00 sec)

-- INSERT IGNOREを使って重複レコード以外のレコードを挿入する
-- warningは出るがレコード作成はひとつ成功している
mysql> INSERT IGNORE INTO sample_table (名前, メールアドレス) 
    -> VALUES ('三郎', 'saburou@email'),
    -> ('サブ太郎', 'tarou@email');
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 2  Duplicates: 1  Warnings: 1

-- 三郎のレコードのみが入っている
mysql> SELECT * FROM sample_table;
+----+--------+-----------------------+
| ID | 名前   | メールアドレス            |
+----+--------+-----------------------+
|  1 | 太郎   | tarou@email           |
|  2 | 花子   | hanako@email          |
|  3 | 三郎   | saburou@email         |
+----+--------+-----------------------+
3 rows in set (0.00 sec)

意図したとおり、三郎のレコードは作成され、メールアドレスが重複していたサブ太郎のレコードは無視されました！ﾔｯﾀｰ
このように、ぱっと見すごく便利だし、IGNOREの単語一つ入れるだけなので気軽に使ってしまいそうになるINSERT IGNOREですが、実はかなり危険なコマンドです。

なぜ危ないのか

エラーを全無視してくれるので、本来は当然エラーになってほしいレコードも許してしまう場合があります。
例えば、先程つくったテーブルでは名前カラムをNOT NULL制約にしていました。
INSERT時の値がNULLになっていれば当然、レコードは作成されないでほしいのですが…

危険なINSERT IGNOREサンプル

mysql> INSERT IGNORE INTO sample_table (名前, メールアドレス) 
    -> VALUES (NULL, 'blank@email');
Query OK, 1 row affected, 1 warning (0.00 sec)

-- INSERT IGNOREしたことで名前の値にNULLが入ったレコードが作成されてしまう
mysql> SELECT * FROM sample_table;
+----+--------+-----------------------+
| ID | 名前   | メールアドレス            |
+----+--------+-----------------------+
|  1 | 太郎   | tarou@email           |
|  2 | 花子   | hanako@email          |
|  3 | 三郎   | saburou@email         |
|  4 |       　| blank@email           |
+----+--------+-----------------------+
4 rows in set (0.00 sec)

これはよくないですね。こうしたレコードが入ったテーブルをそのまま使用することで、意図しないバグが発生する恐れもあり、危険性大です。

結論、INSERT IGNOREは基本使わないほうが良さそうです。
では、代わりにどのようなSQL文を流すべきなのでしょうか？

重複を避けたい時の代替SQL文

今回は重複レコードを例外としてあげ、INSERT時に入らないようにしました。
この方法では、VALUESの代わりにSELECT句を使わないと構文エラーになるので注意です。

代替SQL文のサンプル

-- サブ花子のメールアドレスは重複しているため入ってほしくない
mysql> INSERT INTO sample_table (名前, メールアドレス) 
    -> SELECT * FROM (SELECT 'サブ花子', 'hanako@email') AS temp_table 
    -> WHERE NOT EXISTS (SELECT * FROM sample_table WHERE メールアドレス = 'hanako@email');
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 正常にSQLが流れ、不要なレコードも作成されていない
mysql> SELECT * FROM sample_table;
+----+--------+-----------------------+
| ID | 名前   | メールアドレス       　　　　　　　　 |
+----+--------+-----------------------+
|  1 | 太郎   | tarou@email           |
|  2 | 花子   | hanako@email          |
|  3 | 三郎   | saburou@email         |
+----+--------+-----------------------+
3 rows in set (0.00 sec)

少し複雑な書き方ですが、warningもなく正常にSQLが流れるので安全だと思います。
他にも重複レコードの作成を避ける方法として、ON DUPLICATE KEY UPDATEなどもあるそうですが、基本的には例外をあげるのが丸いやり方ではないでしょうか。

参考記事

kamipo TRADITIONALでは防げないINSERT IGNOREという名の化け物
MySQL 4.1 リファレンスマニュアル 12.1. 返されるエラー
MySQL 8.0 リファレンスマニュアル 13.2.6 INSERT ステートメント
MySQLの存在しないときINSERT,WHERE NOT EXISTSではまる