☰

「INSERT IGNORE」と「INSERT ... ON DUPLICATE KEY UPDATE」の違い 質問する

tag-icon • tag-icon mysql insert
「INSERT IGNORE」と「INSERT ... ON DUPLICATE KEY UPDATE」の違い 質問する

多くの行を含むステートメントを実行するときにINSERT、失敗の原因となる重複エントリをスキップしたいです。調査した結果、オプションは次のいずれかを使用するようです。

  • ON DUPLICATE KEY UPDATEこれは、何らかのコストをかけて不必要な更新を行うことを意味します。
  • INSERT IGNORE他の種類の失敗が予告なしに忍び込む可能性を示唆しています。

これらの仮定は正しいでしょうか? 重複が発生する可能性のある行をスキップして、他の行に進むための最善の方法は何でしょうか?

ベストアンサー1

を使用することをお勧めします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。

ユーザーがこの投稿を編集しようとしました (編集はモデレーターによって拒否されました)。編集では、新しい自動増分 ID が割り当てられるクレームを追加しようとしました。新しい ID が生成されるINSERT...ON DUPLICATE KEY UPDATEのは事実ですが、変更された行では使用されません。

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 ステートメントが重複を検出し、更新を呼び出して の値を変更することを示しています。 は、ID が生成されたが、行では使用されていないことを示していることuに注意してください。AUTO_INCREMENT=3

一方、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 |
+----+------+

おすすめ記事