[解決済み] "INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE"

質問

を実行中に INSERT ステートメントで、多くの行がある場合、失敗の原因となる重複するエントリをスキップしたいのです。いくつかの調査の後、私のオプションは、どちらかを使用するように見えます。

• ON DUPLICATE KEY UPDATE というのは、何らかのコストをかけて不必要なアップデートをすることを意味する、あるいは
• INSERT IGNORE は、他の種類の障害が予告なしに入り込むことを示唆している。

この仮定は正しいでしょうか？重複を引き起こす可能性のある行を単純にスキップして、他の行に進むには、どのような方法が良いでしょうか？

どのように解決するのですか？

を使うことをお勧めします。 INSERT...ON DUPLICATE KEY UPDATE .

を使用する場合 INSERT IGNORE その結果、キーが重複した場合、行は実際に挿入されません。 しかし、この文はエラーを発生させません。 代わりに警告が発生します。 このようなケースは以下の通りです。

• を持つ列に重複するキーを挿入する。 PRIMARY KEY または UNIQUE の制約を受けます。
• を持つカラムにNULLを挿入する。 NOT NULL の制約があります。
• パーティション分割されたテーブルに行を挿入したが、挿入した値がパーティションにマッピングされていない。

を使用した場合 REPLACE を使用すると、MySQLは実際に DELETE の後に INSERT の内部で、予期せぬ副作用が発生します。

• 新しいオートインクリメントIDが割り当てられる。
• 外部キーを持つ従属行は、(カスケード外部キーを使用している場合) 削除されるかもしれませんし、さもなければ REPLACE .
• で発火するトリガー DELETE が不必要に実行される。
• 副作用がレプリカにも伝搬される。

を修正しました。 両方 REPLACE と INSERT...ON DUPLICATE KEY UPDATE は、MySQL に特有の非標準的な独自の発明です。 ANSI SQL 2003 では MERGE ステートメントを使用すると、同じニーズ（およびそれ以上）を解決できますが、MySQL はこのステートメントをサポートしません。 MERGE ステートメントを使用します。

あるユーザーがこの投稿を編集しようとしました（編集はモデレーターによって拒否されました）。 その編集は、次のような主張を追加しようとしました。 INSERT...ON DUPLICATE KEY UPDATE は、新しい自動インクリメントのidを割り当てるようにします。 たしかに新しいidは 生成 しかし、それは変更された行では使用されません。

Percona Server 5.5.28でテストした以下のデモをご覧ください。 設定変数 innodb_autoinc_lock_mode=1 (デフォルト)です。

mysql> create table foo (id serial primary key, u int, unique key (u));
mysql> insert into foo (u) values (10);
mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  1 |   10 |
+----+------+

mysql> show create table foo\G
CREATE TABLE `foo` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `u` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u` (`u`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

mysql> insert into foo (u) values (10) on duplicate key update u = 20;
mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  1 |   20 |
+----+------+

mysql> show create table foo\G
CREATE TABLE `foo` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `u` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u` (`u`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

上記は、IODKU ステートメントが重複を検出し、更新を呼び出して u . なお AUTO_INCREMENT=3 は、id が生成されたが、その行で使用されていないことを示します。

ここで REPLACE は、元の行を削除して新しい行を挿入するので と は新しいオートインクリメントIDを格納します。

mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  1 |   20 |
+----+------+
mysql> replace into foo (u) values (20);
mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  3 |   20 |
+----+------+