☰

MySQL で「存在しない場合は挿入」を実行するにはどうすればいいですか? 質問する

tag-icon • tag-icon sql mysql sql-insert primary-key unique-constraint
MySQL で「存在しない場合は挿入」を実行するにはどうすればいいですか? 質問する

私はグーグルで調べて記事を見つけました標準SQLでINSERT if NOT EXISTSクエリを書く方法ミューテックス テーブルについて説明します。

約 1,400 万件のレコードを含むテーブルがあります。同じ形式でさらにデータを追加したい場合、クエリのペア(つまり、結果セットが空かどうかをチェックするクエリと挿入するクエリ) を使用せずに、挿入するレコードがすでに存在しないことを確認する方法はありますか?

uniqueフィールドに制約がinsertある場合、その制約がすでに存在すると失敗することが保証されますか?

単なる制約では、PHP 経由で挿入を発行すると、スクリプトが異常終了するようです。

ベストアンサー1

使用INSERT IGNORE INTO table。

構文も載っておりINSERT … ON DUPLICATE KEY UPDATE、説明は13.2.6.2 INSERT ... ON DUPLICATE KEY UPDATE ステートメント。

投稿元bogdan.org.uaによるとGoogle のウェブキャッシュ:

2007年10月18日

まず、最新の MySQL では、タイトルで示した構文は使用できません。ただし、既存の機能を使用して期待どおりの結果を得るための非常に簡単な方法がいくつかあります。

解決策としては、INSERT IGNORE、REPLACE、または INSERT … ON DUPLICATE KEY UPDATE を使用するという 3 つの方法があります。

次のようなテーブルがあるとします。

CREATE TABLE `transcripts` (
`ensembl_transcript_id` varchar(20) NOT NULL,
`transcript_chrom_start` int(10) unsigned NOT NULL,
`transcript_chrom_end` int(10) unsigned NOT NULL,
PRIMARY KEY (`ensembl_transcript_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ここで、Ensembl からトランスクリプトのメタデータをインポートする自動パイプラインがあり、さまざまな理由により、実行のどのステップでもパイプラインが壊れる可能性があると想像してください。したがって、次の 2 つの点を確認する必要があります。

  1. パイプラインを繰り返し実行してもデータベースは破壊されません
  1. 繰り返し実行しても、「重複 > 主キー」エラーにより終了することはありません。

方法1: REPLACEを使用する

とても簡単です:

REPLACE INTO `transcripts`
SET `ensembl_transcript_id` = 'ENSORGT00000000001',
`transcript_chrom_start` = 12345,
`transcript_chrom_end` = 12678;

レコードが存在する場合は上書きされ、まだ存在しない場合は作成されます。ただし、この方法を使用することは、このケースでは効率的ではありません。既存のレコードを上書きする必要はなく、スキップするだけで十分です。

方法2: INSERT IGNORE を使用するこれも非常に簡単です:

INSERT IGNORE INTO `transcripts`
SET `ensembl_transcript_id` = 'ENSORGT00000000001',
`transcript_chrom_start` = 12345,
`transcript_chrom_end` = 12678;

ここで、'ensembl_transcript_id' がデータベースにすでに存在する場合、それは暗黙的にスキップされます (無視されます)。(より正確には、MySQL リファレンス マニュアルからの引用です: 「IGNORE キーワードを使用すると、INSERT ステートメントの実行中に発生するエラーは、代わりに警告として扱われます。たとえば、IGNORE がない場合、テーブル内の既存の UNIQUE インデックスまたは PRIMARY KEY 値と重複する行は重複キー エラーを引き起こし、ステートメントは中止されます。」) レコードがまだ存在しない場合は、作成されます。

この 2 番目の方法には、他の問題が発生してもクエリが中止されないなど、いくつかの潜在的な弱点があります (マニュアルを参照)。したがって、IGNORE キーワードなしで以前にテストした場合は、この方法を使用する必要があります。

方法3: INSERT … ON DUPLICATE KEY UPDATE を使用する:

3 番目のオプションは、構文を使用しINSERT … ON DUPLICATE KEY UPDATE、UPDATE 部分では何もせずに、0+0 の計算などの無意味な (空の) 操作を実行します (Geoffray は、MySQL 最適化エンジンがこの操作を無視するように id=id 割り当てを行うことを提案しています)。この方法の利点は、重複キー イベントのみを無視し、他のエラーが発生した場合は中止することです。

最後にお知らせします。この投稿は Xaprb からヒントを得たものです。柔軟な SQL クエリの作成に関する彼の他の投稿も参照することをお勧めします。

おすすめ記事