MySQLで「既存ならIDを取得し, なければ保存してからIDを取得」を複数一括でやる方法

Question

実現したいこと

MySQLで「既存ならIDを取得し, なければ保存してからIDを取得」を複数一括でやりたいです。
既存判定はユニーク制約のついたカラム(name, kind)について走査します。

前提

テーブルは以下とします。

-- テーブル
CREATE TABLE IF NOT EXISTS test_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    kind VARCHAR(255),
    age INT,
    UNIQUE KEY unique_name_kind (name, kind)
);

-- 初期データ
INSERT INTO test_users
    (name, kind, age)
VALUES
    ('John', 'TypeA', 25),
    ('John', 'TypeB', 30),
    ('Alice', 'TypeB', 28);

発生している問題

「一括」ができません。「一回のクエリ」でやりたいのです…。

該当のソースコード

まずこちらを書きました。しかし見た感じのクエリは$sqlの一つですが、execute()を複数回やっているので「一括」とは呼べないよなぁと思い、質問させて頂きました。

    // 方法1
    // execute() を複数回
    // ON DUPLICATE KEY UPDATE は LAST_INSERT_ID を指定
    final public function bulkInsertAndIfDuplicateGetIds1(array $users): array
    {
        // 返り値のID配列
        $ids = [];

        // INSERT文
        $sql = 'INSERT INTO test_users (name, kind, age)
        VALUES (:name, :kind, :age)
        ON DUPLICATE KEY UPDATE
        id = LAST_INSERT_ID(id)';

        try {
            $stmt = $this->pdo->prepare($sql);

            foreach ($users as $user) {
                $stmt->execute($user);
                $ids[] = $this->pdo->lastInsertId();
            }

            return $ids;
        } catch (\PDOException $e) {
            echo $e->getMessage();
        }
    }

試したこと

execute()を1回とし、array_fill()を使い「一括」を試みました。しかし「既存ならIDを取得」ができず、新規IDのみの取得となりました。

    // 方法2
    // execute() を1回
    // ON DUPLICATE KEY UPDATE は LAST_INSERT_ID を指定
    final public function bulkInsertAndIfDuplicateGetIds2(array $users): array
    {
        // 返り値のID配列
        $ids = [];

        // INSERT文
        $placeholders = implode(', ', array_fill(0, count($users), '(?, ?, ?)'));
        $sql = 'INSERT INTO test_users (name, kind, age)
        VALUES ' . $placeholders . '
        ON DUPLICATE KEY UPDATE
        id = LAST_INSERT_ID(id)';

        try {
            $stmt = $this->pdo->prepare($sql);

            $params = [];
            foreach ($users as $user) {
                $params = array_merge($params, array_values($user));
            }
            $stmt->execute($params);

            for ($i = 0; $i < count($users); $i++) {
                $ids[] = $this->pdo->lastInsertId();
            }

            return $ids;
        } catch (\PDOException $e) {
            echo $e->getMessage();
        }
    }

続いて、ON DUPLICATE KEY UPDATE で LAST_INSERT_ID を指定することが悪いのかと考え、ユニークキーを指定してみるように変更しました。しかしこちらも上と同様に新規IDのみの取得となりました。

    // 方法3
    // execute() を1回
    // ON DUPLICATE KEY UPDATE は ユニークキーを指定
    final public function bulkInsertAndIfDuplicateGetIds3(array $users): array
    {
        // 返り値のID配列
        $ids = [];

        // INSERT文
        $placeholders = implode(', ', array_fill(0, count($users), '(?, ?, ?)'));
        $sql = 'INSERT INTO test_users (name, kind, age)
        VALUES ' . $placeholders . '
        ON DUPLICATE KEY UPDATE
        name = VALUES(name),
        kind = VALUES(kind)';

        try {
            $stmt = $this->pdo->prepare($sql);

            $params = [];
            foreach ($users as $user) {
                $params = array_merge($params, array_values($user));
            }
            $stmt->execute($params);

            for ($i = 0; $i < count($users); $i++) {
                $ids[] = $this->pdo->lastInsertId();
            }

            return $ids;
        } catch (\PDOException $e) {
            echo $e->getMessage();
        }
    }

実行用のコードはこちらのリンクにございます。（__construct()内のnew PDO()をご指定頂ければ実行できます。）
https://3v4l.org/pZvuD

もしかして本件において「一括」というのは無理な話なのでしょうか…？
もし無理なのだとしたら、最速とか皆様のオススメなどを知りたいです。

ツールのバージョン

PHP は 8.2 です。
MySQL はやや古めで 5.7 です。
よろしくお願い致します。

追記

「IDを取得」と「一括」について意味がわかりにくいと指摘を受けましたのでご説明させていただきます。

「IDを取得」については bulkInsertAndIfDuplicateGetIds1() が成功しています。リンク34行目で $result1 に ["1","5","3"] が得れることで「IDを取得」が出来ているという意味です。（既存であった 1,3 を得て、さらに新規に 5 も得れています。）

