MySQL :: MySQL 8.4 参考手册 :: 27.5.3 可更新和可插入视图

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 27 存储对象 / 27.5 使用视图 / 27.5.3 可更新和可插入视图

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 93%

27.5.3 可更新和可插入的视图27.5.3 Updatable and Insertable Views

一些视图是可更新的，对它们的引用可以用来指定在数据更改语句中要更新的表。也就是说，你可以在诸如 UPDATE、DELETE 或 INSERT 这样的语句中使用它们来更新基础表的内容。派生表和公用表表达式也可以在多表 UPDATE 和 DELETE 语句中指定，但只能用于读取数据以指定要更新或删除的行。通常，视图引用必须是可更新的，这意味着它们可以被合并而不是物化。复合视图有更复杂的规则。Some views are updatable and references to them can be used to specify tables to be updated in data change statements. That is, you can use them in statements such as UPDATE, DELETE, or INSERT to update the contents of the underlying table. Derived tables and common table expressions can also be specified in multiple-table UPDATE and DELETE statements, but can only be used for reading data to specify rows to be updated or deleted. Generally, the view references must be updatable, meaning that they may be merged and not materialized. Composite views have more complex rules.

要使视图可更新，视图中的行与基础表中的行之间必须存在一一对应关系。还有一些其他结构会使视图不可更新。更具体地说，如果视图包含以下任何内容，则不可更新：For a view to be updatable, there must be a one-to-one relationship between the rows in the view and the rows in the underlying table. There are also certain other constructs that make a view nonupdatable. To be more specific, a view is not updatable if it contains any of the following:

聚合函数或窗口函数（SUM()、MIN()、MAX()、COUNT() 等）Aggregate functions or window functions (SUM(), MIN(), MAX(), COUNT(), and so forth)
DISTINCT
GROUP BY
HAVING
UNION 或 UNION ALLUNION or UNION ALL
选择列表中的子查询Subquery in the select list
选择列表中的非依赖子查询在 INSERT 中会失败，但在 UPDATE、DELETE 中是允许的。对于选择列表中的依赖子查询，不允许有任何数据更改语句。Nondependent subqueries in the select list fail for INSERT, but are okay for UPDATE, DELETE. For dependent subqueries in the select list, no data change statements are permitted.
某些连接（参见本节后续的附加连接讨论）Certain joins (see additional join discussion later in this section)
FROM 子句中引用不可更新的视图Reference to nonupdatable view in the FROM clause
WHERE 子句中的子查询引用了 FROM 子句中的表Subquery in the WHERE clause that refers to a table in the FROM clause
仅引用字面值（在这种情况下，没有底层表可更新）Refers only to literal values (in this case, there is no underlying table to update)
ALGORITHM = TEMPTABLE（使用临时表总是会使视图不可更新）ALGORITHM = TEMPTABLE (use of a temporary table always makes a view nonupdatable)
对基表的任意列有多个引用（INSERT 会失败，UPDATE 和 DELETE 可以）Multiple references to any column of a base table (fails for INSERT, okay for UPDATE, DELETE)

视图中的生成列被视为可更新，因为可以赋入该列。然而，如果该列被显式更新，唯一允许的值是 DEFAULT有关生成列的信息，请参见第 15.1.20.8 节，“创建 TABLE 和生成列”。A generated column in a view is considered updatable because it is possible to assign to it. However, if such a column is updated explicitly, the only permitted value is DEFAULT. For information about generated columns, see Section 15.1.20.8, “CREATE TABLE and Generated Columns”.

在某些情况下，多个表的视图可能是可更新的，前提是它可以通过MERGE算法处理。为了实现这一点，视图必须使用内连接（而不是外连接或UNION）。此外，视图定义中只能更新单个表，因此SET子句必须只指定视图中某个表的列。即使理论上可更新，使用UNION ALL的视图也不允许。It is sometimes possible for a multiple-table view to be updatable, assuming that it can be processed with the MERGE algorithm. For this to work, the view must use an inner join (not an outer join or a UNION). Also, only a single table in the view definition can be updated, so the SET clause must name only columns from one of the tables in the view. Views that use UNION ALL are not permitted even though they might be theoretically updatable.

关于可插入性（可以通过 INSERT 语句进行更新），一个可更新视图如果满足以下附加的视图列要求，也可以被视为可插入视图：With respect to insertability (being updatable with INSERT statements), an updatable view is insertable if it also satisfies these additional requirements for the view columns:

视图列名称不得重复。There must be no duplicate view column names.
视图必须包含基表中所有没有默认值的列。The view must contain all columns in the base table that do not have a default value.
视图列必须是简单的列引用。它们不能是表达式，例如以下情况：The view columns must be simple column references. They must not be expressions, such as these:
```
3.14159
col1 + 3
UPPER(col2)
col3 / col4
(subquery)
```

MySQL 在 CREATE VIEW 时设置一个名为视图可更新性标志的标志。如果视图允许 UPDATE 和 DELETE（以及类似操作），则该标志设置为 YES（真）。否则，该标志设置为 NO（假）。Information Schema 的 VIEWS 表中的 IS_UPDATABLE 列显示了该标志的状态。这意味着服务器始终知道视图是否可更新。MySQL sets a flag, called the view updatability flag, at CREATE VIEW time. The flag is set to YES (true) if UPDATE and DELETE (and similar operations) are legal for the view. Otherwise, the flag is set to NO (false). The IS_UPDATABLE column in the Information Schema VIEWS table displays the status of this flag. It means that the server always knows whether a view is updatable.

