Online PHP editor | perf 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(); } } }

Version	System time (s)	User time (s)	Memory (MiB)
8.4.12	0.007	0.002	19.48
8.4.11	0.014	0.007	22.61
8.4.10	0.014	0.007	17.84
8.4.9	0.009	0.009	20.44
8.4.8	0.011	0.007	18.75
8.4.7	0.013	0.008	18.90
8.4.6	0.013	0.007	20.29
8.4.5	0.011	0.007	19.72
8.4.4	0.003	0.006	17.93
8.4.3	0.015	0.006	18.96
8.4.2	0.019	0.000	20.82
8.4.1	0.010	0.010	19.63
8.3.25	0.013	0.006	18.80
8.3.24	0.007	0.011	16.78
8.3.23	0.003	0.005	16.54
8.3.22	0.014	0.004	19.13
8.3.21	0.009	0.007	18.38
8.3.20	0.007	0.002	16.54
8.3.19	0.013	0.006	16.78
8.3.18	0.014	0.005	16.55
8.3.17	0.012	0.008	18.93
8.3.16	0.012	0.006	17.32
8.3.15	0.010	0.010	17.16
8.3.14	0.007	0.007	16.68
8.3.13	0.003	0.006	18.39
8.3.12	0.009	0.006	20.80
8.3.11	0.003	0.006	20.94
8.3.10	0.000	0.009	16.88
8.3.9	0.003	0.005	16.41
8.3.8	0.006	0.003	18.43
8.3.7	0.000	0.016	16.74
8.3.6	0.013	0.010	18.31
8.3.5	0.007	0.011	16.73
8.3.4	0.007	0.007	20.23
8.3.3	0.011	0.004	21.94
8.3.2	0.005	0.003	24.18
8.3.1	0.000	0.008	24.66
8.3.0	0.004	0.004	26.16
8.2.29	0.009	0.011	16.66
8.2.28	0.003	0.005	20.40
8.2.27	0.015	0.003	17.25
8.2.26	0.009	0.009	16.74
8.2.25	0.008	0.000	18.54
8.2.24	0.004	0.004	18.84
8.2.23	0.007	0.003	22.58
8.2.22	0.000	0.008	24.06
8.2.21	0.014	0.004	26.77
8.2.20	0.008	0.004	18.41
8.2.19	0.009	0.006	16.63
8.2.18	0.010	0.010	18.21
8.2.17	0.009	0.009	19.21
8.2.16	0.014	0.000	22.96
8.2.15	0.000	0.008	25.66
8.2.14	0.004	0.004	24.66
8.2.13	0.004	0.004	26.16
8.2.12	0.004	0.004	19.77
8.2.11	0.012	0.009	22.27
8.2.10	0.019	0.006	20.93
8.2.9	0.024	0.012	18.96
8.2.8	0.023	0.003	18.93
8.2.7	0.026	0.006	18.75
8.2.6	0.022	0.006	18.81
8.2.5	0.019	0.004	20.06
8.2.4	0.018	0.004	18.78
8.2.3	0.017	0.010	18.68
8.2.2	0.014	0.003	19.12
8.2.1	0.012	0.003	18.92
8.2.0	0.007	0.007	19.21
8.1.33	0.008	0.010	16.18
8.1.32	0.008	0.011	18.18
8.1.31	0.004	0.004	16.66
8.1.30	0.012	0.004	20.29
8.1.29	0.009	0.003	18.88
8.1.28	0.015	0.003	25.92
8.1.27	0.005	0.005	24.66
8.1.26	0.000	0.008	26.35
8.1.25	0.014	0.004	28.09
8.1.24	0.013	0.000	18.58
8.1.23	0.015	0.000	18.49
8.1.22	0.009	0.006	18.63
8.1.21	0.010	0.003	19.73
8.1.20	0.005	0.009	18.54
8.1.19	0.015	0.000	18.42
8.1.18	0.012	0.003	18.42
8.1.17	0.003	0.010	18.66
8.1.16	0.011	0.003	18.85
8.1.15	0.004	0.008	20.44
8.1.14	0.005	0.008	18.56
8.1.13	0.011	0.004	18.61
8.1.12	0.000	0.012	18.50
8.1.11	0.009	0.003	19.62
8.1.10	0.005	0.009	18.55
8.1.9	0.007	0.007	18.62
8.1.8	0.025	0.000	18.50
8.1.7	0.016	0.003	18.62
8.1.6	0.015	0.000	18.86
8.1.5	0.013	0.003	18.50
8.1.4	0.009	0.006	18.73
8.1.3	0.008	0.005	18.92
8.1.2	0.007	0.010	18.76
8.1.1	0.007	0.007	18.79
8.1.0	0.010	0.005	18.87
8.0.30	0.011	0.004	17.94
8.0.29	0.012	0.004	18.06
8.0.28	0.007	0.007	17.93
8.0.27	0.009	0.006	17.90
8.0.26	0.007	0.004	18.44
8.0.25	0.015	0.000	17.83
8.0.24	0.000	0.014	18.09
8.0.23	0.012	0.000	18.28
8.0.22	0.015	0.000	17.96
8.0.21	0.010	0.003	18.07
8.0.20	0.012	0.003	17.99
8.0.19	0.012	0.000	18.28
8.0.18	0.011	0.003	17.99
8.0.17	0.032	0.000	17.95
8.0.16	0.016	0.006	17.82
8.0.15	0.009	0.006	18.00
8.0.14	0.012	0.003	17.99
8.0.13	0.012	0.000	18.10
8.0.12	0.012	0.003	18.01
8.0.11	0.009	0.006	18.49
8.0.10	0.010	0.005	17.85
8.0.9	0.015	0.000	18.02
8.0.8	0.011	0.004	17.88
8.0.7	0.014	0.003	17.95
8.0.6	0.017	0.006	18.17
8.0.5	0.029	0.003	17.99
8.0.3	0.015	0.000	18.23
8.0.2	0.021	0.006	17.98
8.0.1	0.017	0.009	18.15