Skip to content
W3S101

MySQL - 更新视图

MySQL - 更新视图

Section titled “MySQL - 更新视图”
本教程将介绍如何使用 `UPDATE` 语句通过 MySQL 视图修改底层表中的数据。我们将探讨其语法、实际示例、可更新视图的限制，以及如何按照现代最佳实践从各种编程语言中执行这些操作。

理解 MySQL 视图

Section titled “理解 MySQL 视图”

MySQL 视图（View）是基于 SQL 语句结果集的虚拟表。它像真实表一样包含行和列。视图通常用于封装复杂查询、简化数据访问，并通过只向用户公开特定列或行来强制执行安全性。

虽然视图本身不存储数据，但有些视图是“可更新的”（updatable）。这意味着您可以在视图上使用 DML（数据操纵语言，Data Manipulation Language）语句，例如 UPDATE、INSERT 或 DELETE，MySQL 会将这些更改应用到底层的基表。本教程重点介绍 UPDATE 操作。

视图上的 UPDATE 语句

Section titled “视图上的 UPDATE 语句”

要通过视图更新数据，您可以使用标准的 UPDATE 语句，引用视图名称就像它是一个表一样。更改会传播到创建该视图的基表。

语法

Section titled “语法”

更新视图的语法与更新表相同：

UPDATE view_name
SET column1 = value1, column2 = value2, ...
WHERE [condition];

⚠️ 重要提示： WHERE 子句至关重要。如果没有它，UPDATE 语句将修改视图中的所有行（从而修改基表）。在执行 UPDATE 语句之前，请务必先使用 SELECT 语句测试您的 WHERE 子句。

先决条件：创建基表和视图

Section titled “先决条件：创建基表和视图”

首先，让我们设置一个名为 employees 的示例表，并向其中填充数据。

CREATE TABLE employees (
    id INT NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    age INT NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    PRIMARY KEY(id)
);


INSERT INTO employees (first_name, last_name, age, department, salary) VALUES
('Ramesh', 'Sharma', 32, 'Engineering', 75000.00),
('Khilan', 'Patel', 25, 'Marketing', 55000.00),
('Kaushik', 'Gupta', 23, 'Sales', 60000.00),
('Chaitali', 'Verma', 26, 'Engineering', 80000.00),
('Hardik', 'Pandya', 27, 'HR', 62000.00),
('Komal', 'Singh', 22, 'Sales', 58000.00),
('Muffy', 'John', 24, 'Marketing', 56000.00);

现在，让我们创建一个视图，它只显示“Engineering”（工程）部门的员工。这是安全和数据简化的常见用例。

CREATE VIEW engineering_staff AS
SELECT id, first_name, last_name, age, salary
FROM employees
WHERE department = 'Engineering';

您可以验证视图的内容：

SELECT * FROM engineering_staff;
idfirst_namelast_nameagesalary
1RameshSharma3275000.00
4ChaitaliVerma2680000.00

示例：通过视图更新单行数据

Section titled “示例：通过视图更新单行数据”

让我们通过 engineering_staff 视图更新 Ramesh 的工资，给他升职。

UPDATE engineering_staff SET salary = 85000.00 WHERE id = 1;

验证

Section titled “验证”

要确认更改，您可以查询视图和基表 employees。更新将在两者中反映出来。

SELECT salary FROM engineering_staff WHERE id = 1; -- Returns 85000.00
SELECT salary FROM employees WHERE id = 1;       -- Also returns 85000.00

可更新视图的限制

Section titled “可更新视图的限制”

并非所有视图都是可更新的。如果视图包含以下任何内容，UPDATE 语句将失败：

  • 聚合函数（如 SUM()、COUNT()、AVG() 等）
  • DISTINCT（去重）
  • GROUP BY 子句
  • HAVING 子句
  • UNION 或 UNION ALL 操作符
  • 联接（JOINs）（有部分例外）
  • SELECT 列表中引用正在更新的表的子查询

本质上，要使视图可更新，MySQL 必须能够将视图的行和列追溯到单个底层表的行和列。

使用客户端程序更新视图