如果视图不可更新，则像 UPDATE、DELETE 和 INSERT 这样的语句是非法的，会被拒绝。（即使视图可更新，也可能无法向其插入数据，如本节其他部分所述。）If a view is not updatable, statements such UPDATE, DELETE, and INSERT are illegal and are rejected. (Even if a view is updatable, it might not be possible to insert into it, as described elsewhere in this section.)

视图的可更新性可能会受到 updatable_views_with_limit 系统变量值的影响。参见第 7.1.8 节，“服务器系统变量”。The updatability of views may be affected by the value of the updatable_views_with_limit system variable. See Section 7.1.8, “Server System Variables”.

对于以下讨论，假设这些表和视图存在：For the following discussion, suppose that these tables and views exist:

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, UPDATE, and DELETE statements are permitted as follows:

INSERT：INSERT语句的插入表可以是一个合并的视图引用。如果视图是连接视图，则视图的所有组件必须是可更新的（非物化）。对于多表可更新视图，如果只插入到单个表中，INSERT 可以正常工作。INSERT: The insert table of an INSERT statement may be a view reference that is merged. If the view is a join view, all components of the view must be updatable (not materialized). For a multiple-table updatable view, INSERT can work if it inserts into a single table.
此语句无效，因为连接视图的一个组件不可更新：This statement is invalid because one component of the join view is nonupdatable:
```
INSERT INTO vjoin (c) VALUES (1);
```
此语句有效；视图不包含任何物化组件：This statement is valid; the view contains no materialized components:
```
INSERT INTO vup (c) VALUES (1);
```
UPDATE：在 UPDATE 语句中要更新的表或表集合可能是合并的视图引用。如果视图是联接视图，则视图的至少一个组件必须是可更新的（这与 INSERT 不同）。UPDATE: The table or tables to be updated in an UPDATE statement may be view references that are merged. If a view is a join view, at least one component of the view must be updatable (this differs from INSERT).
在多表 UPDATE 语句中，语句更新的表引用必须是基表或可更新视图引用。未更新的表引用可以是物化视图或派生表。In a multiple-table UPDATE statement, the updated table references of the statement must be base tables or updatable view references. Nonupdated table references may be materialized views or derived tables.
此语句有效；列 c 来自联接视图中可更新的部分：This statement is valid; column c is from the updatable part of the join view:
```
UPDATE vjoin SET c=c+1;
```
此语句无效；列 x 来自不可更新的部分：This statement is invalid; column x is from the nonupdatable part:
```
UPDATE vjoin SET x=x+1;
```
此语句有效；多表 UPDATE 的更新表引用是可更新视图 (vup)：This statement is valid; the updated table reference of the multiple-table UPDATE is an updatable view (vup):
```
UPDATE vup JOIN (SELECT SUM(x) AS s FROM t1) AS dt ON ...
SET c=c+1;
```
此语句无效；它试图更新一个物化派生表：This statement is invalid; it tries to update a materialized derived table:
```
UPDATE vup JOIN (SELECT SUM(x) AS s FROM t1) AS dt ON ...
SET s=s+1;
```
DELETE：在DELETE语句中，要删除的表必须是合并视图。不允许使用连接视图（这与INSERT和UPDATE不同）。DELETE: The table or tables to be deleted from in a DELETE statement must be merged views. Join views are not allowed (this differs from INSERT and UPDATE).
此语句无效，因为该视图是连接视图：This statement is invalid because the view is a join view:
```
DELETE vjoin WHERE ...;
```
此语句有效，因为该视图是合并（可更新）视图：This statement is valid because the view is a merged (updatable) view:
```
DELETE vup WHERE ...;
```
此语句有效，因为它从合并（可更新）视图中删除：This statement is valid because it deletes from a merged (updatable) view:
```
DELETE vup FROM vup JOIN (SELECT SUM(x) AS s FROM t1) AS dt ON ...;
```

以下提供进一步讨论和示例。Additional discussion and examples follow.

本节前面的讨论指出，如果视图的所有列不是简单的列引用（例如，如果包含表达式列或复合表达式列），则该视图不可插入。尽管这样的视图不可插入，但如果只更新非表达式列，它仍然可以被更新。考虑以下视图：Earlier discussion in this section pointed out that a view is not insertable if not all columns are simple column references (for example, if it contains columns that are expressions or composite expressions). Although such a view is not insertable, it can be updatable if you update only columns that are not expressions. Consider this view:

CREATE VIEW v AS SELECT col1, 1 AS col2 FROM t;

该视图不可插入，因为col2是一个表达式。但如果更新不尝试更新col2，则它是可更新的。以下更新是允许的：This view is not insertable because col2 is an expression. But it is updatable if the update does not try to update col2. This update is permissible:

UPDATE v SET col1 = 0;

该更新不可允许，因为它试图更新一个表达式列：This update is not permissible because it attempts to update an expression column:

UPDATE v SET col2 = 0;

如果一个表包含一个 AUTO_INCREMENT 列，向该表上可插入的视图插入数据而不包括 AUTO_INCREMENT 列，则不会改变 LAST_INSERT_ID() 的值，因为向不属于视图的列插入默认值的副作用不应可见。If a table contains an AUTO_INCREMENT column, inserting into an insertable view on the table that does not include the AUTO_INCREMENT column does not change the value of LAST_INSERT_ID(), because the side effects of inserting default values into columns not part of the view should not be visible.

首页上一级下一节