|
|
Views (including updatable views) are implemented in MySQL Server 5.0. Views are available in binary releases from 5.0.1 and up.
This chapter discusses the following topics:
Creating or altering views with CREATE VIEW
or ALTER VIEW
Destroying views with DROP VIEW
Discussion of restrictions on use of views is given in Section I.4, “Restrictions on Views”.
To use views if you have upgraded to MySQL 5.0.1 from an older release, you should upgrade your grant tables so that they contain the view-related privileges. See Section 5.6.2, “mysql_upgrade — Check Tables for MySQL Upgrade”.
Metadata about views can be obtained from the
INFORMATION_SCHEMA.VIEWS
table and by using the
SHOW CREATE VIEW
statement. See
Section 20.15, “The INFORMATION_SCHEMA VIEWS
Table”, and
Section 13.5.4.7, “SHOW CREATE VIEW
Syntax”.
ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = {user
| CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }] VIEWview_name
[(column_list
)] ASselect_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
This statement changes the definition of an existing view. The
syntax is similar to that for CREATE VIEW
. See
Section 19.2, “CREATE VIEW
Syntax”. This statement requires the
CREATE VIEW
and DROP
privileges for the view, and some privilege for each column
referred to in the SELECT
statement.
This statement was added in MySQL 5.0.1. The
DEFINER
and SQL SECURITY
clauses may be used as of MySQL 5.0.16 to specify the security
context to be used when checking access privileges at view
invocation time. For details, see Section 19.2, “CREATE VIEW
Syntax”.
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = {user
| CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }] VIEWview_name
[(column_list
)] ASselect_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
'
format (the same format used in the user_name
'@'host_name
'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
Section 5.2.2, “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.
DROP VIEW [IF EXISTS]view_name
[,view_name
] ... [RESTRICT | CASCADE]
DROP VIEW
removes one or more views. You must
have the DROP
privilege for each view.
The IF EXISTS
clause prevents an error from
occurring for views that don't exist. When this clause is given, a
NOTE
is generated for each non-existent view.
See Section 13.5.4.25, “SHOW WARNINGS
Syntax”.
RESTRICT
and CASCADE
, if
given, are parsed and ignored.
This statement was added in MySQL 5.0.1.