HoRain云--PHP MySQL DELETE操作完全指南
在PHP中执行MySQL的DELETE操作是数据库管理的基础技能。DELETE语句用于从数据库表中删除记录，但需要谨慎使用，因为删除的数据通常无法恢复。🔥🔥🔥Stay Hungry Stay Foolish 道阻且长,行则将至,让我们一起加油吧！前些天发现了一个超棒的服务器购买网站，性价比超高，大内存超划算！❤️❤️❤️本人水平有限，如有纰漏，欢迎各位大佬评论批评指正！不支持，总是删除所有记
·
🎬 HoRain 云小助手：个人主页
⛺️生活的理想，就是为了理想的生活!
⛳️ 推荐
前些天发现了一个超棒的服务器购买网站，性价比超高，大内存超划算！忍不住分享一下给大家。点击跳转到网站。
目录
🗑️ PHP MySQL DELETE 操作完全指南
在PHP中执行MySQL的DELETE操作是数据库管理的基础技能。DELETE语句用于从数据库表中删除记录，但需要谨慎使用，因为删除的数据通常无法恢复。
📋 目录
📝 DELETE 语句基础语法
基本 DELETE 语法
-- 删除特定记录
DELETE FROM table_name WHERE condition;
-- 删除所有记录（危险！）
DELETE FROM table_name;带 LIMIT 的 DELETE
-- 删除前N条匹配的记录
DELETE FROM table_name WHERE condition LIMIT 10;
-- 按排序删除
DELETE FROM table_name WHERE condition ORDER BY column_name LIMIT 5;多表删除（高级）
-- 删除关联表中的记录
DELETE t1, t2
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.table1_id
WHERE t1.status = 'inactive';🔧 使用 MySQLi 执行 DELETE
1. 面向过程风格
<?php
// 数据库连接配置
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "my_database";
// 创建连接
$conn = mysqli_connect($servername, $username, $password, $dbname);
// 检查连接
if (!$conn) {
die("连接失败: " . mysqli_connect_error());
}
// 示例1：删除特定记录（不安全 - 仅用于演示）
$id = 5;
$sql = "DELETE FROM users WHERE id = $id";
if (mysqli_query($conn, $sql)) {
echo "记录删除成功";
} else {
echo "错误: " . mysqli_error($conn);
}
// 示例2：使用预处理语句（安全）
$id = 5;
$sql = "DELETE FROM users WHERE id = ?";
$stmt = mysqli_prepare($conn, $sql);
if ($stmt) {
// 绑定参数
mysqli_stmt_bind_param($stmt, "i", $id);
// 执行语句
if (mysqli_stmt_execute($stmt)) {
$affected_rows = mysqli_stmt_affected_rows($stmt);
if ($affected_rows > 0) {
echo "成功删除 {$affected_rows} 条记录";
} else {
echo "没有记录被删除（可能ID不存在）";
}
} else {
echo "执行错误: " . mysqli_stmt_error($stmt);
}
// 关闭语句
mysqli_stmt_close($stmt);
} else {
echo "预处理错误: " . mysqli_error($conn);
}
// 关闭连接
mysqli_close($conn);
?>2. 面向对象风格
<?php
// 数据库连接配置
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "my_database";
// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
// 检查连接
if ($conn->connect_error) {
die("连接失败: " . $conn->connect_error);
}
// 示例1：简单删除
$sql = "DELETE FROM products WHERE stock = 0";
if ($conn->query($sql) === TRUE) {
echo "删除成功，影响行数: " . $conn->affected_rows;
} else {
echo "错误: " . $conn->error;
}
// 示例2：使用预处理语句删除多条记录
$ids = [10, 15, 20];
$placeholders = implode(',', array_fill(0, count($ids), '?'));
$sql = "DELETE FROM orders WHERE id IN ($placeholders)";
$stmt = $conn->prepare($sql);
if ($stmt) {
// 构建类型字符串
$types = str_repeat('i', count($ids));
// 绑定参数
$stmt->bind_param($types, ...$ids);
// 执行
if ($stmt->execute()) {
echo "成功删除 {$stmt->affected_rows} 条订单记录";
} else {
echo "删除失败: " . $stmt->error;
}
$stmt->close();
} else {
echo "预处理失败: " . $conn->error;
}
// 示例3：带事务的删除
$conn->begin_transaction();
try {
// 删除用户
$user_id = 100;
$sql1 = "DELETE FROM users WHERE id = ?";
$stmt1 = $conn->prepare($sql1);
$stmt1->bind_param("i", $user_id);
$stmt1->execute();
// 删除用户的订单
$sql2 = "DELETE FROM orders WHERE user_id = ?";
$stmt2 = $conn->prepare($sql2);
$stmt2->bind_param("i", $user_id);
$stmt2->execute();
// 删除用户的评论
$sql3 = "DELETE FROM comments WHERE user_id = ?";
$stmt3 = $conn->prepare($sql3);
$stmt3->bind_param("i", $user_id);
$stmt3->execute();
// 提交事务
$conn->commit();
echo "用户及相关数据删除成功";
} catch (Exception $e) {
// 回滚事务
$conn->rollback();
echo "删除失败，已回滚: " . $e->getMessage();
}
// 关闭连接
$conn->close();
?>🛡️ 使用 PDO 执行 DELETE
1. 基本 PDO 删除操作
<?php
// 数据库连接配置
$host = 'localhost';
$dbname = 'my_database';
$username = 'root';
$password = '';
try {
// 创建PDO连接
$pdo = new PDO(
"mysql:host=$host;dbname=$dbname;charset=utf8mb4",
$username,
$password,
[
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false
]
);
// 示例1：删除单条记录
$id = 25;
$sql = "DELETE FROM products WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute([':id' => $id]);
$rowCount = $stmt->rowCount();
if ($rowCount > 0) {
echo "成功删除 {$rowCount} 条产品记录";
} else {
echo "没有找到要删除的产品";
}
// 示例2：删除多条记录
$category = 'electronics';
$max_price = 100;
$sql = "DELETE FROM products WHERE category = :category AND price > :price";
$stmt = $pdo->prepare($sql);
$stmt->execute([
':category' => $category,
':price' => $max_price
]);
echo "删除高价电子产品: {$stmt->rowCount()} 条";
// 示例3：使用IN子句删除多条记录
$ids = [1, 3, 5, 7, 9];
$placeholders = implode(',', array_fill(0, count($ids), '?'));
$sql = "DELETE FROM users WHERE id IN ($placeholders)";
$stmt = $pdo->prepare($sql);
$stmt->execute($ids);
echo "批量删除用户: {$stmt->rowCount()} 条";
} catch (PDOException $e) {
die("数据库错误: " . $e->getMessage());
}
?>2. 高级 PDO 删除操作
<?php
// 创建PDO连接（同上）
try {
// 示例1：带LIMIT的删除
$sql = "DELETE FROM log_entries WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY) LIMIT 1000";
$stmt = $pdo->prepare($sql);
$stmt->execute();
echo "删除30天前的日志记录: {$stmt->rowCount()} 条";
// 示例2：使用事务删除关联数据
$pdo->beginTransaction();
$user_id = 50;
// 删除用户的所有订单
$sql1 = "DELETE FROM orders WHERE user_id = ?";
$stmt1 = $pdo->prepare($sql1);
$stmt1->execute([$user_id]);
$orders_deleted = $stmt1->rowCount();
// 删除用户的所有评论
$sql2 = "DELETE FROM comments WHERE user_id = ?";
$stmt2 = $pdo->prepare($sql2);
$stmt2->execute([$user_id]);
$comments_deleted = $stmt2->rowCount();
// 删除用户
$sql3 = "DELETE FROM users WHERE id = ?";
$stmt3 = $pdo->prepare($sql3);
$stmt3->execute([$user_id]);
$user_deleted = $stmt3->rowCount();
// 提交事务
$pdo->commit();
echo "删除用户 {$user_id}: 删除{$orders_deleted}个订单, {$comments_deleted}条评论";
// 示例3：使用命名参数的复杂删除
$conditions = [
'status' => 'inactive',
'last_login' => '2023-01-01',
'email_verified' => false
];
$sql = "DELETE FROM users WHERE status = :status AND last_login < :last_login AND email_verified = :email_verified";
$stmt = $pdo->prepare($sql);
$stmt->execute($conditions);
echo "删除不活跃用户: {$stmt->rowCount()} 条";
} catch (PDOException $e) {
// 回滚事务
if (isset($pdo) && $pdo->inTransaction()) {
$pdo->rollBack();
}
die("操作失败: " . $e->getMessage());
}
?>⚠️ WHERE 子句的重要性
1. 没有 WHERE 子句的危险
<?php
// ⚠️ 危险操作：删除所有数据！
$sql = "DELETE FROM users"; // 这将删除users表中的所有记录！
// 安全做法：总是使用WHERE子句
$sql = "DELETE FROM users WHERE id = 5"; // 只删除ID为5的记录
// 更好的做法：添加额外的安全限制
$sql = "DELETE FROM users WHERE id = 5 AND status = 'inactive'";
?>2. 安全的 WHERE 子句示例
<?php
// 示例1：基于多个条件删除
$conditions = [
'status' => 'deleted',
'deleted_at' => '2022-01-01'
];
$sql = "DELETE FROM posts WHERE status = :status AND deleted_at < :deleted_at";
// 示例2：使用BETWEEN删除某个时间范围内的记录
$start_date = '2023-01-01';
$end_date = '2023-12-31';
$sql = "DELETE FROM logs WHERE created_at BETWEEN :start_date AND :end_date";
// 示例3：使用子查询删除
$sql = "DELETE FROM products
WHERE id IN (
SELECT product_id
FROM order_items
GROUP BY product_id
HAVING COUNT(*) = 0
)";
// 示例4：删除重复记录（保留一条）
$sql = "DELETE t1 FROM products t1
INNER JOIN products t2
WHERE t1.id > t2.id
AND t1.sku = t2.sku";
?>🛡️ 安全注意事项与防SQL注入
1. SQL注入风险示例
<?php
// ⚠️ 危险：直接拼接用户输入
$id = $_GET['id']; // 用户输入：1 OR 1=1
$sql = "DELETE FROM users WHERE id = $id";
// 实际执行的SQL: DELETE FROM users WHERE id = 1 OR 1=1
// 结果：删除所有用户！
// ⚠️ 危险：使用addslashes也不安全
$id = addslashes($_GET['id']); // 用户输入：1' OR '1'='1
$sql = "DELETE FROM users WHERE id = '$id'";
// 实际执行的SQL: DELETE FROM users WHERE id = '1' OR '1'='1'
// 结果：删除所有用户！
?>2. 防止SQL注入的正确方法
<?php
// 方法1：使用预处理语句（MySQLi）
$id = $_GET['id'];
$sql = "DELETE FROM users WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $id); // "i"表示整数类型
$stmt->execute();
// 方法2：使用预处理语句（PDO）
$id = $_GET['id'];
$sql = "DELETE FROM users WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute([':id' => $id]);
// 方法3：验证和过滤输入
function deleteUser($user_id) {
// 验证输入是否为整数
if (!filter_var($user_id, FILTER_VALIDATE_INT)) {
throw new Exception("无效的用户ID");
}
// 验证ID范围
if ($user_id <= 0) {
throw new Exception("用户ID必须大于0");
}
// 使用预处理语句
$sql = "DELETE FROM users WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $user_id);
$stmt->execute();
return $stmt->affected_rows;
}
// 方法4：使用白名单验证
function deleteByStatus($status) {
// 定义允许的状态值
$allowed_statuses = ['inactive', 'banned', 'spam'];
// 验证输入
if (!in_array($status, $allowed_statuses)) {
throw new Exception("不允许的状态值");
}
// 使用预处理语句
$sql = "DELETE FROM users WHERE status = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("s", $status);
$stmt->execute();
return $stmt->affected_rows;
}
?>3. 输入验证函数
<?php
/**
* 安全删除函数
*/
class SafeDeleter {
private $conn;
public function __construct($connection) {
$this->conn = $connection;
}
/**
* 安全删除单条记录
*/
public function deleteById($table, $id, $id_column = 'id') {
// 验证表名（防止SQL注入）
if (!preg_match('/^[a-zA-Z_][a-zA-Z0-9_]*$/', $table)) {
throw new Exception("无效的表名");
}
// 验证列名
if (!preg_match('/^[a-zA-Z_][a-zA-Z0-9_]*$/', $id_column)) {
throw new Exception("无效的列名");
}
// 验证ID
if (!filter_var($id, FILTER_VALIDATE_INT) || $id <= 0) {
throw new Exception("无效的ID");
}
// 使用预处理语句
$sql = "DELETE FROM `$table` WHERE `$id_column` = ?";
$stmt = $this->conn->prepare($sql);
if (!$stmt) {
throw new Exception("预处理失败: " . $this->conn->error);
}
$stmt->bind_param("i", $id);
$stmt->execute();
return $stmt->affected_rows;
}
/**
* 批量安全删除
*/
public function deleteMultiple($table, $ids, $id_column = 'id') {
// 验证输入
if (!is_array($ids) || empty($ids)) {
throw new Exception("ID数组不能为空");
}
// 过滤和验证所有ID
$valid_ids = [];
foreach ($ids as $id) {
if (filter_var($id, FILTER_VALIDATE_INT) && $id > 0) {
$valid_ids[] = (int)$id;
}
}
if (empty($valid_ids)) {
return 0;
}
// 创建占位符
$placeholders = implode(',', array_fill(0, count($valid_ids), '?'));
// 使用预处理语句
$sql = "DELETE FROM `$table` WHERE `$id_column` IN ($placeholders)";
$stmt = $this->conn->prepare($sql);
if (!$stmt) {
throw new Exception("预处理失败: " . $this->conn->error);
}
// 构建类型字符串并绑定参数
$types = str_repeat('i', count($valid_ids));
$stmt->bind_param($types, ...$valid_ids);
$stmt->execute();
return $stmt->affected_rows;
}
/**
* 带条件的删除
*/
public function deleteWithConditions($table, $conditions) {
// 验证表名
if (!preg_match('/^[a-zA-Z_][a-zA-Z0-9_]*$/', $table)) {
throw new Exception("无效的表名");
}
// 构建WHERE子句
$where_clauses = [];
$params = [];
$types = '';
foreach ($conditions as $column => $value) {
// 验证列名
if (!preg_match('/^[a-zA-Z_][a-zA-Z0-9_]*$/', $column)) {
throw new Exception("无效的列名: $column");
}
$where_clauses[] = "`$column` = ?";
// 确定参数类型
if (is_int($value)) {
$types .= 'i';
} elseif (is_float($value)) {
$types .= 'd';
} else {
$types .= 's';
}
$params[] = $value;
}
if (empty($where_clauses)) {
throw new Exception("必须提供至少一个条件");
}
$where_sql = implode(' AND ', $where_clauses);
$sql = "DELETE FROM `$table` WHERE $where_sql";
$stmt = $this->conn->prepare($sql);
if (!$stmt) {
throw new Exception("预处理失败: " . $this->conn->error);
}
$stmt->bind_param($types, ...$params);
$stmt->execute();
return $stmt->affected_rows;
}
}
// 使用示例
$deleter = new SafeDeleter($conn);
try {
// 删除单条记录
$deleted = $deleter->deleteById('users', 5);
echo "删除 {$deleted} 条记录";
// 批量删除
$ids = [10, 15, 20];
$deleted = $deleter->deleteMultiple('products', $ids);
echo "批量删除 {$deleted} 条记录";
// 带条件删除
$conditions = [
'status' => 'inactive',
'last_login' => '2022-01-01'
];
$deleted = $deleter->deleteWithConditions('users', $conditions);
echo "条件删除 {$deleted} 条记录";
} catch (Exception $e) {
echo "删除失败: " . $e->getMessage();
}
?>🔍 错误处理与调试
1. 全面的错误处理
<?php
class DatabaseDeleter {
private $conn;
public function __construct($connection) {
$this->conn = $connection;
}
/**
* 安全删除记录
*/
public function safeDelete($table, $id) {
try {
// 开启事务
$this->conn->begin_transaction();
// 1. 首先检查记录是否存在
$check_sql = "SELECT COUNT(*) as count FROM `$table` WHERE id = ?";
$check_stmt = $this->conn->prepare($check_sql);
if (!$check_stmt) {
throw new Exception("检查语句预处理失败: " . $this->conn->error);
}
$check_stmt->bind_param("i", $id);
$check_stmt->execute();
$check_stmt->bind_result($count);
$check_stmt->fetch();
$check_stmt->close();
if ($count == 0) {
throw new Exception("记录不存在，ID: $id");
}
// 2. 执行删除
$delete_sql = "DELETE FROM `$table` WHERE id = ?";
$delete_stmt = $this->conn->prepare($delete_sql);
if (!$delete_stmt) {
throw new Exception("删除语句预处理失败: " . $this->conn->error);
}
$delete_stmt->bind_param("i", $id);
$delete_stmt->execute();
$affected_rows = $delete_stmt->affected_rows;
$delete_stmt->close();
if ($affected_rows == 0) {
throw new Exception("删除失败，可能记录已被删除");
}
// 3. 记录删除日志
$this->logDeletion($table, $id, $affected_rows);
// 提交事务
$this->conn->commit();
return [
'success' => true,
'affected_rows' => $affected_rows,
'message' => "成功删除 {$affected_rows} 条记录"
];
} catch (Exception $e) {
// 回滚事务
if ($this->conn->in_transaction) {
$this->conn->rollback();
}
return [
'success' => false,
'error' => $e->getMessage(),
'code' => $e->getCode()
];
}
}
/**
* 记录删除日志
*/
private function logDeletion($table, $id, $affected_rows) {
$log_sql = "INSERT INTO deletion_logs (table_name, record_id, affected_rows, deleted_at, deleted_by)
VALUES (?, ?, ?, NOW(), ?)";
$log_stmt = $this->conn->prepare($log_sql);
// 获取当前用户（示例）
$deleted_by = $_SESSION['user_id'] ?? 'system';
$log_stmt->bind_param("siis", $table, $id, $affected_rows, $deleted_by);
$log_stmt->execute();
$log_stmt->close();
}
/**
* 软删除（标记删除而非物理删除）
*/
public function softDelete($table, $id) {
try {
$sql = "UPDATE `$table` SET
is_deleted = 1,
deleted_at = NOW(),
deleted_by = ?
WHERE id = ? AND is_deleted = 0";
$stmt = $this->conn->prepare($sql);
if (!$stmt) {
throw new Exception("预处理失败: " . $this->conn->error);
}
$deleted_by = $_SESSION['user_id'] ?? 'system';
$stmt->bind_param("si", $deleted_by, $id);
$stmt->execute();
$affected_rows = $stmt->affected_rows;
$stmt->close();
if ($affected_rows == 0) {
throw new Exception("记录不存在或已被删除");
}
return [
'success' => true,
'affected_rows' => $affected_rows,
'message' => "成功标记删除 {$affected_rows} 条记录"
];
} catch (Exception $e) {
return [
'success' => false,
'error' => $e->getMessage()
];
}
}
}
// 使用示例
$deleter = new DatabaseDeleter($conn);
// 硬删除
$result = $deleter->safeDelete('users', 5);
if ($result['success']) {
echo $result['message'];
} else {
echo "删除失败: " . $result['error'];
}
// 软删除
$result = $deleter->softDelete('products', 10);
if ($result['success']) {
echo $result['message'];
} else {
echo "删除失败: " . $result['error'];
}
?>2. 调试和日志记录
<?php
class DebugDeleter {
private $conn;
private $debug;
public function __construct($connection, $debug = false) {
$this->conn = $connection;
$this->debug = $debug;
}
/**
* 带调试的删除操作
*/
public function deleteWithDebug($table, $conditions) {
// 记录开始时间
$start_time = microtime(true);
try {
// 构建SQL
$where_parts = [];
$params = [];
$types = '';
foreach ($conditions as $key => $value) {
$where_parts[] = "`$key` = ?";
$params[] = $value;
if (is_int($value)) {
$types .= 'i';
} elseif (is_float($value)) {
$types .= 'd';
} else {
$types .= 's';
}
}
$where_sql = implode(' AND ', $where_parts);
$sql = "DELETE FROM `$table` WHERE $where_sql";
// 调试信息
if ($this->debug) {
$this->logDebug("SQL: $sql");
$this->logDebug("参数: " . print_r($params, true));
$this->logDebug("类型: $types");
}
// 准备语句
$stmt = $this->conn->prepare($sql);
if (!$stmt) {
$error = $this->conn->error;
$this->logError("预处理失败: $error");
throw new Exception("预处理失败: $error");
}
// 绑定参数
if (!empty($params)) {
$stmt->bind_param($types, ...$params);
}
// 执行
$executed = $stmt->execute();
if (!$executed) {
$error = $stmt->error;
$this->logError("执行失败: $error");
throw new Exception("执行失败: $error");
}
$affected_rows = $stmt->affected_rows;
$stmt->close();
// 记录执行时间
$end_time = microtime(true);
$execution_time = round(($end_time - $start_time) * 1000, 2);
if ($this->debug) {
$this->logDebug("影响行数: $affected_rows");
$this->logDebug("执行时间: {$execution_time}ms");
}
return [
'success' => true,
'affected_rows' => $affected_rows,
'execution_time' => $execution_time,
'sql' => $sql
];
} catch (Exception $e) {
$end_time = microtime(true);
$execution_time = round(($end_time - $start_time) * 1000, 2);
$this->logError("删除失败: " . $e->getMessage());
$this->logError("执行时间: {$execution_time}ms");
return [
'success' => false,
'error' => $e->getMessage(),
'execution_time' => $execution_time
];
}
}
/**
* 记录调试信息
*/
private function logDebug($message) {
$log = "[" . date('Y-m-d H:i:s') . "] DEBUG: $message\n";
file_put_contents('debug.log', $log, FILE_APPEND);
echo "<pre>DEBUG: $message</pre>";
}
/**
* 记录错误信息
*/
private function logError($message) {
$log = "[" . date('Y-m-d H:i:s') . "] ERROR: $message\n";
file_put_contents('error.log', $log, FILE_APPEND);
echo "<pre style='color: red;'>ERROR: $message</pre>";
}
}
// 使用示例
$debugDeleter = new DebugDeleter($conn, true);
$conditions = [
'status' => 'inactive',
'created_at' => '2022-01-01'
];
$result = $debugDeleter->deleteWithDebug('users', $conditions);
if ($result['success']) {
echo "删除成功，影响行数: " . $result['affected_rows'];
} else {
echo "删除失败: " . $result['error'];
}
?>📊 DELETE vs TRUNCATE vs DROP
比较表格
|
特性 |
DELETE |
TRUNCATE |
DROP |
|---|---|---|---|
|
操作类型 |
DML（数据操作语言） |
DDL（数据定义语言） |
DDL（数据定义语言） |
|
删除内容 |
删除表中的记录 |
删除表中的所有记录 |
删除整个表 |
|
WHERE子句 |
支持，可以条件删除 |
不支持，总是删除所有记录 |
不支持 |
|
事务 |
支持，可以回滚 |
不支持，不能回滚 |
不支持，不能回滚 |
|
触发器 |
会触发DELETE触发器 |
不会触发触发器 |
不会触发触发器 |
|
性能 |
较慢，逐行删除 |
很快，直接删除数据页 |
很快 |
|
自增ID |
不重置自增计数器 |
重置自增计数器 |
删除表结构 |
|
日志 |
记录每行删除日志 |
记录页释放日志 |
记录表删除日志 |
|
恢复 |
可以通过事务回滚恢复 |
无法恢复 |
无法恢复 |
使用示例
<?php
// 1. DELETE - 删除特定记录（可恢复）
$sql = "DELETE FROM users WHERE status = 'inactive'";
// 特点：可以回滚，触发触发器，较慢
// 2. TRUNCATE - 快速清空表（不可恢复）
$sql = "TRUNCATE TABLE temp_data";
// 特点：不能回滚，不触发触发器，很快，重置自增ID
// 3. DROP - 删除整个表（不可恢复）
$sql = "DROP TABLE IF EXISTS old_table";
// 特点：删除表结构和所有数据
// 选择建议：
// - 需要条件删除时使用 DELETE
// - 需要快速清空整个表时使用 TRUNCATE
// - 需要删除整个表结构时使用 DROP
?>🏗️ 实际应用示例
1. 完整的用户管理系统删除功能
<?php
class UserManager {
private $conn;
public function __construct($connection) {
$this->conn = $connection;
}
/**
* 删除用户（硬删除）
*/
public function deleteUser($user_id, $permanent = false) {
try {
$this->conn->begin_transaction();
// 验证用户是否存在
$user = $this->getUserById($user_id);
if (!$user) {
throw new Exception("用户不存在");
}
if ($permanent) {
// 永久删除
$this->permanentDeleteUser($user_id);
} else {
// 软删除
$this->softDeleteUser($user_id);
}
$this->conn->commit();
return ['success' => true, 'message' => '用户删除成功'];
} catch (Exception $e) {
$this->conn->rollback();
return ['success' => false, 'error' => $e->getMessage()];
}
}
/**
* 永久删除用户及相关数据
*/
private function permanentDeleteUser($user_id) {
// 删除顺序：从外键约束最少的表开始
$tables = [
'user_sessions', // 会话
'user_logs', // 日志
'user_comments', // 评论
'user_orders', // 订单
'user_addresses', // 地址
'users' // 用户
];
foreach ($tables as $table) {
$sql = "DELETE FROM $table WHERE user_id = ?";
$stmt = $this->conn->prepare($sql);
$stmt->bind_param("i", $user_id);
$stmt->execute();
$stmt->close();
}
$this->logAction('permanent_delete', $user_id, '永久删除用户');
}
/**
* 软删除用户
*/
private function softDeleteUser($user_id) {
$sql = "UPDATE users SET
status = 'deleted',
deleted_at = NOW(),
deleted_by = ?
WHERE id = ? AND status != 'deleted'";
$deleted_by = $_SESSION['user_id'] ?? 'system';
$stmt = $this->conn->prepare($sql);
$stmt->bind_param("si", $deleted_by, $user_id);
$stmt->execute();
$affected = $stmt->affected_rows;
$stmt->close();
if ($affected > 0) {
$this->logAction('soft_delete', $user_id, '软删除用户');
}
}
/**
* 批量删除不活跃用户
*/
public function deleteInactiveUsers($days = 365) {
try {
$sql = "DELETE FROM users
WHERE last_login < DATE_SUB(NOW(), INTERVAL ? DAY)
AND status = 'active'";
$stmt = $this->conn->prepare($sql);
$stmt->bind_param("i", $days);
$stmt->execute();
$deleted_count = $stmt->affected_rows;
$stmt->close();
$this->logAction('batch_delete', null, "批量删除{$deleted_count}个不活跃用户");
return [
'success' => true,
'deleted_count' => $deleted_count,
'message' => "成功删除 {$deleted_count} 个不活跃用户"
];
} catch (Exception $e) {
return ['success' => false, 'error' => $e->getMessage()];
}
}
/**
* 获取用户信息
*/
private function getUserById($user_id) {
$sql = "SELECT * FROM users WHERE id = ?";
$stmt = $this->conn->prepare($sql);
$stmt->bind_param("i", $user_id);
$stmt->execute();
$result = $stmt->get_result();
$user = $result->fetch_assoc();
$stmt->close();
return $user;
}
/**
* 记录操作日志
*/
private function logAction($action, $target_id, $description) {
$sql = "INSERT INTO audit_logs (action, target_id, description, performed_by, performed_at)
VALUES (?, ?, ?, ?, NOW())";
$performed_by = $_SESSION['user_id'] ?? 'system';
$stmt = $this->conn->prepare($sql);
$stmt->bind_param("siss", $action, $target_id, $description, $performed_by);
$stmt->execute();
$stmt->close();
}
}
// 使用示例
$userManager = new UserManager($conn);
// 删除单个用户（软删除）
$result = $userManager->deleteUser(5);
if ($result['success']) {
echo $result['message'];
}
// 永久删除用户
$result = $userManager->deleteUser(10, true);
if ($result['success']) {
echo $result['message'];
}
// 批量删除不活跃用户
$result = $userManager->deleteInactiveUsers(180); // 删除180天未登录的用户
if ($result['success']) {
echo $result['message'];
}
?>2. 电子商务系统删除功能
<?php
class ProductManager {
private $conn;
public function __construct($connection) {
$this->conn = $connection;
}
/**
* 删除产品（带库存检查）
*/
public function deleteProduct($product_id) {
try {
$this->conn->begin_transaction();
// 检查产品是否存在
$product = $this->getProduct($product_id);
if (!$product) {
throw new Exception("产品不存在");
}
// 检查是否有未完成的订单
if ($this->hasPendingOrders($product_id)) {
throw new Exception("产品有未完成的订单，无法删除");
}
// 检查库存
if ($product['stock'] > 0) {
throw new Exception("产品还有库存，请先清空库存");
}
// 软删除产品
$sql = "UPDATE products SET
is_deleted = 1,
deleted_at = NOW(),
deleted_by = ?
WHERE id = ?";
$deleted_by = $_SESSION['user_id'] ?? 'system';
$stmt = $this->conn->prepare($sql);
$stmt->bind_param("si", $deleted_by, $product_id);
$stmt->execute();
// 记录删除日志
$this->logProductDeletion($product_id, $product['name']);
$this->conn->commit();
return [
'success' => true,
'message' => "产品 '{$product['name']}' 已删除"
];
} catch (Exception $e) {
$this->conn->rollback();
return ['success' => false, 'error' => $e->getMessage()];
}
}
/**
* 批量删除过期产品
*/
public function deleteExpiredProducts() {
try {
$sql = "SELECT id, name FROM products
WHERE expiry_date < NOW()
AND is_deleted = 0";
$stmt = $this->conn->prepare($sql);
$stmt->execute();
$result = $stmt->get_result();
$deleted_products = [];
$error_products = [];
while ($product = $result->fetch_assoc()) {
$delete_result = $this->deleteProduct($product['id']);
if ($delete_result['success']) {
$deleted_products[] = $product['name'];
} else {
$error_products[] = [
'name' => $product['name'],
'error' => $delete_result['error']
];
}
}
$stmt->close();
return [
'success' => true,
'deleted' => $deleted_products,
'errors' => $error_products,
'message' => sprintf(
"成功删除 %d 个产品，%d 个失败",
count($deleted_products),
count($error_products)
)
];
} catch (Exception $e) {
return ['success' => false, 'error' => $e->getMessage()];
}
}
/**
* 删除产品分类（级联删除）
*/
public function deleteCategory($category_id) {
try {
$this->conn->begin_transaction();
// 检查分类是否存在
$category = $this->getCategory($category_id);
if (!$category) {
throw new Exception("分类不存在");
}
// 检查分类下是否有产品
if ($this->categoryHasProducts($category_id)) {
throw new Exception("分类下有产品，无法删除");
}
// 删除分类
$sql = "DELETE FROM categories WHERE id = ?";
$stmt = $this->conn->prepare($sql);
$stmt->bind_param("i", $category_id);
$stmt->execute();
$this->conn->commit();
return [
'success' => true,
'message' => "分类 '{$category['name']}' 已删除"
];
} catch (Exception $e) {
$this->conn->rollback();
return ['success' => false, 'error' => $e->getMessage()];
}
}
private function getProduct($product_id) {
$sql = "SELECT * FROM products WHERE id = ?";
$stmt = $this->conn->prepare($sql);
$stmt->bind_param("i", $product_id);
$stmt->execute();
$result = $stmt->get_result();
$product = $result->fetch_assoc();
$stmt->close();
return $product;
}
private function hasPendingOrders($product_id) {
$sql = "SELECT COUNT(*) as count FROM order_items oi
JOIN orders o ON oi.order_id = o.id
WHERE oi.product_id = ? AND o.status IN ('pending', 'processing')";
$stmt = $this->conn->prepare($sql);
$stmt->bind_param("i", $product_id);
$stmt->execute();
$stmt->bind_result($count);
$stmt->fetch();
$stmt->close();
return $count > 0;
}
private function logProductDeletion($product_id, $product_name) {
$sql = "INSERT INTO product_deletion_log
(product_id, product_name, deleted_by, deleted_at)
VALUES (?, ?, ?, NOW())";
$deleted_by = $_SESSION['user_id'] ?? 'system';
$stmt = $this->conn->prepare($sql);
$stmt->bind_param("iss", $product_id, $product_name, $deleted_by);
$stmt->execute();
$stmt->close();
}
private function getCategory($category_id) {
$sql = "SELECT * FROM categories WHERE id = ?";
$stmt = $this->conn->prepare($sql);
$stmt->bind_param("i", $category_id);
$stmt->execute();
$result = $stmt->get_result();
$category = $result->fetch_assoc();
$stmt->close();
return $category;
}
private function categoryHasProducts($category_id)
❤️❤️❤️本人水平有限，如有纰漏，欢迎各位大佬评论批评指正！😄😄😄
💘💘💘如果觉得这篇文对你有帮助的话，也请给个点赞、收藏下吧，非常感谢!👍 👍 👍
🔥🔥🔥Stay Hungry Stay Foolish 道阻且长,行则将至,让我们一起加油吧！🌙🌙🌙
openEuler 是由开放原子开源基金会孵化的全场景开源操作系统项目，面向数字基础设施四大核心场景（服务器、云计算、边缘计算、嵌入式），全面支持 ARM、x86、RISC-V、loongArch、PowerPC、SW-64 等多样性计算架构
更多推荐
15
0- 0
已为社区贡献12条内容
所有评论(0)