PHP MySQL 插入多条数据
Multi-row INSERT, transaction wrapping, and rollback on failure.
Key PointsA bulk insert should be all-or-nothing — a transaction guarantees it.
Multi-row insert
PHP
<?php
$pdo = new PDO($dsn, $user, $pass, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$rows = [
["Amy", "amy@example.com", 25],
["Bob", "bob@example.com", 28],
["Cara", "cara@example.com", 31],
];
$placeholders = implode(",", array_fill(0, count($rows), "(?,?,?)"));
$stmt = $pdo->prepare("INSERT INTO users (name, email, age) VALUES $placeholders");
$stmt->execute(array_merge(...$rows));
echo "批量插入 " . $stmt->rowCount() . " 行\n";
echo "最后 ID: " . $pdo->lastInsertId() . "\n";Output
批量插入 3 行
最后 ID: 3One statement for three rows means one round trip and one parse — an order of magnitude faster than looping execute.
Wrapping in a transaction
PHP
<?php
$rows = [
["Amy", "amy@example.com", 25],
["Bob", "bob@example.com", 28],
["Cara", "cara@example.com", 31],
];
$pdo->beginTransaction();
try {
$placeholders = implode(",", array_fill(0, count($rows), "(?,?,?)"));
$stmt = $pdo->prepare("INSERT INTO users (name, email, age) VALUES $placeholders");
$stmt->execute(array_merge(...$rows));
$pdo->commit();
echo "提交成功，插入 " . $stmt->rowCount() . " 行\n";
} catch (Throwable $e) {
$pdo->rollBack();
echo "已回滚: " . $e->getMessage() . "\n";
}Output
提交成功，插入 3 行A single INSERT is already atomic. Transactions matter for combinations — inserting an order while decrementing stock must succeed together.
Batching in a loop
PHP
<?php
$all = [];
for ($i = 1; $i <= 2500; $i++) {
$all[] = ["user$i", "user$i@example.com", 20 + $i % 30];
}
$chunkSize = 1000;
$total = 0;
$pdo->beginTransaction();
try {
foreach (array_chunk($all, $chunkSize) as $chunk) {
$ph = implode(",", array_fill(0, count($chunk), "(?,?,?)"));
$stmt = $pdo->prepare("INSERT INTO users (name, email, age) VALUES $ph");
$stmt->execute(array_merge(...$chunk));
$total += $stmt->rowCount();
}
$pdo->commit();
} catch (Throwable $e) {
$pdo->rollBack();
exit("失败: " . $e->getMessage());
}
echo "共插入 $total 行，每批 $chunkSize 行\n";Output
共插入 2500 行，每批 1000 行Huge transactions bloat the undo log and make rollback slow. Commit in chunks of a few thousand rows for large imports.
LAST_INSERT_ID with batches
After a multi-row insert, lastInsertId() returns the first generated ID; the rest are consecutive. Easy to misremember.
PHP
<?php
$rows = [["X1","x1@e.com",20],["X2","x2@e.com",21],["X3","x3@e.com",22]];
$ph = implode(",", array_fill(0, count($rows), "(?,?,?)"));
$stmt = $pdo->prepare("INSERT INTO users (name, email, age) VALUES $ph");
$stmt->execute(array_merge(...$rows));
$first = (int)$pdo->lastInsertId();
$count = $stmt->rowCount();
echo "首个 ID: $first\n";
for ($i = 0; $i < $count; $i++) {
echo " " . ($first + $i) . " => " . $rows[$i][0] . "\n";
}Output
首个 ID: 4
4 => X1
5 => X2
6 => X3Contiguity assumes innodb_autoinc_lock_mode 0 or 1 and no concurrent inserts. Under load IDs may have gaps — never rely on this in business logic.
Importing CSV
PHP
<?php
$csv = "name,email,age\nAmy,amy@example.com,25\nBob,bob@example.com,28\n";
$tmp = __DIR__ . "/import.csv";
file_put_contents($tmp, $csv);
$fp = fopen($tmp, "r");
fgetcsv($fp); // 跳过表头
$rows = [];
while (($r = fgetcsv($fp)) !== false) {
$rows[] = [$r[0], $r[1], (int)$r[2]];
}
fclose($fp);
unlink($tmp);
echo "读到 " . count($rows) . " 行\n";
echo $rows[0][0] . " " . $rows[1][2] . "\n";Output
读到 2 行
Amy 28fgetcsv handles quoted fields containing commas. Hand-rolled explode(",") breaks on "Smith, John".
一条语句插多行
PHP
<?php
$pdo = new PDO($dsn, $user, $pass, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$rows = [
["Amy", "amy@example.com", 25],
["Bob", "bob@example.com", 28],
["Cara", "cara@example.com", 31],
];
$placeholders = implode(",", array_fill(0, count($rows), "(?,?,?)"));
$stmt = $pdo->prepare("INSERT INTO users (name, email, age) VALUES $placeholders");
$stmt->execute(array_merge(...$rows));
echo "批量插入 " . $stmt->rowCount() . " 行\n";
echo "最后 ID: " . $pdo->lastInsertId() . "\n";Output
批量插入 3 行
最后 ID: 3一条语句插 3 行只算一次网络往返和一次解析。行数多时比循环 execute 快一个数量级。
事务包裹
PHP
<?php
$rows = [
["Amy", "amy@example.com", 25],
["Bob", "bob@example.com", 28],
["Cara", "cara@example.com", 31],
];
$pdo->beginTransaction();
try {
$placeholders = implode(",", array_fill(0, count($rows), "(?,?,?)"));
$stmt = $pdo->prepare("INSERT INTO users (name, email, age) VALUES $placeholders");
$stmt->execute(array_merge(...$rows));
$pdo->commit();
echo "提交成功，插入 " . $stmt->rowCount() . " 行\n";
} catch (Throwable $e) {
$pdo->rollBack();
echo "已回滚: " . $e->getMessage() . "\n";
}Output
提交成功，插入 3 行单条 INSERT 本身是原子的，事务的价值在多条语句的组合——比如插订单同时扣库存，必须一起成功。
分批循环
PHP
<?php
$all = [];
for ($i = 1; $i <= 2500; $i++) {
$all[] = ["user$i", "user$i@example.com", 20 + $i % 30];
}
$chunkSize = 1000;
$total = 0;
$pdo->beginTransaction();
try {
foreach (array_chunk($all, $chunkSize) as $chunk) {
$ph = implode(",", array_fill(0, count($chunk), "(?,?,?)"));
$stmt = $pdo->prepare("INSERT INTO users (name, email, age) VALUES $ph");
$stmt->execute(array_merge(...$chunk));
$total += $stmt->rowCount();
}
$pdo->commit();
} catch (Throwable $e) {
$pdo->rollBack();
exit("失败: " . $e->getMessage());
}
echo "共插入 $total 行，每批 $chunkSize 行\n";Output
共插入 2500 行，每批 1000 行大批量导入时事务不宜开得太大——几十万行的单个事务会产生巨大 undo log，回滚也要很久。分批提交（每 1000~5000 行一个事务）更稳。
LAST_INSERT_ID 与批量
批量插入后 lastInsertId() 返回的是第一行的 ID，后面的是连续的。这个行为很容易记错。
PHP
<?php
$rows = [["X1","x1@e.com",20],["X2","x2@e.com",21],["X3","x3@e.com",22]];
$ph = implode(",", array_fill(0, count($rows), "(?,?,?)"));
$stmt = $pdo->prepare("INSERT INTO users (name, email, age) VALUES $ph");
$stmt->execute(array_merge(...$rows));
$first = (int)$pdo->lastInsertId();
$count = $stmt->rowCount();
echo "首个 ID: $first\n";
for ($i = 0; $i < $count; $i++) {
echo " " . ($first + $i) . " => " . $rows[$i][0] . "\n";
}Output
首个 ID: 4
4 => X1
5 => X2
6 => X3依赖「ID 连续」有前提：innodb_autoinc_lock_mode 为 0 或 1，且没有并发插入。高并发下批量插入可能拿到不连续的 ID，不要把这个假设写进业务逻辑。
导入 CSV
PHP
<?php
$csv = "name,email,age\nAmy,amy@example.com,25\nBob,bob@example.com,28\n";
$tmp = __DIR__ . "/import.csv";
file_put_contents($tmp, $csv);
$fp = fopen($tmp, "r");
fgetcsv($fp); // 跳过表头
$rows = [];
while (($r = fgetcsv($fp)) !== false) {
$rows[] = [$r[0], $r[1], (int)$r[2]];
}
fclose($fp);
unlink($tmp);
echo "读到 " . count($rows) . " 行\n";
echo $rows[0][0] . " " . $rows[1][2] . "\n";Output
读到 2 行
Amy 28fgetcsv 会正确处理带引号、含逗号的字段。自己 explode(",") 遇到 "Smith, John" 就散架了。