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

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

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

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

### INSERT IGNOREとは何か

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

> INSERT ステートメントの実行中に発生する無視可能なエラーは無視されます
[MySQL 8.0 リファレンスマニュアル 13.2.6 INSERT ステートメント](https://dev.mysql.com/doc/refman/8.0/ja/insert.html#:~:text=INSERT%20%E3%82%B9%E3%83%86%E3%83%BC%E3%83%88%E3%83%A1%E3%83%B3%E3%83%88%E3%81%AE%E5%AE%9F%E8%A1%8C%E4%B8%AD%E3%81%AB%E7%99%BA%E7%94%9F%E3%81%99%E3%82%8B%E7%84%A1%E8%A6%96%E5%8F%AF%E8%83%BD%E3%81%AA%E3%82%A8%E3%83%A9%E3%83%BC%E3%81%AF%E7%84%A1%E8%A6%96%E3%81%95%E3%82%8C%E3%81%BE%E3%81%99)

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

```sql: 無視されるエラー一覧
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文の流れ

```terminal: 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になっていれば当然、レコードは作成されないでほしいのですが…

```terminal: 危険な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句を使わないと構文エラーになるので注意です。

```terminal: 代替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という名の化け物](https://songmu.jp/riji/entry/2015-07-20-insert-ignore.html)
[MySQL 4.1 リファレンスマニュアル 12.1. 返されるエラー](http://download.nust.na/pub6/mysql/doc/refman/4.1/ja/error-returns.html)
[MySQL 8.0 リファレンスマニュアル 13.2.6 INSERT ステートメント](https://dev.mysql.com/doc/refman/8.0/ja/insert.html#:~:text=INSERT%20%E3%82%B9%E3%83%86%E3%83%BC%E3%83%88%E3%83%A1%E3%83%B3%E3%83%88%E3%81%AE%E5%AE%9F%E8%A1%8C%E4%B8%AD%E3%81%AB%E7%99%BA%E7%94%9F%E3%81%99%E3%82%8B%E7%84%A1%E8%A6%96%E5%8F%AF%E8%83%BD%E3%81%AA%E3%82%A8%E3%83%A9%E3%83%BC%E3%81%AF%E7%84%A1%E8%A6%96%E3%81%95%E3%82%8C%E3%81%BE%E3%81%99)
[MySQLの存在しないときINSERT,WHERE NOT EXISTSではまる](https://blog.goo.ne.jp/xmldtp/e/b803681e5820a044988f45149df5854b)