テーブルアップデート処理について

お世話になります。

テーブルのレコード更新処理を行うプログラム上から行う場合、
update分を発行する場合とreplace(deleteしてから再度regist)する場合があります。
データーベースエンジンの仕様によるものもあると思いますが、あえてreplaceを使う利点などはありますか。

よろしくお願いいたします。

行動規範の内容に同意します

回答4件

REPLACEはあくまでもDELETE+INSERTなのでUPDATEとは用途が違います。

SQL
1create table tbl(
2id int primary key auto_increment,
3val1 varchar(10),
4val2 varchar(10)
5);
6insert into tbl values(1,'a100','b100'),(2,'a110','b110');

INSERTとUPDATE（実際にはDELETEしてINSERT）が同時に行えます

SQL
1replace into tbl values(1,'a200','b200'),(3,'a120','b120');

※上記、id=1は見た目上UPDATEされ、id=3はINSERTされます
ただしこれは、INSERT~ON DUPLOCATE UPDATEでもできます

SQL
1insert into tbl values(1,'a300','b300'),(4,'a130','b130') 
2on duplicate key update val1=values(val1);

大きく違うのはREPLACEがDELETE+INSERTであることから
カラムを指定して処理するとUPDATEとは挙動が違うこと

SQL
1replace into tbl(id,val1) values(1,'a300'),(5,'a140');

この場合id=1はval1が更新されているように見えますが、
DELETEされているわけですからval2も当然NULLになってしまいます。
これはUPDATEが優秀というわけではなく違う処理だということなので
用途にあわせてREPLACEが優先して使われることもあるでしょう。

投稿2017/12/13 01:13

yambejp

総合スコア114843

sazi

2017/12/13 01:56

mysqlにはreplaceがあるんですね。勉強不足でした。

yambejp

2017/12/13 02:04

ああなるほど、逆にreplaceはmysqlの方言なのですね

annderber

2017/12/14 02:01

コメントありがとうございます replaceが優先して使われるような用途には何が考えれるでしょうか？

yambejp

2017/12/14 02:11

必要項目以外の項目をデフォルト値に更新したい場合ですかねもちろんinsert on duplicate key updateでもできますけど記述は少なくてすみます。 auto_inrementでuniqeなカラムをもっていたりすると新しい番号で採番されますぎゃくにreplace構文がSQLの標準で用意されていないということはなくても大丈夫ということです

annderber

2017/12/14 03:09

コメントありがとうございます。なるほど、デフォルト値に戻したいというのはありそうですね。ただ、replace文は漠然とあまり安全ではないというイメージがあります。あえて使う理由もなさそうですね。

行動規範の内容に同意します

ベストアンサー

replace？
deleteしてinsertですよね？

メリットがあるとしたら、SQLの記述が簡潔になる場合があるという位？
実行面ではトランザクション量は多くなるしデメリットの方が多いのではないでしょうか。

追記

yambejpさんの投稿を見て、replaceの存在を知ったので、調べた内容を追記しておきます。
replaceはmargeやupsertを実現するためのmysqlの拡張機能ですが、特殊な目的（※）が無い限り「INSERT ON DUPLICATE KEY UPDATE」による本来のupdate or insertで行った方がよさそうですね。
※どのような場合にreplaceの方が適しているか思い付かないですが。

裏MySQL入門(17) 基礎編11 REPLACE or INSERT ON DUPLICATE KEY UPDATE

投稿2017/12/12 10:05

編集2017/12/13 02:10

sazi

総合スコア25195

annderber

2017/12/14 01:58

コメントありがとうございます。 upsertっていう単語を初めて聞きました。基本的にはreplaceを使うことはないですよね。なのでどのような場合にreplaceを使うのかなと思って質問したのですが、あまりないようですね。プログラムから発行するsql分の数は減るのでコード量は減るのかなと思うんですが、あまり利点にならないでしょうか

sazi

2017/12/14 02:14

コード量よりも結果を優先すべきだと思います。コード量は共通化するなりで対応可能ですが、結果の違いはどうしようもありません。

annderber

2017/12/14 03:00

コメントありがとうございます。結果を優先すべきとのことですが、ここでいう結果とは何を指しているのでしょうか。

sazi

2017/12/14 04:35

意図しないことにならないようようにすることです。例えば、オートインクリメントの情報をKEYとしていて外部キーとして参照しているような場合にdelete+insertだと、リンクが切れてしまいますよね。

annderber

2017/12/14 05:29 編集

コメントありがとうございます。確かにその危険性があります。 replace文ではオートインクリメントされたidなどをsql文に含ませてやれば、そのidでdelete,insertが行われるのでキーが変わることはありませんが(リンクは切れますが)、もし含まれていなかった場合など意図しない挙動が起きやすいですね。updateだとそのようなことがありません。なのであえて使う必要はなさそうですよね。

行動規範の内容に同意します

適切なインデックスがある限り、UPDATEするよりDELETE&INSERTの方が遅いです。
LOOPの中で100万件くらいUPDATE or DELETE&INSERT して処理時間を実測されては？

以前、100万件以上あるテーブルで TABLE ACCESS FULL(全件走査)になっているUPDATEがあったので、インデックスを追加するように伝えたけど、直さないと言われたこともあります。爆

投稿2017/12/12 20:31