在 MySQL 中更新视图 [英] Updating Views in MySQL

问题描述

我正在创建一个视图来向用户显示他/她的数据，但我也希望用户能够在这些视图中的某些字段中进行更改.视图中所做的更改是否也反映在基表中?

I am creating a view to show the user his/her data, but I also want the user to be able to make changes in some of the fields in those views. Are the changes made in a view reflected in the base table as well?

另外，我能否更新由多个基表组成的视图?

Also, would I be able to update a view that is made up of more than one base table?

推荐答案

如 可更新和可插入的视图:

某些视图是可更新的.也就是说，您可以在诸如 UPDATE、DELETE 或 INSERT 更新底层表的内容.要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系.还有某些其他构造使视图不可更新.更具体地说，如果视图包含以下任何一项，则该视图不可更新:

Some views are updatable. That is, you can use them in statements such as UPDATE, DELETE, or INSERT to update the contents of the underlying table. 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()，等等)

DISTINCT

GROUP BY

HAVING

UNION 或 UNION ALL

选择列表中的子查询

某些连接(请参阅本节后面的其他连接讨论)

Certain joins (see additional join discussion later in this section)

FROM 子句中的不可更新视图

Nonupdatable view in the FROM clause

WHERE 子句中的子查询引用了 FROM 子句中的表

A 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(使用临时表总是使视图不可更新)

Uses ALGORITHM = TEMPTABLE (use of a temporary table always makes a view nonupdatable)

多次引用基表的任何列.

Multiple references to any column of a base table.

[删除]

多表视图有时可能是可更新的，假设它可以用 MERGE 算法进行处理.为此，视图必须使用内连接(不是外连接或 联合).此外，视图定义中只能更新单个表，因此 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, because the implementation uses temporary tables to process them.

这篇关于在 MySQL 中更新视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！