VIEW in MySQL

VIEW in MySQL – This article is an overview of views that appeared in MySQL version 5.0. It covers the creation, benefits and limitations of views.

What is a VIEW in MySQL?

A view (VIEW) is a database object that results from a query to a database defined using the SELECT operator when accessing a view.

Views are sometimes called “virtual tables”. This name is due to the fact that a view is available to the user as a table, but it itself does not contain any data, but extracts them from tables at the moment of accessing it. If the data is changed in the base table, the user will get the actual data when accessing the view that uses that table; no caching of the selection results from the table is performed when the view is working. Thus, the mechanism of caching of inquiries (query cache) works at level of inquiries of the user without dependence from that, whether the user addresses to tables or representations.

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

• Enables flexible customization of data access rights due to the fact that rights are given to a view rather than to a table. This is very convenient in case the user needs to give the rights to separate rows of the table or the possibility to get not the data itself but the result of some actions on them.
• Allows you to separate the logic of data storage and software. You can change the data structure without affecting the program code, you just need to create views similar to the tables that were previously accessed by applications. It’s very convenient when you can’t change the program code or when several applications with different data structure requirements address the same database.
• Easy to use by automatically performing actions such as accessing a certain part of rows and/or columns, retrieving data from multiple tables and converting them using different functions.

Restrictions on MySQL views

The article contains restrictions for MySQL 5.1 version (their number may decrease later).

• Restrictions on MySQL views;
• The article contains restrictions for MySQL 5.1 version (their number may decrease later);
• You cannot hang the trigger on the view;
• You cannot make a submission based on temporary tables; you cannot make a temporary submission;
• A subquery in the FROM part of the definition of a view cannot be used;
• You cannot use system and user variables in the view definition; you cannot use local variables or procedure parameters within stored procedures in the view definition;
• You cannot use parameters of prepared expressions (PREPARE) in determining the representation;
• The tables and submissions that are present in the definition of a submission must exist;
• Only the views that satisfy a number of requirements allow the UPDATE, DELETE and INSERT type queries.

View creation

To create a view, the CREATE VIEW statement is used with the following syntax:

CREATE [OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

view_name – the name of the view to be created. select_statement – the SELECT operator that selects data from tables and/or other views to be contained in the view

The CREATE VIEW operator contains 4 optional designs:

• OR REPLACE – when using this construct, if a view with this name exists, the old one will be deleted and the new one created. Otherwise, an error will occur informing that a view with this name is merged and no new view will be created. One peculiarity should be noted – the names of tables and views within the same database must be unique, i.e. a view with the name of an already existing table cannot be created. However, the OR
• REPLACE design only affects views and will not replace a table.
• ALGORITM – defines the algorithm used when accessing a view (discussed in more detail below).
  column_list – specifies the names of the view fields.
• WITH CHECK OPTION – when using this construction, all added or changed rows will be checked for compliance with the view definition. In case of inconsistency, this change will not be performed. Note that when specifying this construction for an unrenewable 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 operator in the view definition. If the names of the fields in the view are explicitly specified, the column_list must include one name for each field separated by a comma.

There are two reasons why it is desirable to use explicit representation field names:

1. The names of the view fields must be unique within this view. When creating a multi-table view, it is possible that the view field names may repeat themselves. For example:

CREATE VIEW v AS SELECT a.id, b.id FROM a,b;

To avoid such a situation, you need to explicitly specify the names of the fields of representation

CREATE VIEW v (a_id, b_id) AS SELECT a.id, b.id FROM a,b;

The same result can be achieved by using synonyms (aliases) for speaker names:

CREATE VIEW v AS SELECT a.id a_id, b.id b_id FROM a,b;

2. If in the definition of a view the data obtained are converted using some functions, the name of the field will be this expression, which is not very convenient for further references to this field. For example:

CREATE VIEW v AS SELECT group_concat(DISTINCT column_name oreder BY column_name separator '+') FROM table_name;

It is hardly convenient to use the field name `group_concat(DISTINCT username ORDER BY username separator ‘+’)` in the future.

To view the contents of a view, we use the SELECT operator (completely similar to the case of a simple table), on the other hand, the SELECT operator is in the view definition itself, i.e. we get an embedded construct – a query in a query. In this case, some constructs of the SELECT operator may be present in both operators. There are three possible developments: they will both be executed, one of them will be ignored and the result is not defined. Let us consider these cases in more detail:

• If there is a WHERE condition in both operators, both of these conditions will be met as if they were combined by the AND operator.
• If there is an ORDER BY construct in the view definition, it will work only if the external SELECT operator addressing the view does not have its own sorting condition. If there is an ORDER BY construct in the external operator, the sorting available in the view definition will be ignored.
• If both operators have modifiers affecting the locking mechanism, such as HIGH_PRIORITY, the result of their joint action is not defined. To avoid uncertainty, it is recommended not to use such modifiers in the representation definition.

Performance Algorithms

There are two algorithms used by MySQL when accessing the view: MERGE and TEMPTABLE.

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

In the case of TEMPTABLE algorithm, MySQL writes the contents of the view to the temporary table, over which then executes the operator addressed to the view.
Note: if this algorithm is used, the view cannot be updated (see below).

When creating a view, it is possible to explicitly specify the algorithm used using an optional construct [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}].

UNDEFINED means that MySQL chooses which algorithm to use when accessing a 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 view choose the ratio of views to the number of responses for the topics in the forum:

