在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 或

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() ，依此类推)

• Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)

DISTINCT

GROUP BY

HAVING

UNION 或选择列表中的子查询

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

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

不可更新视图

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.

[ deletia ]

假设可以使用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屋！