例

以下のようなテーブルがあります。

create table users(
  id integer not null primary key auto_increment,
  email varchar(191) not null unique,
  name varchar(191) not null
);

メールアドレスが example@example.com のユーザの名前を John に設定
同時にそのユーザの id を取得

これをSQL1文だけでアトミックに実行する方法を紹介します。

SQL

-- 更新と id の記憶
update users
set id = LAST_INSERT_ID(id),
    name = 'John'
where LAST_INSERT_ID(null) is null
  and email = 'example@example.com'
limit 1;

-- 選択
select LAST_INSERT_ID(); -- これは C API の mysql_insert_id() でも呼べる
                         -- PHP なら PDO::lastInsertId()

解説

MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.14 情報関数

MySQL の LAST_INSERT_ID() は，テーブルに対して最後にインサートされた値を返す関数ではなく， 接続ごとに任意の値をローカル変数のように記憶できる関数 である。
- LAST_INSERT_ID() として引数無しで呼ぶと，記録されている値を返す。
- LAST_INSERT_ID(expr) とすると expr の値を記録する。
- オートインクリメントが設定されている場合， LAST_INSERT_ID(主キー) に相当する処理がインサート処理の後に自動で実行される。
評価順序は where 句が先で，合致した場合のみ set 句が評価される。

これを踏まえた上で先程のSQLをコメントで解説するとこんな感じ↓

update users
set id = LAST_INSERT_ID(id),         -- (2) email での絞り込みが済んだ後に該当する行の id を記憶
    name = 'John'
where LAST_INSERT_ID(null) is null   -- (1) 以前の結果が残っている場合に備えてNULLクリア
  and email = 'example@example.com'
limit 1;

更新後の値が同じで ROW_COUNT() がゼロを返す場合にも set 句が評価さえされていれば有効なので，実際に行の1カラム以上が更新されたかどうかに左右されず，常に結果を取得できます。

注意点

LAST_INSERT_ID() は文の種類を問わずに LAST_INSERT_ID(expr) の値を取得できますが， mysql_insert_id() は insert update のいずれかを実行した場合にしか更新されません！

今回のケースでは update を使っているので問題なく mysql_insert_id() のほう使っちゃってOKです。つまり本当に1文だけで実現できています。

おまけ

Laravel 版

User::query()
    ->whereNull(DB::raw('LAST_INSERT_ID(null)'))
    ->where('email', 'example@example.com')
    ->limit(1)
    ->update([
        'id' => DB::raw('LAST_INSERT_ID(`id`)'),
        'name' => 'John',
    ]);

$id = (int)DB::getPdo()->lastInsertId() ?: null;

この順番のほうが直感的で読みやすい感！

MySQL で UPDATE された行の ID をアトミックに取得する

例

SQL

解説

注意点

おまけ