既存データがあればUPDATE、なければINSERTするSQL文を実行したい

実現したいこと

・全顧客情報(数千件)が入ったcsvファイルをアップロード
・すでに登録されている顧客であれば電話番号を更新
・未登録の顧客であれば新規レコードを追加

テーブル構造

mysql 5.7.28

テーブル名
customers

カラム情報
・customer_id(一意、10桁の英数字、連番では無い)
・phone(電話番号)
・パスワードなど、顧客情報が10カラム程度

現状

csvファイルからデータを取り出すところまでは完成している
1行ずつデータを取り出し、SQL文を実行したいと考えています。

お聞きしたいこと

以下を実現するSQL文がわかる方がいらっしゃいましたら、アドバイスをいただきたく存じます。

csvファイルから取り出した配列に格納されている「customer_id」と一致するデータがあれば、電話番号を更新(UPDATE)する
csvファイルから取り出した配列に格納されている「customer_id」と一致するデータが無ければ、新規レコードを作成(INSERT)する

上記2つの処理を1つのSQL文にまとめられるのが理想ですが、最悪2つに別れても良いと考えています。
他に必要な情報等ございましたらご指摘いただければと思います。
どうぞよろしくお願いいたします。

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

回答4件

MySQL限定ですが、REPLACE 構文使うと一文でできます。

https://dev.mysql.com/doc/refman/5.6/ja/replace.html

SQL
1REPLACE INTO test VALUES (1, 'Old', '2014-08-20 18:47:00');

投稿2020/04/30 04:42

編集2020/04/30 04:42

退会済みユーザー

総合スコア0

d0ne1s

2020/04/30 05:33

ご回答ありがとうございます。ご教授いただいたREPLACE構文について調べてみました。色々みてみたところ「REPLACE句はDELETEした後、「同じデータを持つレコードをINSERTする」という情報に辿り着きました。私の説明不足で恐縮なのですが、今回の用件では既存データを更新する時、電話番号だけを更新し、他のカラム(パスワード等)は保持したいです。その場合の方法について、自分でももう少し考えてみます。もし何か良い方法がありましたら、ご教授いただけますと大変助かります。

d0ne1s

2020/04/30 05:35

https://beyondjapan.com/blog/2016/02/replace-query/

退会済みユーザー

2020/04/30 05:36

何が問題ですか？

d0ne1s

2020/04/30 05:38

REPLACEで電話番号を更新する際、登録されているパスワードが消えてしまうのではないかと危惧しております。

退会済みユーザー

2020/04/30 05:46

そうならないようにすればいいだけだし、いきなり本番環境でやるような愚かなこと普通しないよね？

d0ne1s

2020/04/30 05:54

いきなり本番環境でやるとは考えていません。現状REPLACEで実装するイメージが浮かんでいないので、まずupsertで対応してみようと思います。ありがとうございました。

yambejp

2020/04/30 06:21

replaceは基本的に削除して追加するのと同等の処理です。一部のカラムだけ更新したい場合は使えません

退会済みユーザー

2020/04/30 06:25

必要なカラムを全て揃えて実行すれば実質更新後と同じ結果になります。

d0ne1s

2020/04/30 06:47

yambejpさまありがとうございます！

d0ne1s

2020/04/30 06:49

Kosuke_Shibuyaさま何か、upsertではなくreplaceを選択した方が良い理由があるのでしょうか。

退会済みユーザー

2020/04/30 07:08

ないです。通常使いませんし、自分もプロダクトでは使ったことないです。あくまで、「一文で」に焦点を絞った回答です。

d0ne1s

2020/04/30 07:09

承知いたしました。ご回答いただきましてありがとうございました。

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

以下参考
MySQL: INSERT...ON DUPLICATE KEY UPDATEまとめ

高速に処理するためにはSQLの発行回数を減らす事ですが、CSVを逐次読み込んでという事なら、Values句に値を並べる事になりますが、それよりも、CSVの取込をCSVの項目と一致した全てテキスト型の項目のワークテーブル（一時テーブルも考慮）を用意して、LOAD DATA INFILEで取込し、そのテーブルを元にinsert into select ON DUPLICATE KEY UPDATEを行うのが一番処理時間の短縮が図れると思います。

仮にCSVのエラーチェックなどがあったとしても、そのワークテーブルでSQL一括で行う方が高速です。

投稿2020/04/30 07:01

編集2020/04/30 07:07

sazi

総合スコア25195

d0ne1s

2020/04/30 07:07

ありがとうございます。 LOAD DATA INFILE、初めて聞きました。こんな便利な構文があるのですね。今回、既存のメソッドとの関係で、一番修正範囲が少ないupsertで実装させていただこうと思いますが、今後同様の仕組みを実装する際には、利用を検討させていただきます。大変参考になりました。

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

ベストアンサー

INSERT ... ON DUPLICATE KEY UPDATE 構文
を試してみてください。

MySQL upsertあたりで調べてみると関連情報も手に入りやすいです。

投稿2020/04/30 04:42

tanat

総合スコア18713

d0ne1s

2020/04/30 05:37

ご回答ありがとうございます。私の使ったことの無い書き方が色々ありそうなので、いただいた情報を元にもう少し頑張ってみます。また質問文で説明しきれていなかったのですが、今回の用件では「既存データを更新する時、電話番号だけを更新し、他のカラム(パスワード等)は保持する」必要があります。もし何か良い実装方法がございましたらご教授いただけますと幸いです。

tanat

2020/04/30 05:43

> 「既存データを更新する時、電話番号だけを更新し、他のカラム(パスワード等)は保持する」動作としてはUPDATEですから、希望通りに動くと思いますよ。まずは検証できる環境で試されるとその辺りの不安も解決すると思います。

d0ne1s

2020/04/30 05:45

頭がこんがらがっていました。確かにおっしゃる通りですね。検証できる環境で試してみます。ありがとうございます！

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

ネットで検索するとでると思いますがPDOでしたら下記のコードになるかと思います。
$countの結果で処理を振り分ければいいと思います。

PHP
1例)カスタマーIDが渡されるとして・・・。
2
3$_POST['key'];
4
5$dbh = new PDO($DSN , $DBUSER , $DBPASS);
6$dbh->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true); 
7$sql = 'SELECT * FROM customers where customer_id='.$key;
8$stmt = $dbh->query($sql);
9$stmt->execute();
10$count=$stmt->rowCount();
11
12//echo $count;//確認用
13
14//ここに分岐処理