Views (VIEW) in MySQL

What is the presentation?

View (VIEW) - a database object that is the result of a query to a database, defined using the SELECT statement, at the time of accessing the view.

Views are sometimes called "virtual tables." This name is due to the fact that the view is available to the user as a table, but it itself does not contain data, but retrieves them from the tables at the time of accessing it. If the data is changed in the base table, the user will receive the actual data when accessing the view using this table; caching of the sample results from the table when views are not performed. At the same time, the query cache mechanism works at the user's query level irrespective of whether the user is accessing tables or views.

Views can be based on both tables and other views, i.e. can be nested (up to 32 levels of nesting).

Benefits of using views:

1. Provides the ability to flexibly configure access rights to data due to the fact that rights are given not to a table, but to a view. This is very useful if the user needs to give rights to individual rows of the table or the possibility of obtaining not the data itself, but the result of some actions on them.
2. Allows you to separate the logic of data storage and software. You can change the data structure without affecting the program code; you only need to create views similar to the tables that applications used to access. This is very convenient when there is no possibility to change the program code or several applications access the same database with different data structure requirements.
3. Ease of use by automatically performing actions such as accessing a specific part of rows and / or columns, retrieving data from several tables, and converting them using various functions.

View limitations in MySQL

The article lists the restrictions for the version of MySQL 5.1 (in the future their number may be reduced).

• you can't hang the trigger on the view,
• cannot be made based on temporary tables; you cannot make a temporary presentation;
• in the definition of the view can not use the subquery in the FROM part
• system definitions and user variables cannot be used in the view definition; inside stored procedures you cannot use local variables or procedure parameters in the view definition,
• parameters of prepared expressions (PREPARE) cannot be used in the view definition,
• Tables and views that are present in the view definition must exist.
• only views that satisfy a number of requirements allow UPDATE, DELETE, and INSERT queries.

Creating views

To create a view, use the CREATE VIEW statement, which has the following syntax:

view_name is the name of the view being created. select_statement - a SELECT statement that selects data from tables and / or other views that will be contained in the view

The CREATE VIEW statement contains 4 optional constructs:

1. OR REPLACE - if this construct is used, if a representation with the same name exists, the old one will be deleted, and the new one will be created. Otherwise, an error will occur that informs about the existence of a view with this name and a new view will not be created. One feature should be noted: the names of tables and views within one database must be unique, i.e. You cannot create a view with the name of an existing table. However, the OR REPLACE construction acts only on views and will not replace the table.
2. ALGORITM - defines the algorithm used when accessing the presentation (more on this later).
3. column_list - sets view field names.
4. WITH CHECK OPTION - when using this construction, all added or modified strings will be checked for compliance with the view definition. In case of inconsistencies, this change will not be made. Please note that when specifying this construction for an unpatched view, an error will occur and the view will not be created. (more on this will be discussed below).

By default, view columns have the same names as the fields returned by the SELECT statement in the view definition. When explicitly specifying field names, column_list must include one comma-separated name for each field. There are two reasons for which it is desirable to use the explicit indication of the names of the presentation fields:

1. View field names must be unique within this view. When creating a view based on several tables, it is possible that the view field names are repeated. For example:
   CREATE VIEW v AS SELECT a. Id, b. Id FROM a, b;
   To avoid this situation, you must explicitly specify the names of the fields
   CREATE VIEW v (a_id, b_id) AS SELECT a.id, b.id FROM a, b;
   The same result can be achieved using synonyms (aliases) for column names:
   CREATE VIEW v AS SELECT a.id a_id, b.id b_id FROM a, b;
2. If in the view definition, the received data is converted using some functions, then the field name will be the expression, which is not very convenient for further references to this field. Napimer:
   CREATE VIEW v AS SELECT group_concat (distinct column_name oreder by column_name separator '+') FROM table_name;
   It is hardly convenient to use in the future as the field name `group_concat (distinct username order by username separator '+')`

To view the contents of the view, we use the SELECT statement (completely similarly as in the case of a simple table), on the other hand, the SELECT statement is in the view definition itself, i.e. it turns out a nested construct - a query in the query. At the same time, some constructions of the SELECT operator can be present in both operators. There are three possible scenarios: they will both be executed, one of them will be ignored and the result is undefined. Let us consider these cases in more detail:

1. If the WHERE clause is met in both operators, then both of these conditions will be fulfilled as if they were combined with the AND operator.
2. If there is an ORDER BY clause in the view definition, then it will work only if there is no own sorting condition in the external SELECT statement referring to the view. If there is an ORDER BY clause in an external statement, the sorting present in the view definition will be ignored.
3. If there are modifiers in both operators that affect the locking mechanism, such as HIGH_PRIORITY, the result of their joint action is undefined. To avoid uncertainty, it is recommended not to use such modifiers in the presentation definition.

