23.5.3 可更新和可插入的视图
一些视图是可更新的,对其的引用可用于指定要在数据更改语句中更新的 table。也就是说,您可以在诸如UPDATE,DELETE或INSERT之类的语句中使用它们来更新基础 table 的内容。也可以在多 tableUPDATE和DELETE语句中指定派生 table,但只能用于读取数据以指定要更新或删除的行。通常,视图引用必须是可更新的,这意味着它们可能会合并而不实现。复合视图具有更复杂的规则。
为了使视图可更新,视图中的行与基础 table 中的行之间必须存在一对一的关系。还有某些其他构造会使视图不可更新。更具体地说,如果视图包含以下任何内容,则该视图不可更新:
在 MySQL 5.7.11 之前,选择列 table 中的子查询对INSERT失败,但对UPDATE,DELETE没问题。从 MySQL 5.7.11 开始,对于非依赖子查询仍然如此。对于选择列 table 中的相关子查询,不允许使用数据更改语句。
-
某些联接(请参阅本节后面的其他联接讨论)
-
FROM
子句中对不可更新视图的引用 -
WHERE
子句中的子查询引用了FROM
子句中的 table -
仅引用 Literals 值(在这种情况下,没有要更新的基础 table)
-
ALGORITHM = TEMPTABLE
(使用临时 table 总是使视图不可更新)
视图中的生成列被认为是可更新的,因为可以对其进行分配。但是,如果显式更新了此列,则唯一允许的值为DEFAULT
。有关生成的列的信息,请参见第 13.1.18.7 节“创建 table 和生成的列”。
假设可以使用MERGE
算法对其进行处理,则有时可以更新多 table 视图。为此,视图必须使用内部联接(而不是外部联接或UNION)。此外,视图定义中的单个 table 只能更新,因此SET
子句必须仅命名视图中 table 之一中的列。尽管使用UNION ALL的视图在理论上可能是可更新的,但这些视图是不允许的。
关于可插入性(可通过INSERT语句更新),如果可更新视图还满足视图列的以下其他要求,则可插入该视图:
-
视图列名称不得重复。
-
该视图必须包含基本 table 中所有没有默认值的列。
-
视图列必须是简单的列引用。它们不能是以下 table 达式:
3.14159
col1 + 3
UPPER(col2)
col3 / col4
(subquery)
MySQL 在CREATE VIEW时间设置一个标志,称为视图可更新性标志。如果UPDATE和DELETE(及类似操作)对该视图合法,则将标志设置为YES
(真)。否则,该标志设置为NO
(假)。 INFORMATION_SCHEMA.VIEWStable 中的IS_UPDATABLE
列显示此标志的状态。
如果视图不可更新,则UPDATE,DELETE和INSERT之类的语句是非法的,将被拒绝。 (即使视图是可更新的,也可能无法插入其中,如本节其他地方所述.)
如果一个视图依赖一个或多个其他视图,并且这些基础视图之一已更新,则IS_UPDATABLE
标志可能不可靠。无论IS_UPDATABLE
值如何,服务器都会跟踪视图的可更新性,并正确拒绝对不可更新的视图的数据更改操作。如果由于基础视图的更改而导致视图的IS_UPDATABLE
值变得不准确,则可以通过删除并重新创建视图来更新该值。
视图的可更新性可能受到updatable_views_with_limit系统变量的值的影响。参见第 5.1.7 节“服务器系统变量”。
对于以下讨论,假设存在这些 table 和视图:
CREATE TABLE t1 (x INTEGER);
CREATE TABLE t2 (c INTEGER);
CREATE VIEW vmat AS SELECT SUM(x) AS s FROM t1;
CREATE VIEW vup AS SELECT * FROM t2;
CREATE VIEW vjoin AS SELECT * FROM vmat JOIN vup ON vmat.s=vup.c;
允许使用INSERT,UPDATE和DELETE语句,如下所示:
- INSERT:INSERT语句的插入 table 可能是合并的视图引用。如果该视图是联接视图,则该视图的所有组件都必须是可更新的(未实现)。对于多 table 可更新视图,如果INSERT插入到单个 table 中,则它可以工作。
该语句无效,因为联接视图的一个组件不可更新:
INSERT INTO vjoin (c) VALUES (1);
此声明有效;该视图不包含具体化的组件:
INSERT INTO vup (c) VALUES (1);
在多 tableUPDATE语句中,该语句的更新 table 引用必须是基本 table 或可更新视图引用。未更新的 table 引用可以是物化视图或派生 table。
此声明有效; c
列来自联接视图的可更新部分:
UPDATE vjoin SET c=c+1;
该语句无效; x
列来自不可更新部分:
UPDATE vjoin SET x=x+1;
此声明有效;多 tableUPDATE的更新 table 引用是可更新的视图(vup
):
UPDATE vup JOIN (SELECT SUM(x) AS s FROM t1) AS dt ON ...
SET c=c+1;
该语句无效;它尝试更新实例化派生 table:
UPDATE vup JOIN (SELECT SUM(x) AS s FROM t1) AS dt ON ...
SET s=s+1;
该语句无效,因为该视图是联接视图:
DELETE vjoin WHERE ...;
该语句有效,因为该视图是合并(可更新)视图:
DELETE vup WHERE ...;
该语句是有效的,因为它从合并(可更新)视图中删除:
DELETE vup FROM vup JOIN (SELECT SUM(x) AS s FROM t1) AS dt ON ...;
接下来是其他讨论和示例。
本节前面的讨论指出,如果并非所有列都是简单的列引用(例如,如果它包含作为 table 达式或复合 table 达式的列),则视图不可插入。尽管这种视图不可插入,但是如果仅更新不是 table 达式的列,则可以更新该视图。考虑以下视图:
CREATE VIEW v AS SELECT col1, 1 AS col2 FROM t;
该视图不可插入,因为col2
是一个 table 达式。但是,如果更新不尝试更新col2
,则是可更新的。允许此更新:
UPDATE v SET col1 = 0;
不允许进行此更新,因为它尝试更新 table 达式列:
UPDATE v SET col2 = 0;
如果 table 包含AUTO_INCREMENT
列,则插入不包含AUTO_INCREMENT
列的 table 的可插入视图不会更改LAST_INSERT_ID()的值,因为将默认值插入不属于视图的列中的副作用应该不可见。