Online PHP editor | output for pZvuD

<?php /*---------------------------------------------------------------------- ユニークキーの値が既存ならIDを取得し, なければ保存してからIDを取得という処理において早い方法はなにか？以下メソッドを比較してテスト bulkInsertAndIfDuplicateGetIds1() bulkInsertAndIfDuplicateGetIds2() bulkInsertAndIfDuplicateGetIds3() ----------------------------------------------------------------------*/ $testUser = new TestUser(); // 挿入データ // initRows() でINSERTされるデータに対し2番目だけが新規なので // 挿入を試みると ["1","5","3"] が返ることを期待するが, 結果は下記 echo の通り $users = [ ['name' => 'John', 'kind' => 'TypeA', 'age' => 25], // 既存 ['name' => 'Bob', 'kind' => 'TypeC', 'age' => 22], // 新規 ['name' => 'Alice', 'kind' => 'TypeB', 'age' => 30], // 既存 ]; // 挿入実行 $testUser->initRows(); $result1 = $testUser->bulkInsertAndIfDuplicateGetIds1($users); $testUser->initRows(); $result2 = $testUser->bulkInsertAndIfDuplicateGetIds2($users); $testUser->initRows(); $result3 = $testUser->bulkInsertAndIfDuplicateGetIds3($users); // 結果確認 echo 'result1' . json_encode($result1); // ["1","5","3"] echo 'result2' . json_encode($result2); // ["4","4","4"] echo 'result3' . json_encode($result3); // ["4","4","4"] // テストユーザークラス final class TestUser { private \PDO $pdo; public function __construct() { $host = 'localhost'; $dbname = 'database_name'; $user = 'username'; $password = 'password'; $dsn = "mysql:host=$host;dbname=$dbname;charset=utf8mb4"; $this->pdo = new PDO($dsn, $user, $password); } // 初期状態として3レコード入れておく final public function initRows(): void { try { // テーブル作成 $this->pdo->query(" 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) ) "); // レコード削除 $this->pdo->query(" TRUNCATE TABLE test_users; ALTER TABLE test_users AUTO_INCREMENT = 1; "); // 3レコード入れておく $this->pdo->query(" INSERT INTO test_users (name, kind, age) VALUES ('John', 'TypeA', 25), ('John', 'TypeB', 30), ('Alice', 'TypeB', 28) "); } catch (\PDOException $e) { echo $e->getMessage(); } } // 方法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(); } } // 方法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(); } } // 方法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(); } } }