MySQL 更新数据

PHP 在 MySQL 中更新数据

使用 MySQLi 和 PDO 在 MySQL 表中更新数据

SQL 的 UPDATE 语句用于修改数据库表中的现有记录。

基本语法:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

警告：WHERE 子句至关重要！它指定要更新哪些记录。省略 WHERE 子句将更新表中的所有记录，这通常不是预期行为，并且可能具有破坏性。

安全最佳实践：预处理语句

为了防止 SQL 注入（SQL injection）漏洞，在执行包含外部数据（如用户输入）的查询时，务必始终使用预处理语句（prepared statements）。预处理语句将 SQL 命令与数据分离，使恶意输入无法更改查询结构。

假设我们有一个 MyGuests 表，包含 id、firstname、lastname、email 和 reg_date 列。

示例场景：更新 id = 2 的访客的 lastname。

示例：使用预处理语句的 MySQLi（面向对象风格）

<?php
$servername = "localhost";
$username = "your_username"; // 替换为你的数据库用户名
$password = "your_password"; // 替换为你的数据库密码
$dbname = "myDB"; // 替换为你的数据库名

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

// 检查连接
if ($conn->connect_error) {
    // 在生产环境中应使用错误日志记录而非 die()
    die("Connection failed: " . $conn->connect_error);
}

// SQL 查询，使用占位符 (?)
$sql = "UPDATE MyGuests SET lastname = ? WHERE id = ?";

// 准备语句
$stmt = $conn->prepare($sql);

if ($stmt === false) {
    // 处理错误，例如记录日志
    die("Error preparing statement: " . $conn->error);
}

// 要绑定的数据
$new_lastname = 'DoeUpdated';
$guest_id = 2;

// 绑定参数 (s = 字符串, i = 整数)
// 类型必须按顺序与占位符匹配
$stmt->bind_param("si", $new_lastname, $guest_id);

// 执行语句
if ($stmt->execute()) {
    // 检查有多少行受到影响
    $affected_rows = $stmt->affected_rows;
    echo "Record updated successfully. {$affected_rows} row(s) affected.";
} else {
    // 处理执行错误
    echo "Error updating record: " . $stmt->error;
}

// 关闭语句和连接
$stmt->close();
$conn->close();
?>

示例：使用预处理语句的 MySQLi（面向过程风格）

<?php
$servername = "localhost";
$username = "your_username"; // 替换为你的数据库用户名
$password = "your_password"; // 替换为你的数据库密码
$dbname = "myDB"; // 替换为你的数据库名

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

// 检查连接
if (!$conn) {
    // 在生产环境中应使用错误日志记录而非 die()
    die("Connection failed: " . mysqli_connect_error());
}

// SQL 查询，使用占位符 (?)
$sql = "UPDATE MyGuests SET lastname = ? WHERE id = ?";

// 准备语句
$stmt = mysqli_prepare($conn, $sql);

if ($stmt === false) {
    // 处理错误
    die("Error preparing statement: " . mysqli_error($conn));
}

// 要绑定的数据
$new_lastname = 'DoeProcUpdated';
$guest_id = 2;

// 绑定参数 (s = 字符串, i = 整数)
mysqli_stmt_bind_param($stmt, "si", $new_lastname, $guest_id);

// 执行语句
if (mysqli_stmt_execute($stmt)) {
    $affected_rows = mysqli_stmt_affected_rows($stmt);
    echo "Record updated successfully. {$affected_rows} row(s) affected.";
} else {
    // 处理执行错误
    echo "Error updating record: " . mysqli_stmt_error($stmt);
}

// 关闭语句和连接
mysqli_stmt_close($stmt);
mysqli_close($conn);
?>

示例：使用预处理语句的 PDO

<?php
$servername = "localhost";
$username = "your_username"; // 替换为你的数据库用户名
$password = "your_password"; // 替换为你的数据库密码
$dbname = "myDBPDO"; // 替换为你的数据库名
$charset = 'utf8mb4'; // 推荐的字符集

// Data Source Name (DSN) 数据源名称
$dsn = "mysql:host=$servername;dbname=$dbname;charset=$charset";

$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION, // 以异常形式开启错误报告
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,       // 默认获取模式为关联数组
    PDO::ATTR_EMULATE_PREPARES   => false,                  // 出于安全考虑禁用预处理语句模拟
];

try {
    // 创建 PDO 实例
    $pdo = new PDO($dsn, $username, $password, $options);

    // SQL 查询，使用命名占位符 (:lastname, :id) 或位置占位符 (?)
    // 此处为清晰起见使用命名占位符
    $sql = "UPDATE MyGuests SET lastname = :lastname WHERE id = :id";

    // 准备语句
    $stmt = $pdo->prepare($sql);

    // 要绑定的数据
    $new_lastname = 'DoePDOUpdated';
    $guest_id = 2;

    // 将值绑定到占位符
    // 也可以直接在 execute() 数组中绑定
    $stmt->bindValue(':lastname', $new_lastname, PDO::PARAM_STR);
    $stmt->bindValue(':id', $guest_id, PDO::PARAM_INT);

    /* 或者，在 execute 中绑定:
    $stmt->execute([
        ':lastname' => $new_lastname,
        ':id' => $guest_id
    ]);
    */

    // 执行语句
    $stmt->execute();

    // 检查有多少行受到影响
    $affected_rows = $stmt->rowCount();
    echo "Record updated successfully. {$affected_rows} row(s) affected.";

} catch (PDOException $e) {
    // 处理 PDO 异常（连接或查询错误）
    // 在生产环境中记录错误日志
    echo "Database error: " . $e->getMessage();
    // 要获取更多细节： throw new PDOException($e->getMessage(), (int)$e->getCode());
}

// 当 $pdo 对象超出作用域或脚本结束时，连接会自动关闭。
// 你也可以通过设置 $pdo = null; 来显式关闭。
$pdo = null;
?>

在 PDO 中选择占位符：命名占位符 (:name) 可以使查询更具可读性，尤其是在参数较多时。位置占位符 (?) 更简洁一些。

在运行其中一个示例更新 id = 2 的记录后，MyGuests 表的数据可能如下所示（取决于运行了哪个示例）：

id | firstname | lastname        | email            | reg_date
---|-----------|-----------------|------------------|--------------------
 1 | John      | Doe             | john@example.com | 2023-10-27 10:00:00
 2 | Mary      | DoeUpdated      | mary@example.com | 2023-10-27 10:05:00
...