PHP MySQL Delete

在PHP中，从MySQL数据库删除数据是一个常见操作，但也是一个需要格外小心的操作。本教程将详细介绍如何使用PHP和MySQL DELETE语句安全地从数据库中删除数据，包括基本的删除操作、条件删除以及如何防止意外删除整个表的数据。

基本的DELETE语句

DELETE语句用于从数据库表中删除记录。与UPDATE语句一样，DELETE语句也应该总是包含WHERE子句来指定要删除的记录，否则将删除表中的所有记录。

1. MySQLi面向对象方式

<?php
// 数据库连接参数
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);

// 检查连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

// SQL删除语句
$sql = "DELETE FROM MyGuests WHERE id=3";

if ($conn->query($sql) === TRUE) {
    echo "记录删除成功";
} else {
    echo "删除错误: " . $conn->error;
}

// 关闭连接
$conn->close();
?>

2. MySQLi过程式方式

<?php
// 数据库连接参数
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// 创建连接
$conn = mysqli_connect($servername, $username, $password, $dbname);

// 检查连接
if (!$conn) {
    die("连接失败: " . mysqli_connect_error());
}

// SQL删除语句
$sql = "DELETE FROM MyGuests WHERE id=3";

if (mysqli_query($conn, $sql)) {
    echo "记录删除成功";
} else {
    echo "删除错误: " . mysqli_error($conn);
}

// 关闭连接
mysqli_close($conn);
?>

3. 使用PDO

<?php
// 数据库连接参数
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// 尝试连接数据库
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname;charset=utf8mb4", $username, $password);
    
    // 设置PDO错误模式为异常
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // SQL删除语句
    $sql = "DELETE FROM MyGuests WHERE id=3";
    
    // 执行查询
    $conn->exec($sql);
    
    echo "记录删除成功";
} catch(PDOException $e) {
    echo $sql . "<br>" . $e->getMessage();
}

// 关闭连接
$conn = null;
?>

警告：在没有WHERE子句的情况下执行DELETE语句将删除表中的所有记录！这是一个不可逆的操作，请务必谨慎操作。

使用WHERE子句删除特定记录

WHERE子句在DELETE语句中至关重要，它指定了哪些记录应该被删除。以下是一些使用不同条件的WHERE子句示例。

<?php
// 数据库连接代码...

// 使用不同条件的WHERE子句
// 1. 根据ID删除
$sql1 = "DELETE FROM MyGuests WHERE id=10";

// 2. 使用比较运算符
$sql2 = "DELETE FROM products WHERE stock = 0"; // 删除库存为0的产品

// 3. 使用LIKE进行模糊匹配
$sql3 = "DELETE FROM users WHERE email LIKE '%@spamdomain.com'"; // 删除特定域名的用户

// 4. 使用IN子句
$sql4 = "DELETE FROM orders WHERE order_id IN (2001, 2002, 2003)";

// 5. 使用AND/OR组合条件
$sql5 = "DELETE FROM logs WHERE type='error' AND timestamp < '2023-01-01'"; // 删除2023年之前的错误日志

// 6. 使用日期条件
$sql6 = "DELETE FROM sessions WHERE last_activity < NOW() - INTERVAL 30 DAY"; // 删除30天未活动的会话

// 执行其中一个删除操作
if ($conn->query($sql1) === TRUE) {
    echo "记录删除成功";
} else {
    echo "删除错误: " . $conn->error;
}
?>

使用预处理语句防止SQL注入

当删除操作涉及用户输入时，必须使用预处理语句来防止SQL注入攻击。预处理语句将SQL逻辑与数据分开处理，确保用户输入不会被误解为SQL代码。

1. MySQLi预处理语句

<?php
// 数据库连接代码...

// 假设这是来自用户的输入数据
$id = $_POST['id'];

// 创建预处理语句
$stmt = $conn->prepare("DELETE FROM MyGuests WHERE id=?");
$stmt->bind_param("i", $id);

// 执行预处理语句
if ($stmt->execute()) {
    echo "记录删除成功";
} else {
    echo "删除错误: " . $stmt->error;
}

// 关闭语句
$stmt->close();
?>

2. PDO预处理语句

<?php
// 数据库连接代码...

// 假设这是来自用户的输入数据
$id = $_POST['id'];

try {
    // 准备预处理语句
    $stmt = $conn->prepare("DELETE FROM MyGuests WHERE id=:id");
    
    // 绑定参数
    $stmt->bindParam(':id', $id, PDO::PARAM_INT);
    
    // 执行预处理语句
    $stmt->execute();
    
    echo "记录删除成功";
} catch(PDOException $e) {
    echo "删除错误: " . $e->getMessage();
}
?>

获取受影响的行数

了解DELETE语句删除了多少行可以帮助您确认操作是否成功，并提供有用的反馈信息。

1. MySQLi获取受影响行数

<?php
// 数据库连接代码...

// 执行删除语句
$sql = "DELETE FROM MyGuests WHERE last_login < '2022-01-01'";

if ($conn->query($sql) === TRUE) {
    $affected_rows = $conn->affected_rows;
    echo "记录删除成功，受影响的行数: " . $affected_rows;
} else {
    echo "删除错误: " . $conn->error;
}

