首页 > 代码库 > 21.5.3 Updatable and Insertable Views

21.5.3 Updatable and Insertable Views

2024-08-11 22:59:49   215人阅读

http://dev.mysql.com/doc/refman/5.7/en/view-updatability.html

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 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:

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

  • DISTINCT        

  • GROUP BY        

  • HAVING        

  • UNION or           UNION ALL        

  •           Subquery in the select list        

              Before MySQL 5.7.11, subqueries in the select list fail for           INSERT, but are okay for           UPDATE,           DELETE. As of MySQL 5.7.11,          that is still true for nondependent subqueries. For dependent          subqueries in the select list, no data change statements are          permitted.        

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

  •           Reference to nonupdatable view in the FROM          clause        

  •           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 (use of a temporary          table always makes a view nonupdatable)        

  •           Multiple references to any column of a base table (fails for           INSERT, okay for           UPDATE,           DELETE)

21.5.3 Updatable and Insertable Views


联系
我们