PHP MySQL 插入数据
Inserting one row with a prepared statement, reading the auto-increment ID, handling duplicate keys.
Key PointsInsert with prepared statements; read the generated key via `lastInsertId()`.
Insert one row
PHP
<?php
$pdo = new PDO($dsn, $user, $pass, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->execute(["Tom", "tom@example.com"]);
echo "新增 ID: " . $pdo->lastInsertId() . "\n";
echo "影响行数: " . $stmt->rowCount() . "\n";Output
新增 ID: 1
影响行数: 1lastInsertId() must be called on the same connection right after the insert — it is connection state, not statement state.
Named placeholders
PHP
<?php
$stmt = $pdo->prepare(
"INSERT INTO users (name, email, age) VALUES (:name, :email, :age)"
);
$stmt->execute([
":name" => "Amy",
":email" => "amy@example.com",
":age" => 25,
]);
echo "新增 ID: " . $pdo->lastInsertId() . "\n";Output
新增 ID: 2Named placeholders read better with many columns, and the same name may appear multiple times — positional ? cannot.
Duplicate key
PHP
<?php
$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
try {
$stmt->execute(["Tom", "tom@example.com"]);
echo "插入成功\n";
} catch (PDOException $e) {
if ($e->getCode() === "23000") {
echo "邮箱已存在，请换一个\n";
} else {
throw $e;
}
}Output
邮箱已存在，请换一个23000 is the integrity-constraint SQLSTATE. To pinpoint which key, inspect $e->errorInfo[1]: 1062 is a duplicate entry.
Upsert
PHP
<?php
$sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE
name = VALUES(name),
age = VALUES(age)";
$stmt = $pdo->prepare($sql);
$stmt->execute(["Tom", "tom@example.com", 30]);
echo "执行完毕\n";
echo "影响行数: " . $stmt->rowCount() . "\n";Output
执行完毕
影响行数: 2rowCount() is special here: 1 for insert, 2 for update, 0 when unchanged. Using it to detect insert-vs-update is error-prone.Insert performance
PHP
<?php
// 慢：一行一个语句，1000 次网络往返
// 快：一条语句插多行，1 次往返
$rows = [
["Bob", "bob@example.com", 28],
["Cara", "cara@example.com", 31],
["Dan", "dan@example.com", 22],
];
$values = implode(",", array_fill(0, count($rows), "(?,?,?)"));
$flat = array_merge(...$rows);
$stmt = $pdo->prepare("INSERT INTO users (name, email, age) VALUES $values");
$stmt->execute($flat);
echo "插入行数: " . $stmt->rowCount() . "\n";Output
插入行数: 3array_merge(...$rows) flattens the 2D array to match the ordered placeholders — the standard bulk-insert idiom.
Huge batches hit
max_allowed_packet. Batch 500–1000 rows per statement.插入一行
PHP
<?php
$pdo = new PDO($dsn, $user, $pass, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->execute(["Tom", "tom@example.com"]);
echo "新增 ID: " . $pdo->lastInsertId() . "\n";
echo "影响行数: " . $stmt->rowCount() . "\n";Output
新增 ID: 1
影响行数: 1lastInsertId() 要在同一条连接上、紧跟插入之后调用。它返回的是连接级别的状态，不是语句级别的。
命名占位符
PHP
<?php
$stmt = $pdo->prepare(
"INSERT INTO users (name, email, age) VALUES (:name, :email, :age)"
);
$stmt->execute([
":name" => "Amy",
":email" => "amy@example.com",
":age" => 25,
]);
echo "新增 ID: " . $pdo->lastInsertId() . "\n";Output
新增 ID: 2命名占位符比 ? 好读，尤其是字段多的时候。同一个命名占位符可以重复出现（? 不行，得按顺序给值）。
唯一键冲突
PHP
<?php
$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
try {
$stmt->execute(["Tom", "tom@example.com"]);
echo "插入成功\n";
} catch (PDOException $e) {
if ($e->getCode() === "23000") {
echo "邮箱已存在，请换一个\n";
} else {
throw $e;
}
}Output
邮箱已存在，请换一个23000 是 SQLSTATE 的完整性约束冲突码。要区分具体是哪个键冲突，看 $e->errorInfo[1]：1062 是唯一键重复。
冲突时改走更新
PHP
<?php
$sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE
name = VALUES(name),
age = VALUES(age)";
$stmt = $pdo->prepare($sql);
$stmt->execute(["Tom", "tom@example.com", 30]);
echo "执行完毕\n";
echo "影响行数: " . $stmt->rowCount() . "\n";Output
执行完毕
影响行数: 2ON DUPLICATE KEY UPDATE 的 rowCount() 语义特殊：插入返回 1，更新返回 2，值没变返回 0。用返回值判断「是新增还是更新」很容易踩坑。批量插入的性能
PHP
<?php
// 慢：一行一个语句，1000 次网络往返
// 快：一条语句插多行，1 次往返
$rows = [
["Bob", "bob@example.com", 28],
["Cara", "cara@example.com", 31],
["Dan", "dan@example.com", 22],
];
$values = implode(",", array_fill(0, count($rows), "(?,?,?)"));
$flat = array_merge(...$rows);
$stmt = $pdo->prepare("INSERT INTO users (name, email, age) VALUES $values");
$stmt->execute($flat);
echo "插入行数: " . $stmt->rowCount() . "\n";Output
插入行数: 3array_merge(...$rows) 把二维数组压平成一维，正好对应按顺序排列的占位符。这是批量插入的标准写法。
一次插太多会撞
max_allowed_packet。每批 500 到 1000 行分批提交，比一条巨型语句稳。