14.2. Creating Views

To define a view, use the CREATE VIEW statement, which has this syntax:

 CREATE [OR REPLACE] [ALGORITHM = algorithm_type]     VIEW view_name [(column_list)]     AS select_statement     [WITH [CASCADED | LOCAL] CHECK OPTION] 

view_name is the name to give the view. It can be unqualified to create the view in the default database, or qualified as db_name.view_name to create it in a specific database.

select_statement is a SELECT statement that indicates how to retrieve data when the view is used. The statement can select from base tables or other views. References in the SELECT statement to unqualified table or view names refer to objects in your default database. To select from a table or view in a specific database, refer to it using the db_name.table_name or db_name.view_name syntax.

Several parts of the CREATE VIEW statement are optional:

• The OR REPLACE clause causes any existing view with same name as the new one to be dropped prior to creation of the new view.

• The ALGORITHM clause specifies the processing algorithm to use when the view is invoked.

• column_list provides names for the view columns to override the default names.

• When the WITH CHECK OPTION clause is included in a view definition, all data changes made to the view are checked to ensure that the new or updated rows satisfy the view-defining condition. If the condition is not satisfied, the change is not accepted, either in the view or in the underlying base table.

This section discusses most parts of the CREATE VIEW statement. For information about ALGORITHM and WITH CHECK OPTION, see Section 14.2.2, "View Algorithms," and Section 14.2.3, "Updatable Views," respectively.

The following CREATE VIEW statement defines a simple view named CityView that selects the ID and Name columns from the City table. The SELECT statement shows an example of how to retrieve from the view:

 mysql> CREATE VIEW CityView AS SELECT ID, Name FROM City; Query OK, 0 rows affected (0.03 sec) mysql> SELECT * FROM CityView; +------+-----------------------------------+ | ID   | Name                              | +------+-----------------------------------+ |    1 | Kabul                             | |    2 | Qandahar                          | |    3 | Herat                             | |    4 | Mazar-e-Sharif                    | ... 

Views and base tables share the same namespace, so CREATE VIEW results in an error if a base table or view with the given name already exists. To create the view if it does not exist, or replace a view of the same name if it does exist, use the OR REPLACE clause:

 mysql> CREATE VIEW CityView AS SELECT ID, Name FROM City; ERROR 1050 (42S01): Table 'CityView' already exists mysql> CREATE OR REPLACE VIEW CityView AS SELECT ID, Name FROM City; Query OK, 0 rows affected (0.00 sec) 

The OR REPLACE clause works only if the existing object is a view. You cannot use it to replace a base table.

Another way to replace a view is by dropping it with DROP VIEW and re-creating it with CREATE VIEW.

By default, the names of the columns in a view are the same as the names of the columns retrieved by the SELECT statement in the view definition. For the CityView view just defined, the SELECT statement retrieves two columns named ID and Name, which also become the view column names. To select data from CityView, select either or both columns by name, or use * to select all view columns:

 SELECT ID FROM CityView ... ; SELECT Name FROM CityView ... ; SELECT ID, Name FROM CityView ... ; SELECT * FROM CityView ... ; 

To override the default view column names and provide explicit names, include a column_list clause following the view name in the CREATE VIEW statement. If present, this list must contain one name per column selected by the view, with multiple names separated by commas. There are some important reasons to name view columns explicitly:

• View column names must be unique within the view. If the columns selected by a view do not satisfy this condition, a list of unique explicit column names resolves name clashes. For example, an attempt to define a view that selects columns with the same name from joined tables fails unless you rename at least one of the columns:

   mysql> CREATE VIEW v     -> AS SELECT Country.Name, City.Name     -> FROM Country, City WHERE Code = CountryCode; ERROR 1060 (42S21): Duplicate column name 'Name' mysql> CREATE VIEW v (CountryName, CityName)     -> AS SELECT Country.Name, City.Name     -> FROM Country, City WHERE Code = CountryCode; Query OK, 0 rows affected (0.04 sec) 

  
  

