TL;DR
業務でのプログラミング中に、INSERT ... ON DUPLICATE KEY UPDATE ... という見慣れぬ構文があったので挙動を確認した備忘録です
INSERT ... ON DUPLICATE KEY UPDATEって？
INSERTしようとしているデータが、挿入先のテーブルのUNIQUEインデックスもしくはPRIMARY KEYに重複した値を発生させる行が挿入された場合は、古い行のUPDATEが実行されます。
この時実行されるUPDATEは、 「INSERT ... ON DUPLICATE KEY UPDATE ... 」のUPDATEより後ろの文となります。
挙動を確認します
適当にテーブルを作成
create table hoge (
pk varchar(1) PRIMARY KEY,
hogeval int(1),
updated_at datetime
);後のINSERTにて重複させるためのデータを挿入
mysql> INSERT INTO hoge (pk, hogeval, updated_at) values ('a', 1, '2022-03-20 00:00:00');
Query OK, 1 row affected (0.01 sec)ここの実行結果の「Query OK, 1 row affected (0.01 sec)」を覚えておいてください。
上のSQLを実行後、データを確認する。
mysql> select * from hoge;
+----+---------+---------------------+
| pk | hogeval | updated_at |
+----+---------+---------------------+
| a | 1 | 2022-03-20 00:00:00 |
+----+---------+---------------------+
1 row in set (0.00 sec)この時のupdated_atの値は、2022-03-20 00:00:00となっていることに注目。
もう1件データを挿入
このデータは、INSERT ... ON DUPLICATE KEY UPDATE ...にて重複したデータ以外には影響を与えていないことを確認するためのデータです。
mysql> INSERT INTO hoge (pk, hogeval, updated_at) values ('b', 1, '2022-03-21 00:00:00');
Query OK, 1 row affected (0.01 sec)
mysql> select * from hoge;
+----+---------+---------------------+
| pk | hogeval | updated_at |
+----+---------+---------------------+
| a | 1 | 2022-03-20 00:00:00 |
| b | 1 | 2022-03-21 00:00:00 |
+----+---------+---------------------+
2 rows in set (0.00 sec)最初に登録したデータを合わせて2件になりました。
では、主キーが重複したデータを挿入
mysql> INSERT INTO hoge (pk, hogeval, updated_at) values ('a', 2, '2022-03-22 00:00:00')
ON DUPLICATE KEY UPDATE updated_at = NOW();
Query OK, 2 rows affected (0.00 sec)
mysql> select * from hoge;
+----+---------+---------------------+
| pk | hogeval | updated_at |
+----+---------+---------------------+
| a | 1 | 2022-03-29 22:38:55 |
| b | 1 | 2022-03-21 00:00:00 |
+----+---------+---------------------+
2 rows in set (0.00 sec)はい、主キー「a」が重複していた行のupdated_atが「2022-03-29 22:38:55」に更新されています。
そして気になることがもう一つ。
INSERT ... ON DUPLICATE KEY UPDATE ... の実行結果が「Query OK, 2 rows affected (0.00 sec)」となぜか2rowsとなっていること。
公式リファレンスを見るとこう書かれています。
https://dev.mysql.com/doc/refman/5.6/ja/insert-on-duplicate.html
ON DUPLICATE KEY UPDATEを使用した場合、行ごとの影響を受けた行の値は、その行が新しい行として挿入された場合は 1、既存の行が更新された場合は 2、既存の行がその現在の値に設定された場合は 0 です。mysqld への接続時にCLIENT_FOUND_ROWSフラグをmysql_real_connect()に指定すると、既存の行がその現在の値に設定された場合の影響を受けた行の値は (0 ではなく) 1 になります。
以上です。おつかれさまでした！
