PHP MySQL Update
UPDATE statements, affected rows, and the cost of a missing WHERE.
Key PointsAn UPDATE without WHERE rewrites the whole table — SELECT first, then update.
Update one row
PHP
<?php
$stmt = $pdo->prepare("UPDATE users SET email = ?, age = ? WHERE id = ?");
$stmt->execute(["new@example.com", 31, 1]);
echo "更新行数: " . $stmt->rowCount() . "\n";Output
更新行数: 1rowCount() counts changed rows, not matched ones. Setting identical values returns 0 — not an error. To distinguish missing from unchanged, SELECT first.Updating selected fields
When a form submits only some fields, build the SET list from an allowlist instead of overwriting every column.
PHP
<?php
$input = ["name" => "Tom Lee", "age" => 32];
$allowed = ["name", "email", "age", "status"];
$sets = [];
$params = [];
foreach ($input as $k => $v) {
if (!in_array($k, $allowed, true)) {
continue;
}
$sets[] = "$k = ?";
$params[] = $v;
}
if (!$sets) {
exit("没有可更新的字段");
}
$params[] = 1;
$sql = "UPDATE users SET " . implode(", ", $sets) . " WHERE id = ?";
echo $sql, "\n";
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
echo "更新行数: " . $stmt->rowCount() . "\n";Output
UPDATE users SET name = ?, age = ? WHERE id = ?
更新行数: 1Column names come from the allowlist and values go through placeholders — the only correct way to build a dynamic UPDATE.
Forgetting WHERE
PHP
<?php
// 灾难：全表被改
$sql = "UPDATE users SET status = 0";
echo $sql, "\n";
echo "没有 WHERE，全部行都会变\n";
// 正确：先查再改
$sql = "UPDATE users SET status = 0 WHERE id = 1";
echo $sql, "\n";Output
UPDATE users SET status = 0
没有 WHERE，全部行都会变
UPDATE users SET status = 0 WHERE id = 1Enable
sql_safe_updates in production — it rejects UPDATE/DELETE without a WHERE or without an index. Also available as mysql --safe-updates.Concurrent updates
Two people editing the same row: the later write wins. Use optimistic locking or atomic operations.
PHP
<?php
// 乐观锁：带版本号更新
$id = 1;
$version = 3;
$stmt = $pdo->prepare("UPDATE users SET name = ?, version = version + 1 WHERE id = ? AND version = ?");
$stmt->execute(["Tom Lee", $id, $version]);
if ($stmt->rowCount() === 0) {
echo "数据已被他人修改，请刷新后重试\n";
} else {
echo "更新成功，版本变为 4\n";
}Output
更新成功，版本变为 4AND version = ? is the crux: a mismatch updates zero rows and the caller knows there was a conflict — the lightest optimistic lock.
Atomic increments
PHP
<?php
// 错：先读再写，并发下会丢更新
// $n = SELECT stock; UPDATE stock = $n - 1;
// 对：在数据库里做减法
$stmt = $pdo->prepare("UPDATE products SET stock = stock - ? WHERE id = ? AND stock >= ?");
$stmt->execute([1, 1, 1]);
if ($stmt->rowCount() === 0) {
echo "库存不足\n";
} else {
echo "扣减成功\n";
}Output
扣减成功WHERE stock >= ? both guards and serialises: insufficient stock updates zero rows, so stock never goes negative. Read-modify-write loses updates under concurrency.更新一行
PHP
<?php
$stmt = $pdo->prepare("UPDATE users SET email = ?, age = ? WHERE id = ?");
$stmt->execute(["new@example.com", 31, 1]);
echo "更新行数: " . $stmt->rowCount() . "\n";Output
更新行数: 1rowCount() 返回的是实际改变的行数，不是匹配的行数。新值和旧值完全一样时返回 0——这不是出错。要区分「没这行」和「值没变」，先 SELECT 一次。只更新部分字段
表单只提交了几个字段时，用白名单 + 动态拼 SET，不要写一条覆盖所有列的 UPDATE。
PHP
<?php
$input = ["name" => "Tom Lee", "age" => 32];
$allowed = ["name", "email", "age", "status"];
$sets = [];
$params = [];
foreach ($input as $k => $v) {
if (!in_array($k, $allowed, true)) {
continue;
}
$sets[] = "$k = ?";
$params[] = $v;
}
if (!$sets) {
exit("没有可更新的字段");
}
$params[] = 1;
$sql = "UPDATE users SET " . implode(", ", $sets) . " WHERE id = ?";
echo $sql, "\n";
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
echo "更新行数: " . $stmt->rowCount() . "\n";Output
UPDATE users SET name = ?, age = ? WHERE id = ?
更新行数: 1列名来自白名单（$allowed），值走占位符。这是动态 UPDATE 唯一正确的写法。
忘了 WHERE
PHP
<?php
// 灾难：全表被改
$sql = "UPDATE users SET status = 0";
echo $sql, "\n";
echo "没有 WHERE，全部行都会变\n";
// 正确：先查再改
$sql = "UPDATE users SET status = 0 WHERE id = 1";
echo $sql, "\n";Output
UPDATE users SET status = 0
没有 WHERE，全部行都会变
UPDATE users SET status = 0 WHERE id = 1生产环境建议开启
sql_safe_updates，它会拒绝没有 WHERE 或 WHERE 里没走索引的 UPDATE / DELETE。也可以在 mysql 客户端里用 --safe-updates。并发更新
两个人同时改同一条记录，后提交的会覆盖前面的。要么加乐观锁（版本号），要么用原子操作。
PHP
<?php
// 乐观锁：带版本号更新
$id = 1;
$version = 3;
$stmt = $pdo->prepare("UPDATE users SET name = ?, version = version + 1 WHERE id = ? AND version = ?");
$stmt->execute(["Tom Lee", $id, $version]);
if ($stmt->rowCount() === 0) {
echo "数据已被他人修改，请刷新后重试\n";
} else {
echo "更新成功，版本变为 4\n";
}Output
更新成功，版本变为 4WHERE ... AND version = ? 是关键：版本对不上就改 0 行，调用方据此知道发生了冲突。这是最轻量的乐观锁实现。
原子增减
PHP
<?php
// 错：先读再写，并发下会丢更新
// $n = SELECT stock; UPDATE stock = $n - 1;
// 对：在数据库里做减法
$stmt = $pdo->prepare("UPDATE products SET stock = stock - ? WHERE id = ? AND stock >= ?");
$stmt->execute([1, 1, 1]);
if ($stmt->rowCount() === 0) {
echo "库存不足\n";
} else {
echo "扣减成功\n";
}Output
扣减成功WHERE stock >= ? 既做条件又做并发保护：库存不够就改 0 行，不会出现负库存。读-改-写三步在并发下必然丢更新，所有计数器都要用这种原子写法。