Summary: in this tutorial, you are going to learn about MySQL View. We will explain how MySQL implements views.
MySQL has supported database views since version 5.x. In MySQL, almost features of views conform to the SQL: 2003 standard. MySQL processes queries against the views in two ways:
- In a first way, MySQL creates a temporary table based on the view definition statement and executes the incoming query on this temporary table.
- In a second way, MySQL combines the incoming query with the query defined the view into one query and executes the combined query.
MySQL supports version system for views. Each time when a view is altered or replaced, a copy of the view is back up in
arc (archive) folder that resides in a specific database folder. The name of the backup file is
view_name.frm-00001 . If you then change the view again, MySQL will create a new backup file named
MySQL allows you to create a view based on other views. In the SELECT statement of the view definition, you can refer to another view.
MySQL view’s restrictions
You cannot create an index on a view. MySQL uses indexes of the underlying tables when you query data against the views that use the merge algorithm. For the views that use the temptable algorithm, indexes are not utilized when you query data against the views.
If you drop or rename tables that a view is based on, MySQL does not issue any errors. However, MySQL does invalidate the view. You can use the
CHECK TABLE statement to check whether the view is valid.
MySQL does not support materialized view like other database systems such as Oracle, PostgreSQL , etc.