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+. In MySQL, almost features of views conform to the SQL: 2003 standard. MySQL processes query 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 versioning system for views. Each time when a view is altered or replaced, a copy of the view is back up in
arc (archive) directory 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 other words, you can refer to other views in the
SELECT statement which defines the 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.
You cannot use subqueries in the
FROM clause of the
SELECT statement that defines the view before MySQL 5.7.7
If you drop or rename tables to which a view references, MySQL does not issue any error. However, MySQL does invalidate the view. You can use the
CHECK TABLE statement to check whether the view is valid or not.