PHP MySQL Delete
Deleting rows, soft deletes, and safe bulk deletion.
Key PointsHard deletes are irreversible — prefer soft deletes with `deleted_at`.
Delete one row
PHP
<?php
$stmt = $pdo->prepare("DELETE FROM users WHERE id = ?");
$stmt->execute([3]);
echo "删除行数: " . $stmt->rowCount() . "\n";
$left = (int)$pdo->query("SELECT COUNT(*) FROM users")->fetchColumn();
echo "剩余: $left\n";Output
删除行数: 1
剩余: 2Soft delete
Business data is rarely hard-deleted. Add deleted_at, write a timestamp on delete, filter on read. Recoverable and auditable.
SQL
ALTER TABLE users ADD COLUMN deleted_at DATETIME NULL DEFAULT NULL;
ALTER TABLE users ADD KEY idx_deleted (deleted_at);PHP
<?php
// 删除
$stmt = $pdo->prepare("UPDATE users SET deleted_at = NOW() WHERE id = ?");
$stmt->execute([2]);
echo "已标记删除: " . $stmt->rowCount() . " 行\n";
// 查询时过滤
$rows = $pdo->query("SELECT name FROM users WHERE deleted_at IS NULL ORDER BY id")
->fetchAll(PDO::FETCH_COLUMN);
echo "有效记录: " . implode(", ", $rows) . "\n";Output
已标记删除: 1 行
有效记录: TomThe cost: **every query needs
deleted_at IS NULL** and one omission leaks data. Use a view or an ORM global scope rather than discipline.Bulk delete
PHP
<?php
$ids = [1, 2, 5];
$in = implode(",", array_fill(0, count($ids), "?"));
$stmt = $pdo->prepare("DELETE FROM users WHERE id IN ($in)");
$stmt->execute($ids);
echo "删除行数: " . $stmt->rowCount() . "\n";Output
删除行数: 3Cast every ID to int, then build placeholders. Return early on an empty array — IN () is a syntax error.
Deleting from big tables
Deleting millions of rows locks the table, bloats the undo log and lags replication. Delete in batches with a pause.
PHP
<?php
$batch = 1000;
$total = 0;
while (true) {
$stmt = $pdo->prepare("DELETE FROM users WHERE deleted_at IS NOT NULL LIMIT ?");
$stmt->bindValue(1, $batch, PDO::PARAM_INT);
$stmt->execute();
$n = $stmt->rowCount();
$total += $n;
if ($n < $batch) {
break;
}
usleep(100000); // 100ms，给主从同步留时间
}
echo "共删除 $total 行，每批 $batch 行\n";Output
共删除 2500 行，每批 1000 行LIMIT in a DELETE needs bindValue(..., PDO::PARAM_INT); otherwise PDO sends a string and MySQL rejects it.TRUNCATE vs DELETE
| Aspect | DELETE | TRUNCATE |
|---|---|---|
| WHERE | 支持 | 不支持 |
| Transaction | 是 | 否（DDL） |
| Speed | 逐行，慢 | 重建表，快 |
| Auto-increment | 保留 | 重置为 1 |
| Triggers | 会触发 | 不触发 |
| Use for | 按条件删 | 清空整表 |
TRUNCATE is not rollback-able, skips triggers and needs DROP privilege. Check foreign keys first or it errors out.删除一行
PHP
<?php
$stmt = $pdo->prepare("DELETE FROM users WHERE id = ?");
$stmt->execute([3]);
echo "删除行数: " . $stmt->rowCount() . "\n";
$left = (int)$pdo->query("SELECT COUNT(*) FROM users")->fetchColumn();
echo "剩余: $left\n";Output
删除行数: 1
剩余: 2软删除
业务数据很少真删。加一个 deleted_at 字段，删除时写时间戳，查询时过滤。数据可恢复，也便于审计。
SQL
ALTER TABLE users ADD COLUMN deleted_at DATETIME NULL DEFAULT NULL;
ALTER TABLE users ADD KEY idx_deleted (deleted_at);PHP
<?php
// 删除
$stmt = $pdo->prepare("UPDATE users SET deleted_at = NOW() WHERE id = ?");
$stmt->execute([2]);
echo "已标记删除: " . $stmt->rowCount() . " 行\n";
// 查询时过滤
$rows = $pdo->query("SELECT name FROM users WHERE deleted_at IS NULL ORDER BY id")
->fetchAll(PDO::FETCH_COLUMN);
echo "有效记录: " . implode(", ", $rows) . "\n";Output
已标记删除: 1 行
有效记录: Tom软删除的代价：**每个查询都要带
deleted_at IS NULL**，忘一处就泄露数据。用视图或者 ORM 的全局作用域统一处理，比靠自觉可靠。批量删除
PHP
<?php
$ids = [1, 2, 5];
$in = implode(",", array_fill(0, count($ids), "?"));
$stmt = $pdo->prepare("DELETE FROM users WHERE id IN ($in)");
$stmt->execute($ids);
echo "删除行数: " . $stmt->rowCount() . "\n";Output
删除行数: 3先校验 $ids 里全是整数（array_map("intval", $ids)），再生成占位符。空数组要提前返回，否则 IN () 是语法错误。
删除大表
一次删几百万行会锁表、撑爆 undo log、拖慢主从同步。分批删，每批 sleep 一下。
PHP
<?php
$batch = 1000;
$total = 0;
while (true) {
$stmt = $pdo->prepare("DELETE FROM users WHERE deleted_at IS NOT NULL LIMIT ?");
$stmt->bindValue(1, $batch, PDO::PARAM_INT);
$stmt->execute();
$n = $stmt->rowCount();
$total += $n;
if ($n < $batch) {
break;
}
usleep(100000); // 100ms，给主从同步留时间
}
echo "共删除 $total 行，每批 $batch 行\n";Output
共删除 2500 行，每批 1000 行DELETE ... LIMIT ? 里的 LIMIT 在 PDO 下必须 bindValue(..., PDO::PARAM_INT)，否则会被当字符串发出而报语法错误。这是和 SELECT 一样的坑。TRUNCATE 与 DELETE
| 对比项 | DELETE | TRUNCATE |
|---|---|---|
| WHERE 条件 | 支持 | 不支持 |
| 可回滚 | 是 | 否（DDL） |
| 速度 | 逐行，慢 | 重建表，快 |
| 自增计数 | 保留 | 重置为 1 |
| 触发器 | 会触发 | 不触发 |
| 适用 | 按条件删 | 清空整表 |
TRUNCATE 不可回滚、不触发 DELETE 触发器，而且需要 DROP 权限。线上清表前务必确认没有外键引用，否则会直接报错。