• Explicit view column names make it easier to use columns that are calculated from expressions. (By default, the name for such a column is the expression, which makes it difficult to reference.) The following example creates a view for which the second column is created from an aggregate expression:

   mysql> CREATE VIEW CountryLangCount AS     -> SELECT Name, COUNT(Language)     -> FROM Country, CountryLanguage WHERE Code = CountryCode     -> GROUP BY Name; Query OK, 0 rows affected (0.01 sec) mysql> DESCRIBE CountryLangCount; +-----------------+------------+------+-----+---------+-------+ | Field           | Type       | Null | Key | Default | Extra | +-----------------+------------+------+-----+---------+-------+ | Name            | char(52)   | NO   |     |         |       | | COUNT(Language) | bigint(20) | NO   |     | 0       |       | +-----------------+------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) 

  
  

  The name of the second column is COUNT(Language), which must be referred to using a quoted identifier (that is, as `COUNT(Language)`). To avoid this, provide names for the columns by including a column list in the view definition:

   mysql> CREATE VIEW CountryLangCount (Name, LangCount) AS     -> SELECT Name, COUNT(Language)     -> FROM Country, CountryLanguage WHERE Code = CountryCode     -> GROUP BY Name; Query OK, 0 rows affected (0.00 sec) mysql> DESCRIBE CountryLangCount; +-----------+------------+------+-----+---------+-------+ | Field     | Type       | Null | Key | Default | Extra | +-----------+------------+------+-----+---------+-------+ | Name      | char(52)   | NO   |     |         |       | | LangCount | bigint(20) | NO   |     | 0       |       | +-----------+------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) 

  
  

Another way to provide names for view columns is by using column aliases in the SELECT statement. This technique is convenient if only some of the view columns need renaming. For example, CountryLangCount could have been created with a definition that uses an AS clause to provide an alias for the second view column:

 CREATE VIEW CountryLangCount AS SELECT Name, COUNT(Language) AS LangCount FROM Country, CountryLanguage WHERE Code = CountryCode GROUP BY Name;  

When you use a SELECT statement that refers to a view, it is possible that the statement will contain clauses that are also present in the view definition. Sometimes the corresponding clauses are both used, sometimes one overrides the other, and sometimes the effect is undefined:

• A view definition can include a WHERE clause. If a statement that refers to the view includes its own WHERE clause, the conditions in both clauses are used. In effect, the conditions are combined with an AND operator.

• If a view definition includes an ORDER BY clause, it is used for sorting view results unless a statement that refers to a view includes its own ORDER BY clause. In that case, the view definition ORDER BY is ignored.

• For some options, such as HIGH_PRIORITY, the effect is undefined if they appear both in the statement that refers to the view and in the view definition. You can avoid ambiguity in such cases by omitting the option from the view definition and specifying it as necessary only when selecting from the view.

14.2.1. Restrictions on Views

A view definition can include most of the constructs that are allowable in SELECT statements, such as WHERE, GROUP BY, and so forth. However, views in MySQL have some restrictions that do not apply to base tables:

• You cannot create a TEMPORARY view.

• You cannot associate a trigger with a view.

• The tables on which a view is to be based must already exist.

• The SELECT statement in a view definition cannot contain any of these constructs:

  • Subqueries in the FROM clause

  • References to TEMPORARY tables

  • References to user variables

  • References to procedure parameters, if the view definition occurs within a stored routine

  • References to prepared statement parameters

14.2.2. View Algorithms

A MySQL-specific extension to the CREATE VIEW statement is the ALGORITHM clause, which specifies the algorithm used to process the view. It has this syntax:

 ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE} 

For UNDEFINED, MySQL chooses the algorithm itself. This is the default if no ALGORITHM clause is present.

For MERGE, MySQL processes a statement that refers to the view by merging parts of the view definition into corresponding parts of the statement and executing the resulting merged statement.

For TEMPTABLE, MySQL processes a statement that refers to the view by first retrieving the view contents into an intermediate temporary table, and then using the temporary table to finish executing the statement. If you specify TEMPTABLE, the view becomes non-updatable. That is, the view cannot be used to update the underlying table. (Modifications would be made to the temporary table instead, leaving the base table unchanged.)

The MERGE algorithm requires a one-to-one relationship between the rows in a view and the rows in the base table. Suppose that a view v is defined as follows to display changes in GNP per country:

 CREATE ALGORITHM = UNDEFINED VIEW v AS SELECT Name, GNP - GNPOld AS GNPDiff FROM Country WHERE GNPOld IS NOT NULL; 

As specified in the CREATE VIEW statement, the algorithm is undefined, so MySQL picks the algorithm. For this view, each row is derived from a single Country table row, so the view can be processed with the MERGE algorithm. Consider the following statement that refers to the view:

 SELECT Name, GNPDiff FROM v WHERE GNPDiff > 0; 

