[解決済み] MySQLで'insert if not exists'を行うにはどうしたらいいですか？

質問

まずはググってみて、こんな記事を見つけました。 標準SQLでINSERT if NOT EXISTSクエリを記述する方法 では、ミューテックステーブルについて説明しています。

私は1,400万件のレコードを持つテーブルを持っています。同じフォーマットでさらにデータを追加したい場合、挿入したいレコードがすでに存在しないことを、一対のクエリ（すなわち、チェックするクエリと、結果セットが空の場合に挿入するクエリ）を使用せずに確認する方法はありますか？

を使用することはできますか？ unique フィールドの制約により insert は、すでに存在する場合は失敗するのでしょうか？

どうやら 単に 制約がある場合、PHP経由で挿入を発行すると、スクリプトが異常終了します。

どうすればいいですか？

使用方法 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、または、REPLACEを使用することです。 重複キー更新時にinsert ... を行う。

あるテーブルがあるとします。

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. を繰り返し実行しても、'重複' が原因で死ぬことはありません。 > primary key'のエラーが発生します。

方法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' が既に存在する場合、'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 は id=id の代入は、MySQL 最適化エンジンがこの代入を無視するようにするためです。 の操作)。この方法の利点は、重複した キーイベントを発生させ、その他のエラーが発生した場合は中止します。

最後に、この投稿はXaprbに触発されたものであることをお知らせします。また、この記事は 柔軟なSQLクエリの書き方については、彼の他の投稿を参照してください。