14.2 更新视图数据

在上一节中，我们学习了如何创建和使用视图。视图作为一种虚拟表，不仅可以用于查询，在某些条件下还可以通过视图对基础表进行插入（INSERT）、更新（UPDATE）和删除（DELETE） 操作。这种能够修改数据的视图称为可更新视图。本节将详细讲解更新视图数据的条件、方法以及注意事项。

一、视图更新的本质

对视图执行更新操作，实际上是在修改视图所依赖的基础表中的数据。视图本身不存储数据，它只是一个映射窗口。因此，视图必须能够将修改操作明确地映射到基础表的对应行和列上，才能支持更新。

二、可更新视图的条件

MySQL 中，一个视图要成为可更新视图，必须满足以下条件（严格限制）：

1、视图的查询不能包含以下任何元素：

• 聚合函数（如 SUM(), AVG(), COUNT() 等）

• DISTINCT 关键字

• GROUP BY 子句

• HAVING 子句

• UNION 或 UNION ALL

• 子查询（某些情况下，子查询在 SELECT 列表中会使其不可更新）

• 连接（即不能涉及多张表）——这是最常见的限制

• 对系统表的引用

• 存储过程或函数调用

2、视图中的列必须直接映射到基础表的列，不能是表达式、函数或常量。例如，SELECT name, age + 1 AS new_age 这样的列不可更新。

3、视图定义的 FROM 子句只能引用一张表（不能是多表连接）。不过，MySQL 对某些使用连接但可更新的情况有特殊限制，通常认为多表视图不可更新。

4、基础表必须存在且有相应的权限。

5、视图中没有使用 WITH CHECK OPTION 但数据仍然必须符合视图定义的 WHERE 条件？实际上，WITH CHECK OPTION 是用来约束更新的，不是必须条件。

如果视图满足上述条件，它就可以像普通表一样接受 INSERT、UPDATE、DELETE 操作。

三、可更新视图示例

示例1：基于单表的简单视图

假设我们有一个 employees 表：

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10,2),
    hire_date DATE
);

INSERT INTO employees (name, department, salary, hire_date) VALUES
('张三', '技术部', 8000, '2023-01-10'),
('李四', '市场部', 7000, '2023-02-15'),
('王五', '技术部', 9000, '2023-03-20');

现在，我们创建一个只包含技术部员工的视图：

CREATE VIEW tech_employees AS
SELECT id, name, salary, hire_date
FROM employees
WHERE department = '技术部';

这个视图满足可更新条件：

• 只涉及一张表

• 没有聚合、分组、连接等

• 列都是直接映射

通过视图更新数据

1. 更新操作

-- 通过视图将张三的工资增加到 8500
UPDATE tech_employees SET salary = 8500 WHERE name = '张三';

-- 查询基础表，确认数据已修改
SELECT * FROM employees WHERE name = '张三';

2. 插入操作

通过视图插入新记录时，需要满足视图的 WHERE 条件（即插入的行也应当属于“技术部”），否则插入后该行可能不会显示在视图中（但基础表中仍然存在）。

-- 向视图插入一条新记录
INSERT INTO tech_employees (name, salary, hire_date) 
VALUES ('赵六', 7500, '2024-01-10');

-- 检查基础表
SELECT * FROM employees;
-- 新插入的赵六，其 department 字段为 NULL？注意：我们的视图定义中只映射了 id,name,salary,hire_date，没有 department 列。

这里有一个关键问题：视图 tech_employees 没有包含 department 列，但基础表的 department 列是 NOT NULL？实际上在我们的表中 department 没有非空约束，所以插入时 department 会被设为默认值 NULL（或没有默认值时报错）。但我们的视图 WHERE 条件是 department = '技术部'，插入的 department 为 NULL，显然不满足条件，因此插入后该行不会出现在视图中，但基础表中确实多了一行。这可能导致数据不一致——视图看起来没变，但表变了。

为了避免这种情况，可以使用 WITH CHECK OPTION（稍后介绍）。

3. 删除操作

-- 通过视图删除李四（注意李四不在视图中，因为部门是市场部，所以不会删除）
DELETE FROM tech_employees WHERE name = '李四'; -- 影响行数为0

