MySQL で行が存在しない場合の挿入方法

1. MySQL に存在しない場合に REPLACE を使って挿入する
2. MySQL に存在しない場合に挿入するには INSERT IGNORE を使用する
3. MySQL に存在しない場合の挿入には INSERT ... ON DUPLICATE KEY UPDATE を使用する

このチュートリアルでは、mySQL でテーブルに行が存在しない場合に、テーブルに行を挿入する方法を説明します。

この問題を解決するには、REPLACE、INSERT IGNORE、INSERT ... ON DUPLICATE KEY UPDATE の 3つの簡単な方法があります。

まず、このように構造化されたテーブル person があるとします。

CREATE TABLE person (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT NOT NULL,
address VARCHAR(128) 
);

次のように構造化されたテーブル person があるとします。

では、実際の解決策に入ってみましょう。

MySQL に存在しない場合に REPLACE を使って挿入する

REPLACE が何をするかというと、既存のレコードが見つかったときに上書きし、まだ存在しない場合は新しい行を挿入します。基本的には UPDATE と似ていますが、INSERT の機能を持っています。

John Doe に REPLACE を実行するサンプルクエリを用意してみましょう。

REPLACE INTO 'person'
SET id = 1,
name = 'John Doe'`,
age = 28,
address = 'Los Angeles, California'

John Doe が存在する場合、その John Doe は新しいデータで上書きされ、存在しない場合は John Doe の新しいエントリが作成されます。

しかし、この方法は既存のレコードが見つかった場合にはスキップするのではなく、既存のレコードを上書きしてしまうため、挿入の効率は良くありません。これで仕事は完了しますが、それはそれを行うための最善の方法ではありません。

John Doe を選択したクエリの新しい結果は次のようになります。

John Doe の年齢と住所は REPLACE スクリプトによって変更または更新されています。

MySQL に存在しない場合に挿入するには INSERT IGNORE を使用する

これについても、上のテーブルと同じように編集されていないデータを使用します。

INSERT IGNORE では、レコードの主キーがすでにデータベースに存在する場合、それは静かに無視されるかスキップされます。

IGNORE キーワードを削除すると、id や主キーが存在するレコードを挿入すると問い合わせが中止され、主キーを持つレコードが既に存在するというエラーメッセージが表示されます。

INSERT の後の IGNORE は、エラーによる中止の代わりに、新しいレコードは挿入しないが問い合わせを続けるだけです。

INSERT IGNORE person
SET 'id' = 4,
age = 1000,
address = 'Chicago'

上で宣言したテーブルに基づいて、id または主キー 4 を持つレコードは既に存在している (Thor Odinson)。したがって、このクエリはスクリプトによって無視され、データベースにレコードを更新したり挿入したりすることはありません。

トールを person で検索すると、結果は次のようになります。

年齢と住所は id 4 の行には全く影響を与えませんでした。

MySQL に存在しない場合の挿入には INSERT ... ON DUPLICATE KEY UPDATE を使用する

ON DUPLICATE KEY UPDATE 句を使用していて、挿入したい行が UNIQUE インデックスや主キーに重複している場合、その行は UPDATE を実行します。

これは基本的に REPLACE と同じことを行います。

例えば、id 2 のレコードを挿入したいとします。

INSERT INTO person (id, name, age, address)
VALUES (1, 'Jane Deer', '33', 'Tallahassee Florida')
ON DUPLICATE KEY UPDATE id = id+1;

id は既存のレコードを更新するのではなく、新しいレコードを生成するためにインクリメントされます。

person のすべての行を問い合わせると、以下のような結果になります。

Jane Doe の代わりに Jane Deer が新しいレコードとしてテーブルに追加されます。

挿入して存在を確認する最も効率的な方法は INSERT IGNORE を使用することです。

挿入したい行が存在するかどうかを更新したい場合は REPLACE を使用します。行を変更したくないが、どうしても新しいレコードを挿入したい場合は INSERT ... ON DUPLICATE KEY UPDATE を使用します。