あとはこれを「一括」でやりたいという意図で、 bulkInsertAndIfDuplicateGetIds2(), bulkInsertAndIfDuplicateGetIds3() を試しました。

そして「一括」かどうかは下記の違いを意図しておりました。

-- 「一括」である
INSERT INTO test_datas(xxx)
VALUES ('aaa'), ('bbb');

-- 「一括」ではない
INSERT INTO test_datas(xxx)
VALUES ('aaa');
INSERT INTO test_datas(xxx)
VALUES ('bbb');

なので bulkInsertAndIfDuplicateGetIds1() は「一括」ではありませんが、「IDを取得」はできています。
逆に bulkInsertAndIfDuplicateGetIds2(), bulkInsertAndIfDuplicateGetIds3() は「一括」ですが、しかし「IDを取得」はできておらず、 ["4","4","4"] になってしまいます。

以上のように「IDを取得」と「一括」をイメージしており、この両立を目指しているという次第です。

Comments

[ikedas]

「一括」というのがよくわかりません。「一括」ができたとした場合に得られる結果を例示してください。

[yambejp]

「IDを取得」の意味がわかりません
insert/updateはあくまで挿入更新なので取得とは別次元の話だと思いますが？
具体的なサンプルをもちいてどういう場合にどうしたいかを説明してください

[ikedas]

UPDATE id = LAST_INSERT_ID(id) はidを更新する意図ではなくLAST_INSERT_IDに既存行のid値をセットするという副作用を起こしたいのだと思います (既存行がなければINSERTが成功して新しいid値がセットされる)。つまりLAST_INSERT_IDに目的の値がセットされることを「IDを取得」と表現していると思われます。
しかしそうすると「一括」とは……? やっぱりサンプルを例示してもらわないとわからんですね。

[origa3]

コメントありがとうございます。

「一括」というのは「一回のクエリ」でやりたいということです。求める結果は該当のソースコードですが、これは「一括」になっておらず納得感が得れません。「IDを取得」はできているが遅そうという意味です。他方で試したことのソースコードは「一括」にはなっているものの「IDを取得」ができておりません。

「IDを取得」というのは「既存ならIDを取得し, なければ保存してからIDを取得」ということで、もう少し詳しく申し上げますと「ユニーク制約を課した (name, kind) のカラムが既存ならそのIDを取得し、なければ新たにINSERTし、そのINSERTされたIDを取得」ということです。

伝わりますでしょうか…。

[ikedas]

具体的にサンプルデータを用意して、「このようなデータが入っているテーブルに対してある処理を行うとこのようなデータが出力されることを『一括』という」「一方このようなデータが出力されたら『一括』ではない」というふうに、具体例で説明してください。
また、「IDを取得」についても同様にサンプルデータに対してどのような処理をして何がどこに出力されることなのかを、具体例で説明してください。

「一括」とか「取得」という言葉の表すものを質問者さんにはイメージできるのでしょうけれど、我々は質問者さんの頭の中まで見えませんので、このままではイメージできません。

[origa3]

失礼いたしました。ご説明させて頂きます。

まず「IDを取得」については bulkInsertAndIfDuplicateGetIds1() が成功しています。リンク34行目で $result1 に ["1","5","3"] が得れることで「IDを取得」が出来ているという意味です。（既存であった 1,3 を得て、さらに新規に 5 も得れています。）

あとはこれを「一括」でやりたいという意図で、 bulkInsertAndIfDuplicateGetIds2(), bulkInsertAndIfDuplicateGetIds3() を試しました。

そして「一括」かどうかは下記の違いを意図しておりました。

-- 「一括」である
INSERT INTO test_datas(xxx)
VALUES ('aaa'), ('bbb');

-- 「一括」ではない
INSERT INTO test_datas(xxx)
VALUES ('aaa');
INSERT INTO test_datas(xxx)
VALUES ('bbb');

なので bulkInsertAndIfDuplicateGetIds1() は「一括」ではありませんが、「IDを取得」はできています。
逆に bulkInsertAndIfDuplicateGetIds2(), bulkInsertAndIfDuplicateGetIds3() は「一括」ですが、しかし「IDを取得」はできておらず、 ["4","4","4"] になってしまいます。

以上のように「IDを取得」と「一括」をイメージしており、この両立を目指しているという次第です。

[ikedas]

質問文を編集して書いていただけますか。コメント欄は隠れてしまうので見落とされます。

Answer 1

MariaDB 10.5.0以降ではINSERT...RETURNING文というのがあり、「insertされたすべての行の指定した列を返す」(... returns the listed columns for all the rows that are inserted) とされています。一方でこの文にはON DUPLICATE KEY UPDATE句も書けるので、「insertされたすべての行」にupdateされた行も含まれるのなら期待通りの動作になりそうです。

以下はMariaDB 10.6.12で確認しました。