// 使用预处理语句时获取受影响行数
$stmt = $conn->prepare("DELETE FROM MyGuests WHERE id=?");
$stmt->bind_param("i", $id);
$stmt->execute();
$affected_rows = $stmt->affected_rows;
echo "预处理语句删除，受影响的行数: " . $affected_rows;
?>

2. PDO获取受影响行数

<?php
// 数据库连接代码...

try {
    // 使用exec()方法获取受影响行数
    $sql = "DELETE FROM MyGuests WHERE last_login < '2022-01-01'";
    $affected_rows = $conn->exec($sql);
    echo "记录删除成功，受影响的行数: " . $affected_rows;
    
    // 使用预处理语句时获取受影响行数
    $stmt = $conn->prepare("DELETE FROM MyGuests WHERE id=:id");
    $stmt->bindParam(':id', $id, PDO::PARAM_INT);
    $stmt->execute();
    $affected_rows = $stmt->rowCount();
    echo "预处理语句删除，受影响的行数: " . $affected_rows;
} catch(PDOException $e) {
    echo "删除错误: " . $e->getMessage();
}
?>

使用事务进行相关删除操作

当您需要执行多个相关的删除操作时，使用事务可以确保数据的一致性。事务允许您将一组操作视为单个原子操作：要么全部成功，要么全部失败。

1. MySQLi事务

<?php
// 数据库连接代码...

// 开始事务
$conn->begin_transaction();

try {
    // 删除用户及其所有相关数据
    // 1. 删除用户的评论
    $sql1 = "DELETE FROM comments WHERE user_id=10";
    $conn->query($sql1);
    
    // 2. 删除用户的订单
    $sql2 = "DELETE FROM orders WHERE user_id=10";
    $conn->query($sql2);
    
    // 3. 最后删除用户记录
    $sql3 = "DELETE FROM users WHERE id=10";
    $conn->query($sql3);
    
    // 提交事务
    $conn->commit();
    echo "用户及其相关数据已成功删除";
} catch (Exception $e) {
    // 发生错误时回滚事务
    $conn->rollback();
    echo "事务回滚: " . $e->getMessage();
}
?>

2. PDO事务

<?php
// 数据库连接代码...

try {
    // 开始事务
    $conn->beginTransaction();
    
    // 删除用户及其所有相关数据
    // 1. 删除用户的评论
    $sql1 = "DELETE FROM comments WHERE user_id=10";
    $conn->exec($sql1);
    
    // 2. 删除用户的订单
    $sql2 = "DELETE FROM orders WHERE user_id=10";
    $conn->exec($sql2);
    
    // 3. 最后删除用户记录
    $sql3 = "DELETE FROM users WHERE id=10";
    $conn->exec($sql3);
    
    // 提交事务
    $conn->commit();
    echo "用户及其相关数据已成功删除";
} catch (PDOException $e) {
    // 发生错误时回滚事务
    $conn->rollBack();
    echo "事务回滚: " . $e->getMessage();
}
?>

安全删除数据的替代方案

在某些情况下，您可能不想真正删除数据，而是希望保留它但将其标记为已删除。这种方法称为"软删除"，在许多应用程序中很常见。

软删除示例

<?php
// 数据库连接代码...

// 软删除：不是真正删除记录，而是更新标记字段
$sql = "UPDATE users SET deleted=1, deleted_at=NOW() WHERE id=5";

if ($conn->query($sql) === TRUE) {
    echo "用户已标记为删除";
} else {
    echo "标记删除错误: " . $conn->error;
}

// 查询时排除已删除的记录
$sql = "SELECT id, username, email FROM users WHERE deleted=0";
$result = $conn->query($sql);
?>

删除数据的最佳实践

始终使用WHERE子句：除非您确实需要删除表中的所有记录，否则一定要包含WHERE子句
使用预处理语句：当删除操作涉及用户输入时，使用预处理语句防止SQL注入
验证和过滤用户输入：在执行删除操作之前，验证和过滤所有用户输入的数据
使用事务处理相关删除：当需要删除多个相关表中的数据时，使用事务确保数据一致性
考虑使用软删除：对于重要数据，考虑使用软删除替代物理删除
实施权限控制：限制哪些用户可以执行删除操作
数据备份：在执行批量删除前，确保有最新的数据备份
测试删除操作：在生产环境中执行删除操作前，先在测试环境中验证
记录删除操作：对于重要数据，记录删除操作的日志，包括谁做的删除，删除的内容等
使用级联删除：在数据库设计时，考虑使用外键和级联删除来自动维护引用完整性
限制单次删除的记录数量：对于大型表，考虑分批删除数据，而不是一次删除大量记录

常见陷阱和错误

忘记WHERE子句：这是最危险的错误之一，可能导致整个表的数据被意外删除
不安全的用户输入处理：直接在SQL语句中使用未验证的用户输入，可能导致SQL注入攻击
忽略外键约束：尝试删除有外键约束的记录而不先删除引用它的记录，会导致错误
不处理事务回滚：在执行多个相关删除时，不使用事务或不处理错误回滚，可能导致数据不一致
过度删除：删除比需要更多的数据，没有考虑数据的潜在价值或法律要求
不考虑性能影响：在高峰期执行大量删除操作，可能影响数据库性能
忽略索引：在WHERE子句中不使用索引列，可能导致删除操作速度很慢