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

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