勉強したことのメモ

Webエンジニア / プログラマが勉強したことのメモ。

ON DUPLICATE KEY UPDATE後にinsert_idを取得すると結果が0になる

   2023/11/17  MySQL データベース

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が出力された。

 

原因

以下記事を見た感じデータの登録・更新が無い場合、このような挙動になるっぽい。

https://stackoverflow.com/questions/25195902/php-mysqli-insert-id-0-after-insert-or-update-should-i-read-first

https://stackoverflow.com/questions/35562796/after-insert-on-duplicate-update-i-got-0-from-mysqli-insert-id

今回の場合、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句は本件とは別にオートインクリメントの値が飛んだりもするし微妙に使い勝手が悪いなぁと思った。もうちょっと使いどころを考えないといけなさそう。

 - MySQL データベース

  関連記事

MySQLで一連の処理を関数のように実行できるストアドプロシージャの設定と利用方法

MySQL関連のサイトを見ていた際にストアドプロシージャが云々というページがあっ ...

MySQLでJOINの高速化

JOINで無茶苦茶悩んだのでメモ。やりたかった事は、MySQLで テーブルAには ...

MySQLでデータの集計時に1日の起点を任意の時間に変更する方法

MySQLで1日毎の集計を行う際、例えば2020/01/01のデータは2020/ ...

MySQLのエラーログ取得関数

場合によってログに出力させたい値が異なる場合があったのでメモ。 function ...

InnoDBのオートインクリメントで抜け番が発生する原因について

MySQLのInnoDBでIDというカラムにオートインクリメント設定していたが、 ...