CSVでテーブルの一括UPSERT

バッチでよくあるやつで、
CSVを使って 一括でテーブルをUPSERT(新規または更新)する流れをまとめてみました。
ちなみに mysql です。

準備

DB作る

更新対象テーブルつくる

データを投入

CSVを用意する

以下コマンドでcsvを生成する。

テーブルをcsvに書き出す方法としては、ほかにも mysqldump コマンドを使う方法があります。
awsのフルマネージドなRDSを使っている場合は、 mysqldump より mysql コマンドのほうが使い勝手は良いです。

仮テーブル作る

WHERE 1=0 はインデックスやユニークキー、主キー制約がすべて除去する意図で指定している。
仮テーブルにはデータを投入したい/全件スキャンしたいだけなので、これらは不要。逆にあると、パフォーマンスが落ちるので。

仮テーブル (users_temp) が意図した形で作成されたか確認

意図した形というのは:

  • 必要なフィールド(id, email, name )が揃っている
  • インデックスやユニークキー、主キー制約がすべて除去されている

ok

CSVを編集する

今回は検証なので適当に users.csv を編集する:

  • id:1 は更新レコード
  • id:4 は新規レコード

となるように編集した。
そして以下のようにしてCSVを仮テーブルに投入する。
CSVの1行目はヘッダーなので無視するようにする。

CSVを仮テーブルに投入する

LOAD DATA LOCAL INFILELOCAL というのは、mysqlが動いているサーバではなく、クライアント側のファイルを指定するという意味です。 LOCAL を指定しない場合(つまり LOAD DATA INFILEの場合)はmysqlサーバ上のファイルを指定することになります。しかも /var/lib/mysql など特定の場所からの相対パスであることを想定する必要があります(mysqldの設定による)。

仮テーブルにCSVが入ったか確認

ok

仮テーブルのデータを対象テーブルに投入する

  • ON DUPLICATE KEY は id または email のキーで重複したレコードがあった場合の処理を指定するという意味です。
  • UPDATE name = VALUES(name) 重複レコードは CSVの方の値で更新するという意味です。

また UPDATE対象を複数指定することもできます。

確認

投入前:

Image from Gyazo

投入後:

Image from Gyazo

ok

Filed under: テストTagged with:

よく読まれている投稿

No comment yet, add your voice below!


Add a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.