ON DUPLICATE KEY UPDATE後にinsert_idを取得すると結果が0になる
2023/11/17
MySQLでON DUPLICATE KEY UPDATE句指定し、データの登録・更新した後に「$mysqli->insert_id」でIDを取得すると結果が0になるというケースがあった。毎回なるわけではなく同じデータをほぼ同時に登録・更新した際に発生しているっぽい。以下に原因と対応策をメモ。
データベース内容
以下のようなデータベースを用意した。idに主キーを設定している。
+----+-----------+-------+---------------------+---------------------+ | id | name | point | regist_ymd | update_ymd | +----+-----------+-------+---------------------+---------------------+ | 1 | 鈴木 | 100 | 2023-11-02 18:00:54 | 2023-11-02 18:00:54 | | 2 | 山田 | 50 | 2023-11-02 18:01:05 | 2023-11-02 18:01:05 | | 3 | 佐々木 | 70 | 2023-11-02 18:01:22 | 2023-11-02 18:01:22 | +----+-----------+-------+---------------------+---------------------+
現象
$sql = ' INSERT INTO `test_table` ( `id`, `name`, `point`, `regist_ymd`, `update_ymd` ) VALUES ( 2, "鈴木", 1000, now(), now() ) ON DUPLICATE KEY UPDATE `name` = "鈴木編集", `point` = 2000, `update_ymd` = now() '; $row = $mysqli->query($sql); $id = $mysqli->insert_id; var_dump($id); #int(2)が出力される $row = $mysqli->query($sql); $id = $mysqli->insert_id; var_dump($id); #int(0)が出力される
感覚的には2回目のvar_dumpで2が出力されそうなのに0が出力された。
原因
以下記事を見た感じデータの登録・更新が無い場合、このような挙動になるっぽい。
今回の場合、1回目はupdate_ymd(更新日時)が更新されるため正常に動作するが、2回目はupdate_ymdも全て同じ内容のため発生している模様。
対応策
こちらの記事を見ると以下のようにid部分を更新したように見せる形が良いらしい。
$sql = ' INSERT INTO `insert_id_test` ( `id`, `name`, `point`, `regist_ymd`, `update_ymd` ) VALUES ( 2, "鈴木", 1000, now(), now() ) ON DUPLICATE KEY UPDATE `id` = LAST_INSERT_ID(`id`), `name` = "鈴木編集", `point` = 2000, `update_ymd` = now() ';
「`id` = LAST_INSERT_ID(`id`)」部分を追記すると正常に動作した。
後は力業だけどPHPのsleepとかで数秒開けて処理すればupdate_ymdが変わるので正常に動作する。
所感
ON DUPLICATE KEY UPDATE句は本件とは別にオートインクリメントの値が飛んだりもするし微妙に使い勝手が悪いなぁと思った。もうちょっと使いどころを考えないといけなさそう。
関連記事
-
-
SQLで同じ値を複数のレコードに対してUPDATE
やりたかった事は、複数のレコードに対して 同じ値に更新したいというもの。 $sq ...
-
-
MySQLでROW_NUMBER関数を使用してグループ毎に連番を設定する方法
以前にMySQLでオートインクリメントを使用せずにグループ毎に連番を設定する方法 ...
-
-
MySQLでテキスト(日本語、半角英数字)を暗号化・復号化する方法
MySQLでデータを登録する際に暗号化したいというケースがあった。対象のデータは ...
-
-
MySQLにてSELECT時にdate型のカラムから干支(十二支)を計算し表示する方法
MySQLにdate型のカラムに誕生年月日が格納されており、そこから干支(十二支 ...
-
-
MySQLにてUUIDを発行しプライマリキー(主キー)にする方法
普段MySQLでプライマリキー(主キー)を使う場合、オートインクリメントを利用し ...