Section titled “使用客户端程序更新视图”

从应用程序执行数据库操作是一种标准实践。下面是使用 Node.js、Python 和 PHP 中现代代码更新视图的示例。所有示例都使用预处理语句（prepared statements）来防止 SQL 注入（SQL injection），这是一项关键的安全实践。

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

Section titled “Node.js（使用 mysql2/promise 和 async/await）”

此示例使用流行的 mysql2 库和 Promises，以编写整洁、现代的异步代码。首先，设置您的项目：运行 npm init -y 和 npm install mysql2 dotenv。

require('dotenv').config(); // For loading credentials from a .env file
const mysql = require('mysql2/promise');


async function main() {
  let connection;
  try {
    // Best practice: Use environment variables for credentials
    connection = await mysql.createConnection({
      host: process.env.DB_HOST || 'localhost',
      user: process.env.DB_USER || 'root',
      password: process.env.DB_PASSWORD || 'password',
      database: process.env.DB_NAME || 'your_database'
    });


    console.log('Successfully connected to the database.');


    const newSalary = 82000.00;
    const employeeId = 4;


    const updateQuery = 'UPDATE engineering_staff SET salary = ? WHERE id = ?';
    const [result] = await connection.execute(updateQuery, [newSalary, employeeId]);


    if (result.affectedRows > 0) {
      console.log(`Successfully updated employee ID ${employeeId}.`);
    } else {
      console.log(`Employee with ID ${employeeId} not found or no change was needed.`);
    }


  } catch (error) {
    console.error('An error occurred:', error.message);
  } finally {
    if (connection) {
      await connection.end();
      console.log('Database connection closed.');
    }
  }
}


main();

Python（使用 mysql-connector-python）

Section titled “Python（使用 mysql-connector-python）”

此示例使用官方的 MySQL Python 连接器。通过 pip install mysql-connector-python 命令安装。

import mysql.connector
import os # For environment variables


try:
    # It's recommended to use environment variables for sensitive data
    connection = mysql.connector.connect(
        host=os.getenv('DB_HOST', 'localhost'),
        user=os.getenv('DB_USER', 'root'),
        password=os.getenv('DB_PASSWORD', 'password'),
        database=os.getenv('DB_NAME', 'your_database')
    )


    cursor = connection.cursor()


    new_salary = 83000.00
    employee_id = 4


    update_query = "UPDATE engineering_staff SET salary = %s WHERE id = %s"
    cursor.execute(update_query, (new_salary, employee_id))


    # Commit the transaction to make the changes permanent
    connection.commit()


    if cursor.rowcount > 0:
        print(f"Successfully updated {cursor.rowcount} row(s) for employee ID {employee_id}.")
    else:
        print(f"Employee with ID {employee_id} not found or no update was made.")


except mysql.connector.Error as err:
    print(f"Error: {err}")
finally:
    if 'connection' in locals() and connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed.")

PHP（使用 PDO）

Section titled “PHP（使用 PDO）”

PDO (PHP Data Objects) 是 PHP 中与数据库交互的现代推荐方式，因为它提供了一致的接口并支持预处理语句。

<?php
$host = $_ENV['DB_HOST'] ?? 'localhost';
$db   = $_ENV['DB_NAME'] ?? 'your_database';
$user = $_ENV['DB_USER'] ?? 'root';
$pass = $_ENV['DB_PASSWORD'] ?? '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);
    echo "Connected to the database successfully.\n";


    $newSalary = 84000.00;
    $employeeId = 4;


    $sql = "UPDATE engineering_staff SET salary = :salary WHERE id = :id";
    $stmt = $pdo->prepare($sql);


    $stmt->execute(['salary' => $newSalary, 'id' => $employeeId]);


    $rowCount = $stmt->rowCount();
    if ($rowCount > 0) {
        echo "Successfully updated {$rowCount} record(s) for employee ID {$employeeId}.\n";
    } else {
        echo "Employee with ID {$employeeId} not found or data was unchanged.\n";
    }


} catch (\PDOException $e) {
    // In a real application, you would log this error, not display it to the user
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}
?>