The MERGE algorithm merges the view definition into the SELECT statement: MySQL replaces v with Country, replaces the column list by the corresponding view column definitions, and adds the view WHERE conditions to the statement with AND. The resulting transformed SELECT statement that MySQL executes looks something like this:

 SELECT Name, GNP - GNPOld AS GNPDiff FROM Country WHERE (GNP - GNPOld > 0) AND (GNPOld IS NOT NULL); 

The MERGE requirement for a one-to-one relationship is not satisfied if the view definition produces a view row from multiple base table rows, or uses constructs such as DISTINCT, aggregate functions, a subquery in the select list, GROUP BY or HAVING. Suppose that a view v is defined as follows to display the number of languages spoken per country:

 CREATE VIEW v AS   SELECT CountryCode, COUNT(*) AS LangCount   FROM CountryLanguage GROUP BY CountryCode; 

Each row in the view is not necessarily derived from a single CountryLanguage table row, so the MERGE algorithm cannot be used to process the view. For example, to find the largest number of languages spoken in a single country, we would select from the view like this:

 SELECT MAX(LangCount) FROM v; 

But the MERGE algorithm would treat that statement as equivalent to this one, which is illegal due to the nested aggregate functions:

 SELECT MAX(COUNT(*))   FROM CountryLanguage GROUP BY CountryCode; 

Instead, MySQL processes a view such as this by using a temporary table to produce a result that contains the aggregated counts. This is called "materializing" the view (into the temporary table). Then MySQL evaluates SELECT MAX() using the temporary table to get the maximum count. It is as though MySQL does something like this:

 CREATE TEMPORARY TABLE tmp_table   SELECT CountryCode, COUNT(*) AS LangCount   FROM CountryLanguage GROUP BY CountryCode; SELECT MAX(LangCount) FROM tmp_table; DROP TABLE tmp_table; 

When you create a view, should you specify an algorithm explicitly? Generally speaking, it's unnecessary:

• If you specify no algorithm, MySQL picks the algorithm automatically. It uses MERGE if possible because that usually is more efficient than TEMPTABLE and also does not prevent the view from being updatable the way that TEMPTABLE does. MySQL uses a temporary table if MERGE cannot be used.

• If you specify MERGE but the view definition contains any construct that prevents MERGE from being used, MySQL issues a warning and resets the algorithm to UNDEFINED.

For these reasons, there usually is little reason to specify either UNDEFINED or MERGE. On the other hand, you might want to specify TEMPTABLE to influence how MySQL uses locking while it processes the view. Locks used for any underlying tables can be released after the temporary table has been created. This might reduce contention by allowing other clients to access the underlying tables earlier while the temporary table is used to finish processing the view.

14.2.3. Updatable Views

A view is updatable if it can be used with statements such as UPDATE or DELETE to modify the underlying base table. Not all views are updatable. For example, you might be able to update a table, but you cannot update a view on the table if the view is defined in terms of aggregate values calculated from the table. The reason for this is that each view row need not correspond to a unique base table row, in which case MySQL would not be able to determine which table row to update.

The primary conditions for updatability are that there must be a one-to-one relationship between the rows in the view and the rows in the base table, and that the view columns to be updated must be defined as simple table references, not expressions. (There are other conditions as well, but we will not go into them here.)

The following example demonstrates updatability. First, use the following statement to create a CountryPop table containing three columns from the Country table. (By modifying this table, we avoid changing the contents of the original world database tables.)

 mysql> CREATE TABLE CountryPop     -> SELECT Name, Population, Continent FROM Country; Query OK, 239 rows affected (0.01 sec) Records: 239  Duplicates: 0  Warnings: 0 

Then create a simple view that contains the rows in CountryPop for countries in Europe:

 mysql> CREATE VIEW EuropePop AS     -> SELECT Name, Population FROM CountryPop     -> WHERE Continent = 'Europe'; Query OK, 0 rows affected (0.02 sec) 

