mysql - 在 MySQL 中更新 View

Question

我正在创建一个 View 来向用户显示他/她的数据，但我也希望用户能够在这些 View 中的某些字段中进行更改。 View 中所做的更改是否也反射(reflect)在基表中？

另外，我能否更新由多个基表组成的 View ？

Answer 1

根据 Updatable and Insertable Views 记录:

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:

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

• DISTINCT

• GROUP BY

• HAVING

• UNION or UNION ALL

• Subquery in the select list

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

• Nonupdatable view in the FROM clause

• 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)

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

• Multiple references to any column of a base table.

[ deletia ]

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.