View algorithms

There are two algorithms that MySQL uses when accessing a view: MERGE and TEMPTABLE.

In the case of the MERGE algorithm, MySQL, when accessing a view, adds to the operator used the corresponding parts from the view definition and executes the resulting operator.

In the case of the TEMPTABLE algorithm, MySQL puts the contents of the view into a temporary table, on which the statement facing the view is then executed.
Please note : in the case of using this algorithm, the presentation cannot be updated (see below).

When creating a view, it is possible to explicitly specify the algorithm used with the optional construct [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
UNDEFINED means that MySQL chooses which algorithm to use when accessing the view. This is the default value if this construct is missing.

Using the MERGE algorithm requires a 1 to 1 match between the rows of the table and the view based on it.

Let our presentation choose the ratio of the number of views to the number of answers for forum topics:

For this presentation, each line corresponds to a single line from the topics table, i.e. MERGE algorithm can be used. Consider the following appeal to our view:

In the case of the MERGE algorithm, MySQL includes the definition of the view in the used SELECT statement: replaces the view name with the table name, replaces the list of fields with the view field definitions, and adds a condition in the WHERE part with the AND operator. The summary statement executed then by MySQL looks like this:

If the view definition uses group functions (count, max, avg, group_concat, etc.), subqueries in the field enumeration part or the DISTINCT, GROUP BY clause, then the 1 to 1 correspondence between the rows of the table and the table based on her submission.

Let our presentation choose the number of topics for each forum:

Find the maximum number of topics in the forum:

In this case, MySQL uses the TEMPTABLE algorithm, i.e. enters the contents of the view into a temporary table (this process is sometimes called "materialization of the view"), and then calculates MAX () using the data of the temporary table:

Summing up, it should be noted that there are no serious reasons to explicitly specify an algorithm when creating a view, since:

1. In the case of UNDEFINED, MySQL tries to use MERGE wherever possible, since it is more efficient than TEMPTABLE and, unlike it, does not make the view not updatable.
2. If you explicitly specify MERGE, and the view definition contains constructions that prohibit its use, then MySQL will issue a warning and set the value to UNDEFIND.

Viewable updatability

A view is called updatable if UPDATE and DELETE statements can be applied to it to change the data in the tables on which the view is based. In order for the view to be updated, 2 conditions must be met:

1. The 1 to 1 correspondence between the rows of the view and the tables on which the view is based, i.e. each row of the view should correspond to one row in the source tables.
2. The fields of the view should be a simple enumeration of the table fields, and not col1 / col2 or col1 + 2 expressions.

Please note : the requirements in the Russian-language literature that the updated representation is based on a single table and the presence of a physical table in the number of fields of the primary key representation are not necessary. Most likely the requirement of a single table is a translation error. The fact is that through a view based on several tables, only one table can be updated per request, i.e. The SET clause of an UPDATE statement must enumerate the columns of only one table from the view definition. In addition, in order for a view based on several tables to be updatable, the tables in its definition must be merged only with the help of INNER JOIN, and not OUTER JOIN or UNION.

The updated view may allow the addition of data (INSERT) if all fields of the source table that are not present in the view have default values.

Note : for views based on several tables, the operation of adding data (INSERT) works only if it is being added to a single real table. Data deletion (DELETE) for such views is not supported.

When used in the definition of the representation of the construction WITH [CASCADED | LOCAL] CHECK OPTION All rows added or modified will be checked for compliance with the view definition.

• The data change (UPDATE) will occur only if the row with the new values ​​satisfies the WHERE condition in the view definition.
• Adding data (INSERT) will occur only if the new line satisfies the WHERE condition in the view definition.

In other words, you cannot add or change data in a view so that it is not accessible through the view.

The CASCADED and LOCAL keywords define the depth of validation for views based on other views:

• For LOCAL, the WHERE clause is checked only in its own view definition.
• For CASCADED, a check is performed for all views on which the given view is based. The default value is CASCADED.

Consider an example of an updated view based on two tables. Let our presentation choose forum topics with more than 2000 views.

However, if we try to set the value of num_views to less than 2000, the new value will not satisfy the condition WHERE num_views> 2000 in the definition of the view and the update will not occur.

Not all updated views allow data to be added:

The reason is that the default value of the forum_id column is 0, therefore the row being added does not satisfy the WHERE condition forum_id = f.id in the view definition. We cannot explicitly specify the value of forum_id, since there is no such field in the view definition:

On the other hand:

Thus, our view, based on two tables, allows us to update both tables and add data to only one of them.