-- 删除张三
DELETE FROM tech_employees WHERE name = '张三';
-- 基础表中张三被删除

四、不可更新视图示例

大多数复杂的视图都是不可更新的。例如，涉及连接、聚合、分组等的视图。

示例2：连接视图（不可更新）

CREATE VIEW employee_dept AS
SELECT e.id, e.name, e.salary, d.dept_name
FROM employees e
JOIN departments d ON e.department_id = d.id;

对这个视图执行更新会报错：

UPDATE employee_dept SET salary = 10000 WHERE id = 1;
-- 错误：The target table employee_dept of the UPDATE is not updatable

示例3：包含聚合函数的视图

CREATE VIEW dept_avg_salary AS
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

此视图不可更新。

五、WITH CHECK OPTION：确保数据一致性

WITH CHECK OPTION 是创建视图时的一个可选子句，用于限制通过视图进行的插入和更新操作，必须使修改后的行仍然满足视图的 WHERE 条件。这样可以保证视图的数据始终与定义一致。

语法

CREATE VIEW 视图名 AS
查询语句
WITH [LOCAL | CASCADED] CHECK OPTION;

• LOCAL：只检查当前视图的条件。

• CASCADED（默认）：不仅检查当前视图的条件，还递归检查所有依赖视图的条件。

示例：使用 WITH CHECK OPTION

重新创建技术部视图，并加上 WITH CHECK OPTION：

CREATE VIEW tech_employees_check AS
SELECT id, name, salary, hire_date
FROM employees
WHERE department = '技术部'
WITH CHECK OPTION;

现在尝试插入一条不属于技术部的记录：

-- 尝试插入市场部的员工
INSERT INTO tech_employees_check (name, salary, hire_date) 
VALUES ('钱七', 6000, '2024-02-01');

由于我们没有指定 department，基础表的 department 会为 NULL，不满足 department = '技术部'，因此插入被拒绝，报错：

ERROR 1369 (HY000): CHECK OPTION failed 'test.tech_employees_check'

同样，更新操作也会检查：如果通过视图将某员工的部门改为非技术部，也会被拒绝（前提是视图中有 department 列，但本例中没有，所以无法通过视图修改 department，因此不会触发检查）。

如果视图包含条件列，则 WITH CHECK OPTION 就能发挥作用。例如，在视图中包含 department 列：

CREATE VIEW tech_employees2 AS
SELECT id, name, department, salary, hire_date
FROM employees
WHERE department = '技术部'
WITH CHECK OPTION;

此时，尝试通过视图将某个员工的部门改为市场部：

UPDATE tech_employees2 SET department = '市场部' WHERE id = 1;
-- 错误：CHECK OPTION failed

六、更新视图的注意事项

1. 并非所有视图都可更新：设计视图时，如果需要更新，必须遵守可更新视图的条件。

2. 谨慎使用视图更新：通过视图更新数据可能会绕过基础表的某些约束或触发器的逻辑，但外键约束、非空约束等仍然会生效。

3. WITH CHECK OPTION 的重要性：如果视图有过滤条件，强烈建议加上 WITH CHECK OPTION，以避免插入或更新后数据“消失”的现象。

4. 多表视图不可更新：在 MySQL 中，涉及多表连接的视图通常不可更新。如果确实需要更新多表，应直接操作基础表。

5. 权限：要对视图执行更新，需要拥有对基础表的相应权限（INSERT、UPDATE、DELETE），而不仅仅是视图的权限。

七、练习

1. 创建一个包含 students 表（id, name, age, class_id）中所有年龄大于18岁的学生的视图，并允许通过该视图更新学生年龄，但要确保更新后的年龄仍然大于18。

2. 尝试向该视图插入一个年龄为17岁的学生，观察结果。

3. 如果移除 WITH CHECK OPTION，再试一次，观察结果。

小结

• 可更新视图 允许通过视图对基础表执行 INSERT、UPDATE、DELETE 操作。

• 视图必须基于单表，且查询中不能包含聚合、分组、连接、子查询等复杂元素，才能成为可更新视图。

• WITH CHECK OPTION 可以防止通过视图插入或更新后数据不再满足视图条件，保证视图数据的一致性。

• 更新视图应谨慎使用，理解其限制和潜在影响。