MySQL での重複キー更新時の replace into と insert into の使用法と相違点の分析

この記事では、MySQL での重複キー更新時の replace into と insert into の使用方法と違いを例を使って説明します。ご参考までに、詳細は以下の通りです。

重複キーの更新時にreplace intoとinsert intoの両方を実行すると、通常発生する問題を解決できます。

つまり、レコードがデータベース内に存在する場合はレコード内のデータを更新し、存在しない場合はレコードを追加します。

テストテーブルテストを作成します

テーブル「test」を作成します（
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
 `name` varchar(32) デフォルト '' COMMENT '名前',
 `addr` varchar(256) デフォルト '' COMMENT 'アドレス',
 主キー (`id`)
)ENGINE=InnoDB デフォルト文字セット=utf8;

テーブルにデータを挿入する

テストに挿入
価値観
	(NULL、'a'、'aaa')、
	(NULL、'b'、'bbb')、
	(NULL、'c'、'ccc')、
	(NULL、'd'、'ddd');

影響を受ける行数は 4 で、結果は次のようになります。

次のステートメントを実行します。

テストVALUES(NULL, 'e', 'eee')に置き換えます。

結果は、1 行が影響を受け、レコードが正常に挿入されたことを示しています。

上記のステートメントでは、主キー ID を入力していないことに注意してください。

次に、次のステートメントを実行します。

テスト値(1, 'aa', 'aaaa')に置き換えます。

結果は、2 行が影響を受け、ID 1 のレコードが正常に更新されたことを示しています。

なぜこのようなことが起こるのでしょうか。その理由は、replace into が最初にテーブルにレコードを挿入しようとするからです。ID は主キーであり、繰り返すことができないため、このレコードは明らかに正常に挿入できません。次に、replace into は既存のレコードを削除してから挿入するため、影響を受ける行の数は 2 であると表示されます。

次のステートメントをもう一度実行してみましょう。

test(id,name) VALUES(1, 'aaa') に置き換えます。

ここでは、 id フィールドと name フィールドのみを指定します。置換後も addr フィールドの内容がまだ存在するかどうかを確認しましょう。

明らかに、addr フィールドの内容は消えており、これは上記の分析と一致しています。reaplce into は最初に id 1 のレコードを削除し、次にレコードを挿入しますが、addr の値を指定していないため、上の図のようになります。

しかし、場合によっては、レコードが存在する場合に指定されたフィールドのデータを更新し、上記のように addr フィールドのデータが消えるのではなく、元のフィールドのデータが保持されることが求められることがあります。

ここでは重複キーの更新時に挿入を使用する必要があります

次のステートメントを実行します。

テストに挿入 (ID、名前)
値(2, 'bb') 
重複キーについて 
アップデート 
名前 = VALUES(名前);

VALUES(フィールド名)は現在のステートメント挿入の列値を取得することを意味し、VALUES(名前)は「bb」を意味します。

結果は2行が影響を受けていることを示している

上図に示すように、addr フィールドの値は保持されます。

重複キー更新ステートメントの insert into は、最初にレコードを挿入し、失敗した場合はレコードを更新しますが、影響を受ける行数が 2 なのはなぜですか?

テーブルを再構築してみましょう test2

テーブル「test2」を作成します（
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
 `sn` varchar(32) DEFAULT '' COMMENT 'ユニークキー',
 `name` varchar(32) デフォルト '' COMMENT '名前',
 `addr` varchar(256) デフォルト '' COMMENT 'アドレス',
 主キー (`id`)、
 ユニークキー `sn` (`sn`)
)ENGINE=InnoDB デフォルト文字セット=utf8;

そこにデータを挿入する

test2に挿入
価値観
	(NULL、'01'、'a'、'aaa')、
	(NULL、'02'、'b'、'bbb')、
	(NULL、'03'、'c'、'ccc')、
	(NULL、'04'、'd'、'ddd');

次のステートメントを実行します。

test2に挿入(sn、名前、アドレス)
価値観
	('02'、'bb'、'bbbb')
重複キーについて 
アップデート 
名前 = VALUES(名前)、
addr = VALUES(addr);

結果は次のとおりです。

上記のステートメントが実行されるたびに、影響を受ける行数は 0 ですが、テーブル test2 の自動インクリメント フィールドは 1 ずつ増加します。

明らかに、重複キー更新ステートメントの挿入が元のレコードのみを更新する場合、自動インクリメント フィールドは自動的に 1 増加されず、レコード削除操作も実行されることになります。

まずレコードを挿入します。失敗した場合は、元のレコードを削除しますが、更新ステートメント後のフィールドの値を保持します。次に、保持された値を更新される値とマージしてから、新しいレコードを挿入します。

要約:

replace into と insert into on duplicate key update はどちらも、最初にレコードを挿入しようとします。失敗した場合、レコードは削除されます。replace into は元のレコードの値を保持しませんが、insert into on duplicate key update は保持します。次に、新しいレコードを挿入します。

MySQL 関連のコンテンツに興味のある読者は、このサイトの次のトピックをチェックしてください: 「MySQL クエリ スキル」、「MySQL 共通関数の概要」、「MySQL ログ操作スキル」、「MySQL トランザクション操作スキルの概要」、「MySQL ストアド プロシージャ スキル」、および「MySQL データベース ロック関連スキルの概要」

この記事が皆様のMySQLデータベース設計に役立つことを願っています。