SQL 可更新视图

学习视图更新 · 难度：高级 · +15XP

SQL 可更新视图

SQL 可更新视图 — 通过视图修改数据

学习前的准备

你需要一个 MySQL 数据库环境。本教程假设你已经了解了视图的基本概念——视图本质上是一个“保存的查询”。我们将深入探讨可更新视图：通过 INSERT、UPDATE、DELETE 来修改数据。

回顾视图

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    dept VARCHAR(50),
    salary DECIMAL(10,2),
    status VARCHAR(20) DEFAULT '在职'
);
CREATE VIEW active_employees AS
SELECT id, name, dept, salary
FROM employees
WHERE status = '在职';

什么是可更新视图？

视图满足以下条件时才能更新：基于单个表、没有聚合函数、没有 DISTINCT、没有 GROUP BY/HAVING、没有 UNION。

-- 可更新视图
CREATE VIEW v_tech AS
SELECT id, name, salary
FROM employees
WHERE dept = '技术部';
-- 通过视图插入
INSERT INTO v_tech (name, salary) VALUES ('赵六', 7500);
-- 通过视图更新
UPDATE v_tech SET salary = 9500 WHERE name = '王五';
-- 通过视图删除
DELETE FROM v_tech WHERE name = '赵六';

不可更新的视图

以下视图不可更新：使用了聚合函数（SUM、COUNT、AVG 等）、多表 JOIN、DISTINCT。

WITH CHECK OPTION — 保持数据一致性

CREATE VIEW tech_employees AS
SELECT id, name, salary
FROM employees
WHERE dept = '技术部'
WITH CHECK OPTION;
-- 下面这条会失败——更新后数据不满足视图条件
UPDATE tech_employees SET dept = '市场部' WHERE name = '王五';
-- ERROR: CHECK OPTION failed

LOCAL vs CASCADED 检查选项

CASCADED（默认）检查所有底层视图的条件，LOCAL 只检查当前视图的条件。

-- CASCADED：层层检查
CREATE VIEW v1 AS SELECT * FROM employees WHERE salary > 5000;
CREATE VIEW v2 AS SELECT * FROM v1 WHERE salary < 10000
WITH CASCADED CHECK OPTION;
-- LOCAL：只检查当前层
CREATE VIEW v3 AS SELECT * FROM employees WHERE salary > 5000;
CREATE VIEW v4 AS SELECT * FROM v3 WHERE salary < 10000
WITH LOCAL CHECK OPTION;

实际应用：行级安全控制

CREATE VIEW tech_manager_view AS
SELECT id, name, salary
FROM employees
WHERE dept = '技术部'
WITH CHECK OPTION;
GRANT SELECT, INSERT, UPDATE, DELETE
ON tech_manager_view TO 'tech_manager'@'localhost';
-- 技术经理只能查看和管理技术部数据

小结

可更新视图让你能像操作普通表一样通过视图修改数据，前提是视图基于单表且不含聚合/DISTINCT/GROUP BY。WITH CHECK OPTION 确保数据不会“丢失”在视图之外。可更新视图在权限控制和数据隔离方面非常实用。