MySQL Advanced features---view

View:
The view itself is a virtual table and does not hold any data.
When accessing a view using an SQL statement, the data it returns is generated from other tables by MySQL.
Views and tables in the same namespace, MySQL is treated similarly in many places below the view and table.
However, views and tables are different, for example, you cannot create a trigger on a view, or you can delete a view by using the drop TABLE command

There are two implementations of the algorithm:
merge: Merge algorithm, use this

temptable: Temporal table algorithm as much as possible. 　　If the view is in Alpine group BU, DISTINCT, any aggregate function, UNION, subquery, etc.


explain select * from <view_name>
Span style= "font-family: ' Microsoft Yahei ';" > ID select_type

2 derived

To update the view:
Refers to updating the view to update the related tables involved in the view.
If the view definition contains the Group by,union aggregate function, as well as some other special cases, it cannot be updated.
A query that updates a view can also be a closed statement, but with one restriction, the column being updated must be from the same table.
In addition, all views implemented using the Temporal table algorithm cannot be updated.

You can use a view to implement column-based permission control without the need to really create column permissions on the system, because there is no additional overhead.

A view implemented using the Temporal table algorithm, at some point, will be poorly performing.
MySQL executes such views recursively, executing the outer query first, even if the outer query optimizer optimizes it well, but MySQL
Optimizations may not be able to do more internal and external optimizations like other databases.
The Where condition of the outer query cannot be pushed down into the query for the staging table of the build view, nor can the staging table be indexed.

Limitations of MySQL View:
MySQL does not support materialized views (materialized view refers to storing view result data in a table that can be viewed and periodically refreshing data from the original table to the table)
The MySQL view does not save the original SQL statement for the view definition. Show create view is rendered in an unfriendly internal format.

MySQL Advanced features---view