The EuropePop view satisfies the one-to-one requirement, and its columns are simple column references, not expressions such as col1+1 or col2/col3. EuropePop is updatable, as demonstrated by the following statements. The example also selects from the base table CountryPop to show that the base table is indeed modified by the UPDATE and DELETE statements that use the view.

 mysql> SELECT * FROM EuropePop WHERE Name = 'San Marino'; +------------+------------+ | Name       | Population | +------------+------------+ | San Marino |      27000 | +------------+------------+ 1 row in set (0.02 sec) mysql> UPDATE EuropePop SET Population = Population + 1     -> WHERE Name = 'San Marino'; Query OK, 1 row affected (0.01 sec) Rows matched: 1  Changed: 1  Warnings: 0 mysql> SELECT * FROM EuropePop WHERE Name = 'San Marino'; +------------+------------+ | Name       | Population | +------------+------------+ | San Marino |      27001 | +------------+------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM CountryPop WHERE Name = 'San Marino'; +------------+------------+-----------+ | Name       | Population | Continent | +------------+------------+-----------+ | San Marino |      27001 | Europe    | +------------+------------+-----------+ 1 row in set (0.00 sec) mysql> DELETE FROM EuropePop WHERE Name = 'San Marino'; Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM EuropePop WHERE Name = 'San Marino'; Empty set (0.00 sec) mysql> SELECT * FROM CountryPop WHERE Name = 'San Marino'; Empty set (0.01 sec) 

An updatable view might also be insertable (usable with INSERT) if the view columns consist only of simple table column references (not expressions) and if any columns present in the base table but not named in the view or the INSERT have default values. In this case, an INSERT into the view creates a new base table row with each column not named in the INSERT set to its default value.

If a view is updatable, you can use the WITH CHECK OPTION clause to place a constraint on allowable modifications. This clause causes the conditions in the WHERE clause of the view definition to be checked when updates are attempted:

• An UPDATE to an existing row is allowed only if the WHERE clause remains true for the resulting row.

• An INSERT is allowed only if the WHERE clause is true for the new row.

In other words, WITH CHECK OPTION ensures that you cannot update a row in such a way that the view no longer selects it, and that you cannot insert a row that the view will not select.

Using the CountryPop table created earlier in this section, define the following view that selects countries with a population of at least 100 million:

 mysql> CREATE VIEW LargePop AS     -> SELECT Name, Population FROM CountryPop     -> WHERE Population >= 100000000     -> WITH CHECK OPTION; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM LargePop; +--------------------+------------+ | Name               | Population | +--------------------+------------+ | Bangladesh         |  129155000 | | Brazil             |  170115000 | | Indonesia          |  212107000 | | India              | 1013662000 | | Japan              |  126714000 | | China              | 1277558000 | | Nigeria            |  111506000 | | Pakistan           |  156483000 | | Russian Federation |  146934000 | | United States      |  278357000 | +--------------------+------------+ 10 rows in set (0.00 sec) 

The WITH CHECK OPTION clause in the view definition allows some modifications but disallows others. For example, it's possible to increase the population of any country in the view:

 mysql> UPDATE LargePop SET Population = Population + 1     -> WHERE Name = 'Nigeria'; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0 mysql> SELECT * FROM LargePop WHERE Name = 'Nigeria'; +---------+------------+ | Name    | Population | +---------+------------+ | Nigeria |  111506001 | +---------+------------+ 1 row in set (0.01 sec) 

It is also possible to decrease a population value, but only if it does not drop below the minimum value of 100 million that is required by the view's WHERE clause:

 mysql> UPDATE LargePop SET Population = 99999999     -> WHERE Name = 'Nigeria'; ERROR 1369 (HY000): CHECK OPTION failed 'world.LargePop' 

For inserts, a row can be added unless the Population value is less than 100 million:

 mysql> INSERT INTO LargePop VALUES('some country',100000000); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO LargePop VALUES('some country2',99999999); ERROR 1369 (HY000): CHECK OPTION failed 'world.LargePop' 

The WITH CHECK OPTION clause takes an optional keyword that controls the extent to which MySQL performs WHERE-checking for updates when a view is defined in terms of other views:

• For WITH LOCAL CHECK OPTION, the check applies only to the view's own WHERE clause.

• For WITH CASCADED CHECK OPTION, the view's WHERE clause is checked, as is the WHERE clause for any underlying views. CASCADED is the default if neither CASCADED nor LOCAL is given.

WITH CHECK OPTION is allowed only for updatable views, and an error occurs if you use it for a non-updatable view. This means that ALGORITHM = TEMPTABLE and WITH CHECK OPTION are mutually exclusive, because TEMPTABLE makes a view non-updatable.