CREATE VIEW v AS SELECT subject, num_views/num_replies AS param FROM topics WHERE num_replies>0;

For this view, each row corresponds to a single row from the topics table, i.e. the MERGE algorithm can be used. Let us consider the following reference to our view:

SELECT subject, param FROM v WHERE param>1000;

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

SELECT subject, num_views/num_replies AS param FROM topics WHERE num_replies>0 AND num_views/num_replies>1000;

If group functions (count, max, avg, group_concat, etc.), subqueries regarding field enumeration or DISTINCT, GROUP BY constructs are used in the view definition, the 1 to 1 correspondence between the rows of the table and the view based on it is not performed as required by the MERGE algorithm.

Let our view select the number of topics for each forum:

CREATE VIEW v AS SELECT forum_id, count(*) AS num FROM topics GROUP BY forum_id;

We will find the maximum number of threads in the forum:

SELECT MAX(num) FROM v;

If the MERGE algorithm was used, this query would be converted as follows:

SELECT MAX(count(*)) FROM topics GROUP BY forum_id;

Executing this query leads to the error “ERROR 1111 (HY000): Invalid USE of GROUP function”, since the nesting of group functions is used.

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

CREATE TEMPORARY TABLE tmp_table SELECT forum_id, count(*) AS num FROM topics GROUP BY forum_id;
SELECT MAX(num) FROM tmp_table;
DROP TABLE tpm_table;

To sum up, there is no serious reason to explicitly indicate the algorithm when creating a submission, as there is no reason to do so:

• In the case of UNDEFINED MySQL tries to use MERGE wherever possible, as it is more efficient than TEMPTABLE and, unlike it, does not make the view not updateable.
• If you explicitly specify MERGE and the view definition contains constructs that prohibit its use, MySQL will issue a warning and set UNDEFIND.

View MySQL Updatability

A view is called updatable if the UPDATE and DELETE operators can be applied to it to change the data in the tables the view is based on. For a view to be updatable 2 conditions must be met:

• To 1 match between the rows of the view and the tables on which the view is based, i.e. each row of the view must match one row in the source tables.
• The view fields must be a simple listing of table fields, not col1/col2 or col1+2 expressions.

Note: requirements in Russian literature that the updated view be based on a single table and the presence of a physical table among the representation fields of the primary key is not necessary. Rather, a single table requirement is a translation error. The point is that through a multi-table view, only one table per query can be updated, i.e., the UPDATE operator’s SET construction must list the columns of only one table from the view definition.

In addition, for a multi-table view to be updatable, the tables in its definition must be combined only by using INNER JOIN, not OUTER JOIN or UNION.

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

Note: For multi-table views, an INSERT operation only works if you are adding to a single real table. Deleting data (DELETE) for these views is not supported.

When WITH [CASCADED | LOCAL] CHECK OPTION is used in a view definition, all rows that are added or modified will be checked to ensure that they meet the definition of the view.

• Data change (UPDATE) will only occur if a string with new values meets the WHERE condition in the view definition.
• Adding data (INSERT) will only occur if the new string meets the WHERE condition in the view definition.

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

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

• For LOCAL, the WHERE condition is checked only in the own view definition.
• For CASCADED, all views on which this view is based are checked. The default value is CASCADED.

Let us consider an example of an updatable view based on two tables. Let our representation chooses forum themes with number of views more than 2000.

punbb >CREATE OR REPLACE VIEW v AS
-> SELECT forum_name, `subject`, num_views FROM topics,forums f
-> WHERE forum_id=f.id AND num_views>2000 WITH CHECK OPTION;
Query OK, 0 rows affected (0.03 sec)

punbb >SELECT * FROM v WHERE subject='test';
+------------+---------+-----------+
| forum_name | subject | num_views |
+------------+---------+-----------+
| Новости | test | 3000 |
+------------+---------+-----------+
1 row IN SET (0.03 sec)

punbb >UPDATE v SET num_views=2003 WHERE subject='test';
Query OK, 0 rows affected (0.03 sec)
Rows matched: 1 Changed: 0 WARNINGS: 0

punbb >SELECT * FROM v WHERE subject='test';
+------------+---------+-----------+
| forum_name | subject | num_views |
+------------+---------+-----------+
| Новости | test | 2003 |
+------------+---------+-----------+
1 row IN SET (0.01 sec)

punbb >SELECT subject, num_views FROM topics WHERE subject='test';
+---------+-----------+
| subject | num_views |
+---------+-----------+
| test | 2003 |
+---------+-----------+
1 rows IN SET (0.01 sec)

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

punbb >UPDATE v SET num_views=1999 WHERE subject='test';

ERROR 1369 (HY000): CHECK OPTION failed ‘punbb.v’.

Not all updated views allow adding data:

punbb >INSERT INTO v (subject,num_views) VALUES('test1',4000);

ERROR 1369 (HY000): CHECK OPTION failed ‘punbb.v’.

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

punbb >INSERT INTO v (forum_id,subject,num_views) VALUES(1,'test1',4000);

ERROR 1054 (42S22): Unknown COLUMN ‘forum_id’ IN ‘field list’.

On the other hand:

punbb >INSERT INTO v (forum_name) VALUES('TEST');

Query OK, 1 row affected (0.00 sec)

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

Good luck with your views!

Learning MySQL – CREATE VIEW (video)