(mysql.info) create-view
Info Catalog
(mysql.info) alter-view
(mysql.info) views
(mysql.info) drop-view
19.2 `CREATE VIEW' Syntax
=========================
CREATE
[OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = { USER | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW VIEW_NAME [(COLUMN_LIST)]
AS SELECT_STATEMENT
[WITH [CASCADED | LOCAL] CHECK OPTION]
This statement creates a new view, or replaces an existing one if the
`OR REPLACE' clause is given. The SELECT_STATEMENT is a `SELECT'
statement that provides the definition of the view. The statement can
select from base tables or other views.
This statement requires the `CREATE VIEW' privilege for the view, and
some privilege for each column selected by the `SELECT' statement. For
columns used elsewhere in the `SELECT' statement you must have the
`SELECT' privilege. If the `OR REPLACE' clause is present, you must
also have the `DROP' privilege for the view.
A view belongs to a database. By default, a new view is created in the
default database. To create the view explicitly in a given database,
specify the name as DB_NAME.VIEW_NAME when you create it.
mysql> CREATE VIEW test.v AS SELECT * FROM t;
Base tables and views share the same namespace within a database, so a
database cannot contain a base table and a view that have the same name.
Views must have unique column names with no duplicates, just like base
tables. By default, the names of the columns retrieved by the `SELECT'
statement are used for the view column names. To define explicit names
for the view columns, the optional COLUMN_LIST clause can be given as a
list of comma-separated identifiers. The number of names in COLUMN_LIST
must be the same as the number of columns retrieved by the `SELECT'
statement.
Columns retrieved by the `SELECT' statement can be simple references to
table columns. They can also be expressions that use functions,
constant values, operators, and so forth.
Unqualified table or view names in the `SELECT' statement are
interpreted with respect to the default database. A view can refer to
tables or views in other databases by qualifying the table or view name
with the proper database name.
A view can be created from many kinds of `SELECT' statements. It can
refer to base tables or other views. It can use joins, `UNION', and
subqueries. The `SELECT' need not even refer to any tables. The
following example defines a view that selects two columns from another
table, as well as an expression calculated from those columns:
mysql> CREATE TABLE t (qty INT, price INT);
mysql> INSERT INTO t VALUES(3, 50);
mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
mysql> SELECT * FROM v;
+------+-------+-------+
| qty | price | value |
+------+-------+-------+
| 3 | 50 | 150 |
+------+-------+-------+
A view definition is subject to the following restrictions:
* The `SELECT' statement cannot contain a subquery in the `FROM'
clause.
* The `SELECT' statement cannot refer to system or user variables.
* The `SELECT' statement cannot refer to prepared statement
parameters.
* Within a stored routine, the definition cannot refer to routine
parameters or local variables.
* Any table or view referred to in the definition must exist.
However, after a view has been created, it is possible to drop a
table or view that the definition refers to. To check a view
definition for problems of this kind, use the `CHECK TABLE'
statement.
* The definition cannot refer to a `TEMPORARY' table, and you cannot
create a `TEMPORARY' view.
* The tables named in the view definition must already exist.
* You cannot associate a trigger with a view.
`ORDER BY' is allowed in a view definition, but it is ignored if you
select from a view using a statement that has its own `ORDER BY'.
For other options or clauses in the definition, they are added to the
options or clauses of the statement that references the view, but the
effect is undefined. For example, if a view definition includes a
`LIMIT' clause, and you select from the view using a statement that has
its own `LIMIT' clause, it is undefined which limit applies. This same
principle applies to options such as `ALL', `DISTINCT', or
`SQL_SMALL_RESULT' that follow the `SELECT' keyword, and to clauses
such as `INTO', `FOR UPDATE', `LOCK IN SHARE MODE', and `PROCEDURE'.
If you create a view and then change the query processing environment
by changing system variables, that may affect the results that you get
from the view:
mysql> CREATE VIEW v AS SELECT CHARSET(CHAR(65)), COLLATION(CHAR(65));
Query OK, 0 rows affected (0.00 sec)
mysql> SET NAMES 'latin1';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM v;
+-------------------+---------------------+
| CHARSET(CHAR(65)) | COLLATION(CHAR(65)) |
+-------------------+---------------------+
| latin1 | latin1_swedish_ci |
+-------------------+---------------------+
1 row in set (0.00 sec)
mysql> SET NAMES 'utf8';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM v;
+-------------------+---------------------+
| CHARSET(CHAR(65)) | COLLATION(CHAR(65)) |
+-------------------+---------------------+
| utf8 | utf8_general_ci |
+-------------------+---------------------+
1 row in set (0.00 sec)
The `DEFINER' and `SQL SECURITY' clauses specify the security context
to be used when checking access privileges at view invocation time.
They were addded in MySQL 5.0.13, but have no effect until MySQL 5.0.16.
`CURRENT_USER' also can be given as `CURRENT_USER()'.
Within a stored routine that is defined with the `SQL SECURITY DEFINER'
characteristic, `CURRENT_USER' returns the routine creator. This also
affects a view defined within such a routine, if the view definition
contains a `DEFINER' value of `CURRENT_USER'.
The default `DEFINER' value is the user who executes the `CREATE VIEW'
statement. (This is the same as `DEFINER = CURRENT_USER'.) If a USER
value is given, it should be a MySQL account in
`'USER_NAME'@'HOST_NAME'' format (the same format used in the `GRANT'
statement). The USER_NAME and HOST_NAME values both are required.
If you specify the `DEFINER' clause, you cannot set the value to any
user but your own unless you have the `SUPER' privilege. These rules
determine the legal `DEFINER' user values:
* If you do not have the `SUPER' privilege, the only legal USER
value is your own account, either specified literally or by using
`CURRENT_USER'. You cannot set the definer to some other account.
* If you have the `SUPER' privilege, you can specify any
syntactically legal account name. If the account does not actually
exist, a warning is generated.
The `SQL SECURITY' characteristic determines which MySQL account to use
when checking access privileges for the view when the view is executed.
The legal characteristic values are `DEFINER' and `INVOKER'. These
indicate that the view must be executable by the user who defined it or
invoked it, respectively. The default `SQL SECURITY' value is `DEFINER'.
As of MySQL 5.0.16 (when the `DEFINER' and `SQL SECURITY' clauses were
implemented), view privileges are checked like this:
* At view definition time, the view creator must have the privileges
needed to use the top-level objects accessed by the view. For
example, if the view definition refers to a stored function, only
the privileges needed to invoke the function can be checked. The
privileges required when the function runs can be checked only as
it executes: For different invocations of the function, different
execution paths within the function might be taken.
* At view execution time, privileges for objects accessed by the
view are checked against the privileges held by the view creator
or invoker, depending on whether the `SQL SECURITY' characteristic
is `DEFINER' or `INVOKER', respectively.
* If view execution causes execution of a stored function, privilege
checking for statements executed within the function depend on
whether the function is defined with a `SQL SECURITY'
characteristic of `DEFINER' or `INVOKER'. If the security
characteristic is `DEFINER', the function runs with the privileges
of its creator. If the characteristic is `INVOKER', the function
runs with the privileges determined by the view's `SQL SECURITY'
characteristic.
Prior to MySQL 5.0.16 (before the `DEFINER' and `SQL SECURITY' clauses
were implemented), privileges required for objects used in a view are
checked at view creation time.
Example: A view might depend on a stored function, and that function
might invoke other stored routines. For example, the following view
invokes a stored function `f()':
CREATE VIEW v AS SELECT * FROM t WHERE t.id = f(t.name);
Suppose that `f()' contains a statement such as this:
IF name IS NULL then
CALL p1();
ELSE
CALL p2();
END IF;
The privileges required for executing statements within `f()' need to
be checked when `f()' executes. This might mean that privileges are
needed for `p1()' or `p2()', depending on the execution path within
`f()'. Those privileges need to be checked at runtime, and the user who
must possess the privileges is determined by the `SQL SECURITY' values
of the function `f()' and the view `v'.
The `DEFINER' and `SQL SECURITY' clauses for views are extensions to
standard SQL. In standard SQL, views are handled using the rules for
`SQL SECURITY INVOKER'.
If you invoke a view that was created before MySQL 5.0.13, it is
treated as though it was created with a `SQL SECURITY INVOKER' clause
and with a `DEFINER' value that is the same as your account. However,
because the actual definer is unknown, MySQL issues a warning. To make
the warning go away, it is sufficient to re-create the view so that the
view definition includes a `DEFINER' clause.
The optional `ALGORITHM' clause is a MySQL extension to standard SQL.
`ALGORITHM' takes three values: `MERGE', `TEMPTABLE', or `UNDEFINED'.
The default algorithm is `UNDEFINED' if no `ALGORITHM' clause is
present. The algorithm affects how MySQL processes the view.
For `MERGE', the text of a statement that refers to the view and the
view definition are merged such that parts of the view definition
replace corresponding parts of the statement.
For `TEMPTABLE', the results from the view are retrieved into a
temporary table, which then is used to execute the statement.
For `UNDEFINED', MySQL chooses which algorithm to use. It prefers
`MERGE' over `TEMPTABLE' if possible, because `MERGE' is usually more
efficient and because a view cannot be updatable if a temporary table
is used.
A reason to choose `TEMPTABLE' explicitly is that locks can be released
on underlying tables after the temporary table has been created and
before it is used to finish processing the statement. This might result
in quicker lock release than the `MERGE' algorithm so that other
clients that use the view are not blocked as long.
A view algorithm can be `UNDEFINED' for three reasons:
* No `ALGORITHM' clause is present in the `CREATE VIEW' statement.
* The `CREATE VIEW' statement has an explicit `ALGORITHM =
UNDEFINED' clause.
* `ALGORITHM = MERGE' is specified for a view that can be processed
only with a temporary table. In this case, MySQL generates a
warning and sets the algorithm to `UNDEFINED'.
As mentioned earlier, `MERGE' is handled by merging corresponding parts
of a view definition into the statement that refers to the view. The
following examples briefly illustrate how the `MERGE' algorithm works.
The examples assume that there is a view `v_merge' that has this
definition:
CREATE ALGORITHM = MERGE VIEW v_merge (vc1, vc2) AS
SELECT c1, c2 FROM t WHERE c3 > 100;
Example 1: Suppose that we issue this statement:
SELECT * FROM v_merge;
MySQL handles the statement as follows:
* `v_merge' becomes `t'
* `*' becomes `vc1, vc2', which corresponds to `c1, c2'
* The view `WHERE' clause is added
The resulting statement to be executed becomes:
SELECT c1, c2 FROM t WHERE c3 > 100;
Example 2: Suppose that we issue this statement:
SELECT * FROM v_merge WHERE vc1 < 100;
This statement is handled similarly to the previous one, except that
`vc1 < 100' becomes `c1 < 100' and the view `WHERE' clause is added to
the statement `WHERE' clause using an `AND' connective (and parentheses
are added to make sure the parts of the clause are executed with correct
precedence). The resulting statement to be executed becomes:
SELECT c1, c2 FROM t WHERE (c3 > 100) AND (c1 < 100);
Effectively, the statement to be executed has a `WHERE' clause of this
form:
WHERE (select WHERE) AND (view WHERE)
The `MERGE' algorithm requires a one-to relationship between the rows
in the view and the rows in the underlying table. If this relationship
does not hold, a temporary table must be used instead. Lack of a
one-to-one relationship occurs if the view contains any of a number of
constructs:
* Aggregate functions (`SUM()', `MIN()', `MAX()', `COUNT()', and so
forth)
* `DISTINCT'
* `GROUP BY'
* `HAVING'
* `UNION' or `UNION ALL'
* Refers only to literal values (in this case, there is no
underlying table)
Some views are updatable. That is, you can use them in statements such
as `UPDATE', `DELETE', or `INSERT' to update the contents of the
underlying table. For a view to be updatable, there must be a one-to
relationship between the rows in the view and the rows in the
underlying table. There are also certain other constructs that make a
view non-updatable. To be more specific, a view is not updatable if it
contains any of the following:
* Aggregate functions (`SUM()', `MIN()', `MAX()', `COUNT()', and so
forth)
* `DISTINCT'
* `GROUP BY'
* `HAVING'
* `UNION' or `UNION ALL'
* Subquery in the select list
* Join
* Non-updatable view in the `FROM' clause
* A subquery in the `WHERE' clause that refers to a table in the
`FROM' clause
* Refers only to literal values (in this case, there is no
underlying table to update)
* `ALGORITHM = TEMPTABLE' (use of a temporary table always makes a
view non-updatable)
With respect to insertability (being updatable with `INSERT'
statements), an updatable view is insertable if it also satisfies these
additional requirements for the view columns:
* There must be no duplicate view column names.
* The view must contain all columns in the base table that do not
have a default value.
* The view columns must be simple column references and not derived
columns. A derived column is one that is not a simple column
reference but is derived from an expression. These are examples of
derived columns:
3.14159
col1 + 3
UPPER(col2)
col3 / col4
(SUBQUERY)
A view that has a mix of simple column references and derived columns
is not insertable, but it can be updatable if you update only those
columns that are not derived. Consider this view:
CREATE VIEW v AS SELECT col1, 1 AS col2 FROM t;
This view is not insertable because `col2' is derived from an
expression. But it is updatable if the update does not try to update
`col2'. This update is allowable:
UPDATE v SET col1 = 0;
This update is not allowable because it attempts to update a derived
column:
UPDATE v SET col2 = 0;
It is sometimes possible for a multiple-table view to be updatable,
assuming that it can be processed with the `MERGE' algorithm. For this
to work, the view must use an inner join (not an outer join or a
`UNION'). Also, only a single table in the view definition can be
updated, so the `SET' clause must name only columns from one of the
tables in the view. Views that use `UNION ALL' are disallowed even
though they might be theoretically updatable, because the implementation
uses temporary tables to process them.
For a multiple-table updatable view, `INSERT' can work if it inserts
into a single table. `DELETE' is not supported.
The `WITH CHECK OPTION' clause can be given for an updatable view to
prevent inserts or updates to rows except those for which the `WHERE'
clause in the SELECT_STATEMENT is true.
In a `WITH CHECK OPTION' clause for an updatable view, the `LOCAL' and
`CASCADED' keywords determine the scope of check testing when the view
is defined in terms of another view. The `LOCAL' keyword restricts the
`CHECK OPTION' only to the view being defined. `CASCADED' causes the
checks for underlying views to be evaluated as well. When neither
keyword is given, the default is `CASCADED'. Consider the definitions
for the following table and set of views:
mysql> CREATE TABLE t1 (a INT);
mysql> CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 2
-> WITH CHECK OPTION;
mysql> CREATE VIEW v2 AS SELECT * FROM v1 WHERE a > 0
-> WITH LOCAL CHECK OPTION;
mysql> CREATE VIEW v3 AS SELECT * FROM v1 WHERE a > 0
-> WITH CASCADED CHECK OPTION;
Here the `v2' and `v3' views are defined in terms of another view, `v1'.
`v2' has a `LOCAL' check option, so inserts are tested only against the
`v2' check. `v3' has a `CASCADED' check option, so inserts are tested
not only against its own check, but against those of underlying views.
The following statements illustrate these differences:
mysql> INSERT INTO v2 VALUES (2);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO v3 VALUES (2);
ERROR 1369 (HY000): CHECK OPTION failed 'test.v3'
The updatability of views may be affected by the value of the
`updatable_views_with_limit' system variable. See
server-system-variables.
The `CREATE VIEW' statement was added in MySQL 5.0.1. The `WITH CHECK
OPTION' clause was implemented in MySQL 5.0.2.
Info Catalog
(mysql.info) alter-view
(mysql.info) views
(mysql.info) drop-view
automatically generated byinfo2html