【詳しく解説】MySQLにおけるUpsertの書き方(サンプル付き)

当ページのリンクには広告が含まれています。

【総まとめ】UPSERTとは?DBごとの違いは?」では、主要なDBごとの書き方をざっくり説明しました。

今回は、MySQLでのUpsertに焦点を絞り、さらに掘り下げていきたいと思います。

参考として、DataFrameからUpsertを生成するPython関数のサンプルも紹介しているので、必要に応じてコピぺしてお使いください。

目次

MySQLは Insert into ~ on duplicate key ~ で Upsertを行う

MySQLでUpsertを行う場合は、Insert into ~ on duplicate key~を使います。

MySQL におけるUPSERT文の基本構文の画像

要約すると、Insert Into ~にインサート文を記述し on duplicate key update ~ にアップデート文を記述します。

MySQLの場合、更新先テーブルに対するキーの存在チェックは、プライマリキーで判断しているため、他のデータベースの様にユニークキーを記述する必要はありません。

これは記述が簡素化できるメリットがありますが、逆にプライマリキー以外のユニークキーを使ったUpsertが出来ないという意味でもあります。

ただ、実際の利用シーンではプライマリキーでUpsertすることがほとんどなので、それほど問題にはならないかと思います。

value 句で指定した値を update句で参照するには、values(列名) と記述しますが、table_A にエイリアス名を付けて参照することも可能です。直接テーブル名を指定してもうまく動作しませんのでご注意ください

Insert Into の後に value ではなく select * from テーブル名 を記述すると、2つのテーブル間でのUpsert が行えます。

検証データ

本記事のUpsert文を実際に試してみたい場合は、事前に以下のSQLを実行しておいて下さい。

1行をUpsertする

1レコードのUpsertイメージ図

下記は、サンプルテーブルに (1,’2023/01/01 09:00:00′,10000,15) のデータをInsertし、レコードが既に存在していれば 数量(count)を 15にUpdate するサンプルです。

複数行をまとめてUpsertする

MySQLでは、1回のInsert文で複数行(複数レコード)をまとめて登録する マルチインサート文が利用できますが、マルチインサート文に続けて ON DUPLICATE KEY UPDATE ~ と記述することで、複数行をまとめてUpsertできます。

この例では、on duplicate key updat ~ では values 関数を使って Updateの値を参照しています。こうすることで、Insertに失敗した場合、各Insert ごとの count の値が Update されるようになります。

MySQL では、on duplicate key updat count=5 の様に、直接値を指定することも可能ですが、この場合はInsertに失敗したレコード(つまりUpdateされるレコード)は一律 5 で更新されてしまいます(PostgreSQLやSQLiteの場合はエラーになる)。

また、1つのInsert文の中に、キーが重複するデータが含まれている場合は後勝ち(1回目でInsertされ、2回目でUpdateされるイメージ)になります(PostgreSQLやSQLiteの場合はエラーになる)。

テーブル間でUpsertする

2つのテーブル同士のUpsertイメージ図

2つのテーブルAとBがあって、Bの内容をAにUpsertしたい場合、select * from を使います。

insert into テーブル名A select * from テーブル名B

下記は old_oders から oders へ Upsertするサンプルです。

UpsertでInsertだけ行う(Updateしない)

Upsertでなければインサート、あればなのもしない動作のイメージ画像

なければインサート、あれば何もしたくない場合は、insert ignore ~ を使用します。

Pythonのサンプル

次に、参考例としてDataFrameの内容からUpsert文を生成するPythonのサンプルプログラムをご紹介します。

使い方のサンプル

create_upsert にDataFrame、テーブル名、ユニークキーのリストを指定するだけです。

尚、下記サンプルで生成したUpsert文は、最初に紹介したサンプルテーブルに対して実行が可能です。

関数のソースコード

以下は、関数本体のソースコードです。数値以外のデータ(文字列、日付)はシングルクォートで括る必要がありますが、それを dtype が ‘object’ か否かで判断しています。

また、insert into ~ values ~ の values に渡す列の値に ‘nan’ という文字列が含まれている場合、無条件に NULL に置換しています。ほとんど発生しないと思いますが、列の値に ‘nan’ という文字列がが含まれていると、NULLに変換されてしまうのでご注意下さい。

まとめ

今回はMySQLのUpsertに焦点を絞り、掘り下げてみました。

MySQLでUpsertを行う場合、Insert into ~ にインサート文を、do ~ に update 文を記述します。

また、キーが存在した場合に何もしなくない場合は、insert ignore into ~ と記述します。

また、update ~ にvalues(列名) と記述することで、これから Insertしようとする値が参照できます。

MySQLでUpsertが必要になった時は、是非この記事を参考にしてください。

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

コメント

コメントする

目次