MySQL - UPDATE 查询

MySQL - UPDATE 语句

本教程将介绍如何使用 UPDATE 语句修改 MySQL 表中现有的记录。我们将探讨其语法、安全执行的最佳实践、结合联接（JOIN）的高级用法，以及如何从各种编程语言中执行更新操作。

理解 MySQL UPDATE 语句

MySQL 的 UPDATE 语句用于修改表中的现有记录。它是一种数据操作语言（Data Manipulation Language, DML）命令，因为它只影响表内的数据，而不影响表的结构（如列或索引）。

使用 UPDATE 语句时，务必小心。如果您在执行 UPDATE 语句时没有 WHERE 子句，表中的所有行都将被修改。这可能导致不可逆的数据丢失。WHERE 子句对于精确指定您想要更改的行至关重要。

语法

UPDATE 命令的基本语法如下：

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

• 您可以在单个语句中更新一个或多个列。
• WHERE 子句用于筛选要更新的记录。如果省略，所有记录都将被更新。
• UPDATE 语句一次操作一个表（除非使用 JOIN）。

更新数据：实践示例

让我们设置一个示例表来进行操作。我们将创建一个带有自增主键（AUTO_INCREMENT PRIMARY KEY）的现代化 customers 表，这是一种常见的最佳实践。

示例设置

首先，创建 customers 表：

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL,
    address VARCHAR(255),
    salary DECIMAL(10, 2)
);

现在，插入一些示例数据。请注意，我们不需要提供 id，因为它被设置为 AUTO_INCREMENT（自增）。

INSERT INTO customers (name, age, address, salary)
VALUES
    ('Ramesh', 32, 'Ahmedabad', 2000.00),
    ('Khilan', 25, 'Delhi', 1500.00),
    ('Kaushik', 23, 'Kota', 2000.00),
    ('Chaitali', 25, 'Mumbai', 6500.00),
    ('Hardik', 27, 'Bhopal', 8500.00),
    ('Komal', 22, 'Hyderabad', 4500.00),
    ('Muffy', 24, 'Indore', 10000.00);

让我们查看数据：

SELECT * FROM customers;

id	姓名	年龄	地址	薪资
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

示例 1：更新单条记录

让我们将 id 为 6 的客户名称更新为 ‘Nikhilesh’。

UPDATE customers
SET name = 'Nikhilesh'
WHERE id = 6;

为了验证更改，我们再次查询数据：

SELECT * FROM customers WHERE id = 6;

输出显示了更新后的名称：

id	姓名	年龄	地址	薪资
6	Nikhilesh	22	Hyderabad	4500.00

示例 2：更新多条记录

您可以更新符合条件的多个记录。让我们将 id 为 3 或 6 的所有客户地址更改为 ‘Visakhapatnam’。更简洁的方法是使用 IN 运算符。

UPDATE customers
SET address = 'Visakhapatnam'
WHERE id IN (3, 6);

运行此查询后，customers 表将如下所示：

id	姓名	年龄	地址	薪资
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Visakhapatnam	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Nikhilesh	22	Visakhapatnam	4500.00
7	Muffy	24	Indore	10000.00

安全更新的最佳实践

1. 始终使用 WHERE 子句

这是最重要的规则。在运行 UPDATE 语句之前，最佳实践是先使用相同的 WHERE 子句运行 SELECT 语句，以预览将受影响的行。

-- 首先，预览要更新的行
SELECT * FROM customers WHERE age < 25;

-- 如果选择正确，则运行更新
UPDATE customers SET salary = salary * 1.05 WHERE age < 25;

2. 使用事务

对于关键更新，请将您的语句包装在事务（Transaction）中。这允许您在出现问题时撤消（UNDO）更改。InnoDB（现代 MySQL 中的默认存储引擎）等存储引擎支持此功能。

START TRANSACTION;

-- 执行更新
UPDATE customers SET address = 'Unknown' WHERE id = 5;

-- 检查结果
SELECT * FROM customers WHERE id = 5;

-- 如果更改正确，则使其永久生效
-- COMMIT;

-- 如果出现问题，撤消更改
-- ROLLBACK;

