kamipo TRADITIONALでは防げないINSERT IGNOREという名の化け物

インスパイア元→kamipo traditional (というかSTRICT_ALL_TABLES) では防げないMyISAMという名の化け物

タイトルが全てです。ピンときた方は読み進む必要はありません。

データがなかったらINSERTして欲しいけど既に入っている場合には何もして欲しくないみたいな処理をするときに、 INSERT IGNORE を使ってしまうことがありますが、 INSERT IGNORE はユニークキー制約違反だけじゃなくて、あらゆるエラーをIGNOREしてしまいます。つまりkamipo TRADITIONALすらIGNOREしてしまうのです。なので使わないほうが安全です。

様子です。

mysql> SET SESSION sql_mode='TRADITIONAL,NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE test (
    ->   id INTEGER PRIMARY KEY,
    ->   name VARCHAR(191) NOT NULL,
    ->   created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO test (id, name) VALUES (1, 'Songmu');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test;
+----+--------+---------------------+
| id | name   | created_at          |
+----+--------+---------------------+
|  1 | Songmu | 2015-07-20 02:44:15 |
+----+--------+---------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO test (id, name) VALUES (1, 'yoku0825');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

mysql> INSERT IGNORE INTO test (id, name) VALUES (1, 'yoku0825');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM test;
+----+--------+---------------------+
| id | name   | created_at          |
+----+--------+---------------------+
|  1 | Songmu | 2015-07-20 02:44:15 |
+----+--------+---------------------+
1 row in set (0.00 sec)

idが1でSongmuを入れた後に、id 1でyoku0825をINSERT IGNOREしてもエラーにはならずに、ちゃんとSongmuが残っています。これは意図した挙動といえるでしょう。

では、idを2にしてyoku0825を入れてみることにします。ただし、created_atに不正な値を入れることにしてみます。

mysql> INSERT INTO test (id, name, created_at) VALUES (2, 'yoku0825', '0000-00-00 00:00:00');
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'created_at' at row 1

mysql> INSERT IGNORE INTO test (id, name, created_at) VALUES (2, 'yoku0825', '0000-00-00 00:00:00');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SELECT * FROM test;
+----+----------+---------------------+
| id | name     | created_at          |
+----+----------+---------------------+
|  1 | Songmu   | 2015-07-20 02:44:15 |
|  2 | yoku0825 | 0000-00-00 00:00:00 |
+----+----------+---------------------+
2 rows in set (0.00 sec)

(　ﾟдﾟ)

(つд⊂)ｺﾞｼｺﾞｼ

(；ﾟдﾟ)

(つд⊂)ｺﾞｼｺﾞｼ
   _, ._
（；ﾟ Дﾟ）

はい、INSERT IGNORE していない場合にはちゃんと、エラーが返ってきていますが、 INSERT IGNORE したらデータが入ってしまっています。…orz.

なので、 INSERT IGNORE は使わないほうがいいです。以前、レコードがなかったらINSERTして返すみたいなのを確実にやる というエントリーを書いたことがあるのですが、ちょっとこれでは不十分なようで、

• INSERT ON DUPLICATE KEY UPDATEを使う
• SELECTして無かったらINSERTして、Duplicateエラーをトラップしたら一旦トランザクションを抜けてからまたトランザクションを張ってSELECTしなおす
• DELETEしてからINSERTする
• 素直に例外をあげる

という選択肢の中からマッチしたやり方を選択するのが正しいように思います。

我々は何故2015年にもなってSELECTして無かったらINSERTする場合のベストパターンを確立できていないのか。

— songmu (@songmu) 2015, 7月 16

大変ですね。