Inserts and IDs

Inserts and IDs

Inserting Rows and Last Insert IDs

After an INSERT into a table with an AUTO_INCREMENT key, lastInsertId() returns the new key, as a string, from MySQL 524 's per-connection LAST_INSERT_ID(), so other visitors' inserts cannot interfere. A unique-key violation throws SQLSTATE 23000, a class that also covers foreign-key and NOT NULL violations, so check MySQL's code 1062 as well before you report "already registered":

A new key, then a duplicate emailPHP

<?php
$pdo = require 'db.php';
$add = $pdo->prepare('INSERT INTO customers (email, name, country) VALUES (?, ?, ?)');
$add->execute(['ivo@example.com', 'Ivo Petrov', 'BG']);
var_dump($pdo->lastInsertId());
try {
  $add->execute(['ana@example.com', 'Ana Again', 'BR']);   // email is UNIQUE
} catch (PDOException $e) {
  if ($e->getCode() !== '23000' || $e->errorInfo[1] !== 1062) throw $e;
  echo "duplicate: {$e->errorInfo[2]}\n";
}

Output

string(1) "9"
duplicate: Duplicate entry 'ana@example.com' for key 'customers.email'

Catching the duplicate beats checking with a SELECT first, since two requests can both pass the check and then race; the unique index is the only reliable referee. To update instead of failing, use INSERT ... ON DUPLICATE KEY UPDATE (Subsection 3.2.6). A multi-row INSERT reports its first new key. Keys are never reused: the failed duplicate consumed 10, so the next customer gets 11.

MENU