3. 注意安全更新模式

许多 MySQL 客户端默认启用“安全更新模式”（sql_safe_updates）。此模式会阻止 UPDATE 或 DELETE 语句在 WHERE 子句中不使用键列或不使用 LIMIT 子句的情况。如果您遇到有关安全更新的错误，您的查询可能过于宽泛且存在潜在危险。要继续操作，您必须要么细化您的 WHERE 子句以使用索引键，要么（如果您确定）暂时为您的会话禁用此模式，使用 SET SQL_SAFE_UPDATES = 0;。

从客户端程序更新（现代方法）

从应用程序（如 PHP、Node.js、Java 或 Python）更新数据时，最关键的最佳实践是使用预处理语句（Prepared Statements，或参数化查询）。此技术可防止 SQL 注入（SQL Injection），这是一种常见且严重的安全漏洞。

以下示例演示了执行 `UPDATE` 操作的现代、安全方式。

PHP（使用 PDO）

PHP 数据对象（PHP Data Objects, PDO）是 PHP 中与数据库交互的现代推荐方式。它提供了一致的面向对象 API，并有助于使用预处理语句（Prepared Statements）防止 SQL 注入。

<?php
$host = 'localhost';
$db   = 'your_database';
$user = 'your_username';
$pass = 'your_password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

$newAddress = 'Pune';
$customerId = 4;

$sql = "UPDATE customers SET address = ? WHERE id = ?";

$stmt = $pdo->prepare($sql);
$stmt->execute([$newAddress, $customerId]);

$affectedRows = $stmt->rowCount();
echo "Update successful. {$affectedRows} row(s) affected.";
?>

Node.js（使用 mysql2/promise 和 async/await）

在 Node.js 中，使用 mysql2/promise 结合 async/await 语法和连接池（Connection Pools）提供了一种现代、可读且高效的数据库操作方式。

const mysql = require('mysql2/promise');

async function main() {
    const pool = mysql.createPool({
        host: 'localhost',
        user: 'your_username',
        password: 'your_password',
        database: 'your_database',
        waitForConnections: true,
        connectionLimit: 10,
        queueLimit: 0
    });

    const newSalary = 9000.00;
    const customerId = 5;

    const sql = 'UPDATE customers SET salary = ? WHERE id = ?';

    try {
        const [result] = await pool.execute(sql, [newSalary, customerId]);
        console.log(`Update successful. ${result.affectedRows} row(s) changed.`);
    } catch (error) {
        console.error('Failed to update record:', error);
    } finally {
        await pool.end(); // 关闭连接池中的所有连接
    }
}

main();

Java（使用 JDBC 和 try-with-resources）

现代 Java 使用 try-with-resources 来自动管理连接和语句等资源，防止资源泄露。PreparedStatement 用于确保安全性。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class UpdateRecord {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/your_database";
        String user = "your_username";
        String password = "your_password";

        String sql = "UPDATE customers SET age = ? WHERE id = ?";

        try (Connection conn = DriverManager.getConnection(url, user, password);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, 26); // 设置新年龄
            pstmt.setInt(2, 2);  // 设置客户ID

            int affectedRows = pstmt.executeUpdate();

            System.out.println("Update successful. " + affectedRows + " row(s) affected.");

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Python（使用 mysql-connector-python 和 with 语句）

Python 的 with 语句确保资源得到妥善管理。连接器使用 execute() 方法和一个参数元组来防止 SQL 注入。

import mysql.connector
from mysql.connector import errorcode

def update_customer():
    config = {
        'user': 'your_username',
        'password': 'your_password',
        'host': 'localhost',
        'database': 'your_database'
    }

    sql = "UPDATE customers SET salary = %s WHERE id = %s"
    data = (7000.00, 4) # 新薪资和客户ID

    try:
        with mysql.connector.connect(**config) as conn:
            with conn.cursor() as cursor:
                cursor.execute(sql, data)
                conn.commit() # 重要：提交事务
                print(f"Update successful. {cursor.rowcount} row(s) affected.")
    except mysql.connector.Error as err:
        print(f"Error: {err}")

if __name__ == '__main__':
    update_customer()