MariaDB [test]> select * from test_users;
+----+-------+-------+------+
| id | name  | kind  | age  |
+----+-------+-------+------+
|  1 | John  | TypeA |   25 |
|  2 | John  | TypeB |   30 |
|  3 | Alice | TypeB |   28 |
+----+-------+-------+------+
3 rows in set (0.001 sec)

MariaDB [test]> INSERT INTO test_users
    ->     (name, kind, age)
    -> VALUES
    ->     ('John', 'TypeA', 25),
    ->     ('Joan', 'TypeB', 20),
    ->     ('Adel', 'TypeC', 40)
    -> ON DUPLICATE KEY UPDATE
    ->     name = VALUES(name)
    -> RETURNING id;
+----+
| id |
+----+
|  1 |
|  4 |
|  5 |
+----+
3 rows in set (0.003 sec)

MariaDB [test]> select * from test_users;
+----+-------+-------+------+
| id | name  | kind  | age  |
+----+-------+-------+------+
|  1 | John  | TypeA |   25 |
|  2 | John  | TypeB |   30 |
|  3 | Alice | TypeB |   28 |
|  4 | Joan  | TypeB |   20 |
|  5 | Adel  | TypeC |   40 |
+----+-------+-------+------+
5 rows in set (0.001 sec)

MariaDB [test]>

できているようです。期待した通り、既存行 (id=1) と追加された行 (id=4, 5) のidが出力されています (個人的にはUPDATEで実際には更新しない式を書いているのがちょっと気持ち悪いですが)。

ただ、MySQLには (8.0以降でも5.7以前でも) 同様の機能はないようです。ほかにPostgreSQLでも9.6以降はRETURNING句を書けますがON DUPLICATE KEY UPDATEはないです、9.5以降でON DUPLICATE KEY UPDATEと同様の効果のあるON CONFLICT DO UPDATE句が書けます。DB2もデータ変更文から結果セットを得ること (Pipelined DMLというらしい) ができるようですがやはりON DUPLICATE KEY UPDATEのようなものはないです。つまり、ほとんどの場合はトランザクションを使うなどして複数文で実現するしかないでしょうし、そのほうが汎用性が高いと思います。

2023-11-22 コメントに基づき訂正

Comments

[origa3]

なんと見事なご回答でしょうか…。大変有意義な情報とご意見を誠にありがとうございます。
なかなか要領を得たご説明ができずご迷惑をおかけいたしました。

[sazi]

本題には関係ないですが・・
> PostgreSQLでも9.6以降はRETURNING句を書けますがON DUPLICATE KEY UPDATEはないです。
同等に使用するなら、「insert ～ ON CONFLICT() DO UPDATE」ですね。
但し、更新時にRETURNINGでの返却を必須にするには更新も必ず行うようにする必要がありますが。

[ikedas]

なるほどできますね。ただいずれにせよMariaDBと同じく実装依存なので、個人的には使いたくないですが。
更新が必須になるのはyambejpさんも私も指摘しているとおりで、そこもなんかいけてない感じですね。

Answer 2

やりたいことの意図がわかりませんが、primary keyを更新するのは意味がないのでやめたほうがよいでしょう。
もしデータの順序を担保したいということであればidを更新するより更新日時などのカラムをつくっておきそこを更新すればよいような気がします。

参考

CREATE TABLE IF NOT EXISTS test_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    kind VARCHAR(255),
    age INT,
    created datetime not null default now(),
    updated datetime null,
    UNIQUE KEY unique_name_kind (name, kind)
);

INSERT INTO test_users
    (name, kind, age)
VALUES
    ('John', 'TypeA', 25),
    ('John', 'TypeB', 30),
    ('Alice', 'TypeB', 28);

insert/update

INSERT INTO test_users
    (name, kind, age)
VALUES
    ('John', 'TypeA', 0),
    ('John', 'TypeB', 30),
    ('John', 'TypeC', 100)
ON DUPLICATE KEY 
UPDATE 
age=values(age),
updated=now()
;

Comments

[origa3]

ご回答ありがとうございます。データの順序を担保したいという意図はございませんでした。primary keyを更新しているのは、それによって「IDを取得」を成そうという意図です。目的の一つに「既存ならIDを取得」があるので、primary keyを更新しないと（`ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id)`をしないと）、`lastInsertId()`で得れないだろうと思っています。

[yambejp]

ちょっと意味不明です。「IDを取得」が結局なにを意味しているかわからないので適切な回答ができません。

LAST_INSERT_IDの処理が意味不明でした参考データを追記しておきました。単純にinsert/updateを処理すれば良いんですよね？

[yambejp]

基本的に複数のlastInsertIdという考え方が間違いでしょう
更新日時をもとに最新データはとればよいのですから

[origa3]

日本語だけの説明にすれば良かったですね。生兵法で LAST_INSERT_ID を記述したせいでいろいろな誤解を与えるような質問になっていたかもしれません。そんなわかりにくい質問にお答え頂きありがとうございます。