<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"><title>Chapter 20. Views</title><link rel="stylesheet" href="mysql-html.css" type="text/css"><meta name="generator" content="DocBook XSL Stylesheets V1.69.1"><link rel="start" href="index.html" title="MySQL 5.0 Reference Manual"><link rel="up" href="index.html" title="MySQL 5.0 Reference Manual"><link rel="prev" href="triggers.html" title="Chapter 19. Triggers"><link rel="next" href="information-schema.html" title="Chapter 21. INFORMATION_SCHEMA Tables"></head><body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="3" align="center">Chapter 20. Views</th></tr><tr><td width="20%" align="left"><a accesskey="p" href="triggers.html">Prev</a> </td><th width="60%" align="center"> </th><td width="20%" align="right"> <a accesskey="n" href="information-schema.html">Next</a></td></tr></table><hr></div><div class="chapter" lang="en"><div class="titlepage"><div><div><h2 class="title"><a name="views"></a>Chapter 20. Views</h2></div></div></div><div class="toc"><p><b>Table of Contents</b></p><dl><dt><span class="section"><a href="views.html#alter-view">20.1. <code class="literal">ALTER VIEW</code> Syntax</a></span></dt><dt><span class="section"><a href="views.html#create-view">20.2. <code class="literal">CREATE VIEW</code> Syntax</a></span></dt><dt><span class="section"><a href="views.html#drop-view">20.3. <code class="literal">DROP VIEW</code> Syntax</a></span></dt></dl></div><a class="indexterm" name="id3007567"></a><p>
Views (including updatable views) are implemented in MySQL Server
5.0. Views are available in binary releases from 5.0.1
and up.
</p><p>
Answers to some frequently asked questions concerning views in MySQL
5.0 can be found in <a href="faqs.html#faqs-views" title="A.6. MySQL 5.0 FAQ — Views">Section A.6, “MySQL 5.0 FAQ — Views”</a>.
</p><p>
This chapter discusses the following topics:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Creating or altering views with <code class="literal">CREATE VIEW</code>
or <code class="literal">ALTER VIEW</code>
</p></li><li><p>
Destroying views with <code class="literal">DROP VIEW</code>
</p></li></ul></div><p>
Discussion of restrictions on use of views is given in
<a href="restrictions.html#view-restrictions" title="F.4. Restrictions on Views">Section F.4, “Restrictions on Views”</a>.
</p><p>
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 <a href="server-administration.html#mysql-upgrade" title="5.5.8. mysql_upgrade — Check Tables for MySQL Upgrade">Section 5.5.8, “<span><strong class="command">mysql_upgrade</strong></span> — Check Tables for MySQL Upgrade”</a>.
</p><p>
Metadata about views can be obtained from the
<code class="literal">INFORMATION_SCHEMA.VIEWS</code> table and by using the
<code class="literal">SHOW CREATE VIEW</code> statement. See
<a href="information-schema.html#views-table" title="21.15. The INFORMATION_SCHEMA VIEWS Table">Section 21.15, “The <code class="literal">INFORMATION_SCHEMA VIEWS</code> Table”</a>, and
<a href="sql-syntax.html#show-create-view" title="12.5.4.7. SHOW CREATE VIEW Syntax">Section 12.5.4.7, “<code class="literal">SHOW CREATE VIEW</code> Syntax”</a>.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="alter-view"></a>20.1. <code class="literal">ALTER VIEW</code> Syntax</h2></div></div></div><a class="indexterm" name="id3007691"></a><pre class="programlisting">ALTER
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = { <em class="replaceable"><code>user</code></em> | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW <em class="replaceable"><code>view_name</code></em> [(<em class="replaceable"><code>column_list</code></em>)]
AS <em class="replaceable"><code>select_statement</code></em>
[WITH [CASCADED | LOCAL] CHECK OPTION]
</pre><p>
This statement changes the definition of a view, which must exist.
The syntax is similar to that for <code class="literal">CREATE VIEW</code>
and the effect is the same as for <code class="literal">CREATE OR REPLACE
VIEW</code>. See <a href="views.html#create-view" title="20.2. CREATE VIEW Syntax">Section 20.2, “<code class="literal">CREATE VIEW</code> Syntax”</a>. This statement
requires the <code class="literal">CREATE VIEW</code> and
<code class="literal">DROP</code> privileges for the view, and some
privilege for each column referred to in the
<code class="literal">SELECT</code> statement. As of MySQL 5.0.52,
<code class="literal">ALTER VIEW</code> is allowed only to the original
definer or users with the <code class="literal">SUPER</code> privilege.
</p><p>
This statement was added in MySQL 5.0.1. The
<code class="literal">DEFINER</code> and <code class="literal">SQL SECURITY</code>
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 <a href="views.html#create-view" title="20.2. CREATE VIEW Syntax">Section 20.2, “<code class="literal">CREATE VIEW</code> Syntax”</a>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="create-view"></a>20.2. <code class="literal">CREATE VIEW</code> Syntax</h2></div></div></div><a class="indexterm" name="id3007843"></a><a class="indexterm" name="id3007852"></a><a class="indexterm" name="id3007865"></a><pre class="programlisting">CREATE
[OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = { <em class="replaceable"><code>user</code></em> | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW <em class="replaceable"><code>view_name</code></em> [(<em class="replaceable"><code>column_list</code></em>)]
AS <em class="replaceable"><code>select_statement</code></em>
[WITH [CASCADED | LOCAL] CHECK OPTION]
</pre><p>
The <code class="literal">CREATE VIEW</code> statement creates a new view,
or replaces an existing one if the <code class="literal">OR REPLACE</code>
clause is given. This statement was added in MySQL 5.0.1. If the
view does not exist, <code class="literal">CREATE OR REPLACE VIEW</code> is
the same as <code class="literal">CREATE VIEW</code>. If the view does
exist, <code class="literal">CREATE OR REPLACE VIEW</code> is the same as
<code class="literal">ALTER VIEW</code>.
</p><p>
The <em class="replaceable"><code>select_statement</code></em> is a
<code class="literal">SELECT</code> statement that provides the definition
of the view. (When you select from the view, you select in effect
using the <code class="literal">SELECT</code> statement.)
<em class="replaceable"><code>select_statement</code></em> can select from base
tables or other views.
</p><p>
The <code class="literal">ALGORITHM</code> clause affects how MySQL
processes the view. The <code class="literal">DEFINER</code> and
<code class="literal">SQL SECURITY</code> clauses specify the security
context to be used when checking access privileges at view
invocation time. The <code class="literal">WITH CHECK OPTION</code> clause
can be given to constrain inserts or updates to rows in tables
referenced by the view. These clauses are described later in this
section.
</p><p>
The <code class="literal">CREATE VIEW</code> statement requires the
<code class="literal">CREATE VIEW</code> privilege for the view, and some
privilege for each column selected by the
<code class="literal">SELECT</code> statement. For columns used elsewhere in
the <code class="literal">SELECT</code> statement you must have the
<code class="literal">SELECT</code> privilege. If the <code class="literal">OR
REPLACE</code> clause is present, you must also have the
<code class="literal">DROP</code> privilege for the view.
</p><p>
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
<em class="replaceable"><code>db_name.view_name</code></em> when you create it.
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE VIEW test.v AS SELECT * FROM t;</code></strong>
</pre><p>
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.
</p><p>
Views must have unique column names with no duplicates, just like
base tables. By default, the names of the columns retrieved by the
<code class="literal">SELECT</code> statement are used for the view column
names. To define explicit names for the view columns, the optional
<em class="replaceable"><code>column_list</code></em> clause can be given as a
list of comma-separated identifiers. The number of names in
<em class="replaceable"><code>column_list</code></em> must be the same as the
number of columns retrieved by the <code class="literal">SELECT</code>
statement.
</p><p>
Columns retrieved by the <code class="literal">SELECT</code> statement can
be simple references to table columns. They can also be
expressions that use functions, constant values, operators, and so
forth.
</p><p>
Unqualified table or view names in the <code class="literal">SELECT</code>
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.
</p><p>
A view can be created from many kinds of <code class="literal">SELECT</code>
statements. It can refer to base tables or other views. It can use
joins, <code class="literal">UNION</code>, and subqueries. The
<code class="literal">SELECT</code> 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:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE t (qty INT, price INT);</code></strong>
mysql> <strong class="userinput"><code>INSERT INTO t VALUES(3, 50);</code></strong>
mysql> <strong class="userinput"><code>CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;</code></strong>
mysql> <strong class="userinput"><code>SELECT * FROM v;</code></strong>
+------+-------+-------+
| qty | price | value |
+------+-------+-------+
| 3 | 50 | 150 |
+------+-------+-------+
</pre><p>
A view definition is subject to the following restrictions:
</p><div class="itemizedlist"><ul type="disc"><li><p>
The <code class="literal">SELECT</code> statement cannot contain a
subquery in the <code class="literal">FROM</code> clause.
</p></li><li><p>
The <code class="literal">SELECT</code> statement cannot refer to system
or user variables.
</p></li><li><p>
The <code class="literal">SELECT</code> statement cannot refer to
prepared statement parameters.
</p></li><li><p>
Within a stored routine, the definition cannot refer to
routine parameters or local variables.
</p></li><li><p>
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. In this case,
use of the view results in an error. To check a view
definition for problems of this kind, use the <code class="literal">CHECK
TABLE</code> statement.
</p></li><li><p>
The definition cannot refer to a <code class="literal">TEMPORARY</code>
table, and you cannot create a <code class="literal">TEMPORARY</code>
view.
</p></li><li><p>
The tables named in the view definition must already exist.
</p></li><li><p>
You cannot associate a trigger with a view.
</p></li></ul></div><p>
<code class="literal">ORDER BY</code> is allowed in a view definition, but
it is ignored if you select from a view using a statement that has
its own <code class="literal">ORDER BY</code>.
</p><p>
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 <code class="literal">LIMIT</code> clause, and you select from
the view using a statement that has its own
<code class="literal">LIMIT</code> clause, it is undefined which limit
applies. This same principle applies to options such as
<code class="literal">ALL</code>, <code class="literal">DISTINCT</code>, or
<code class="literal">SQL_SMALL_RESULT</code> that follow the
<code class="literal">SELECT</code> keyword, and to clauses such as
<code class="literal">INTO</code>, <code class="literal">FOR UPDATE</code>,
<code class="literal">LOCK IN SHARE MODE</code>, and
<code class="literal">PROCEDURE</code>.
</p><p>
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:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE VIEW v (mycol) AS SELECT 'abc';</code></strong>
Query OK, 0 rows affected (0.01 sec)
mysql> <strong class="userinput"><code>SET sql_mode = '';</code></strong>
Query OK, 0 rows affected (0.00 sec)
mysql> <strong class="userinput"><code>SELECT "mycol" FROM v;</code></strong>
+-------+
| mycol |
+-------+
| mycol |
+-------+
1 row in set (0.01 sec)
mysql> <strong class="userinput"><code>SET sql_mode = 'ANSI_QUOTES';</code></strong>
Query OK, 0 rows affected (0.00 sec)
mysql> <strong class="userinput"><code>SELECT "mycol" FROM v;</code></strong>
+-------+
| mycol |
+-------+
| abc |
+-------+
1 row in set (0.00 sec)
</pre><p>
The <code class="literal">DEFINER</code> and <code class="literal">SQL SECURITY</code>
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.
</p><p>
The default <code class="literal">DEFINER</code> value is the user who
executes the <code class="literal">CREATE VIEW</code> statement. This is the
same as specifying <code class="literal">DEFINER = CURRENT_USER</code>
explicitly.
<a href="functions.html#function_current-user"><code class="literal">CURRENT_USER</code></a>
also can be given as
<a href="functions.html#function_current-user"><code class="literal">CURRENT_USER()</code></a>. If a
<em class="replaceable"><code>user</code></em> value is given, it should be a
MySQL account in
<code class="literal">'<em class="replaceable"><code>user_name</code></em>'@'<em class="replaceable"><code>host_name</code></em>'</code>
format (the same format used in the <code class="literal">GRANT</code>
statement). The <em class="replaceable"><code>user_name</code></em> and
<em class="replaceable"><code>host_name</code></em> values both are required.
</p><p>
If you specify the <code class="literal">DEFINER</code> clause, you cannot
set the value to any user but your own unless you have the
<code class="literal">SUPER</code> privilege. These rules determine the
legal <code class="literal">DEFINER</code> user values:
</p><div class="itemizedlist"><ul type="disc"><li><p>
If you do not have the <code class="literal">SUPER</code> privilege, the
only legal <em class="replaceable"><code>user</code></em> value is your own
account, either specified literally or by using
<a href="functions.html#function_current-user"><code class="literal">CURRENT_USER</code></a>.
You cannot set the definer to some other account.
</p></li><li><p>
If you have the <code class="literal">SUPER</code> privilege, you can
specify any syntactically legal account name. If the account
does not actually exist, a warning is generated.
</p></li></ul></div><p>
Within a stored routine that is defined with the <code class="literal">SQL
SECURITY DEFINER</code> characteristic,
<a href="functions.html#function_current-user"><code class="literal">CURRENT_USER</code></a>
returns the routine creator. This also affects a view defined
within such a routine, if the view definition contains a
<code class="literal">DEFINER</code> value of
<a href="functions.html#function_current-user"><code class="literal">CURRENT_USER</code></a>.
</p><p>
The <code class="literal">SQL SECURITY</code> characteristic determines
which MySQL account to use when checking access privileges for the
view when a statement is executed that references the view. The
legal characteristic values are <code class="literal">DEFINER</code> and
<code class="literal">INVOKER</code>. These indicate that the required
privileges must be held by the user who defined or invoked the
view, respectively. The default <code class="literal">SQL SECURITY</code>
value is <code class="literal">DEFINER</code>. If the value is
<code class="literal">DEFINER</code> but the definer account does not exist
when the view is referenced, an error occurs.
</p><p>
As of MySQL 5.0.16 (when the <code class="literal">DEFINER</code> and
<code class="literal">SQL SECURITY</code> clauses were implemented), view
privileges are checked like this:
</p><div class="itemizedlist"><ul type="disc"><li><p>
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 table
columns, the creator must have privileges for the columns, as
described previously. If the 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.
</p></li><li><p>
When a view is referenced, privileges for objects accessed by
the view are checked against the privileges held by the view
creator or invoker, depending on whether the <code class="literal">SQL
SECURITY</code> characteristic is
<code class="literal">DEFINER</code> or <code class="literal">INVOKER</code>,
respectively.
</p></li><li><p>
If reference to a view causes execution of a stored function,
privilege checking for statements executed within the function
depend on whether the function is defined with a <code class="literal">SQL
SECURITY</code> characteristic of
<code class="literal">DEFINER</code> or <code class="literal">INVOKER</code>. If
the security characteristic is <code class="literal">DEFINER</code>, the
function runs with the privileges of its creator. If the
characteristic is <code class="literal">INVOKER</code>, the function
runs with the privileges determined by the view's <code class="literal">SQL
SECURITY</code> characteristic.
</p></li></ul></div><p>
Prior to MySQL 5.0.16 (before the <code class="literal">DEFINER</code> and
<code class="literal">SQL SECURITY</code> clauses were implemented),
privileges required for objects used in a view are checked at view
creation time.
</p><p>
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 <code class="literal">f()</code>:
</p><pre class="programlisting">CREATE VIEW v AS SELECT * FROM t WHERE t.id = f(t.name);
</pre><p>
Suppose that <code class="literal">f()</code> contains a statement such as
this:
</p><pre class="programlisting">IF name IS NULL then
CALL p1();
ELSE
CALL p2();
END IF;
</pre><p>
The privileges required for executing statements within
<code class="literal">f()</code> need to be checked when
<code class="literal">f()</code> executes. This might mean that privileges
are needed for <code class="literal">p1()</code> or <code class="literal">p2()</code>,
depending on the execution path within <code class="literal">f()</code>.
Those privileges must be checked at runtime, and the user who must
possess the privileges is determined by the <code class="literal">SQL
SECURITY</code> values of the view <code class="literal">v</code> and the
function <code class="literal">f()</code>.
</p><p>
The <code class="literal">DEFINER</code> and <code class="literal">SQL SECURITY</code>
clauses for views are extensions to standard SQL. In standard SQL,
views are handled using the rules for <code class="literal">SQL SECURITY
INVOKER</code>.
</p><p>
If you invoke a view that was created before MySQL 5.0.13, it is
treated as though it was created with a <code class="literal">SQL SECURITY
DEFINER</code> clause and with a <code class="literal">DEFINER</code>
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 <code class="literal">DEFINER</code> clause.
</p><p>
The optional <code class="literal">ALGORITHM</code> clause is a MySQL
extension to standard SQL. It affects how MySQL processes the
view. <code class="literal">ALGORITHM</code> takes three values:
<code class="literal">MERGE</code>, <code class="literal">TEMPTABLE</code>, or
<code class="literal">UNDEFINED</code>. The default algorithm is
<code class="literal">UNDEFINED</code> if no <code class="literal">ALGORITHM</code>
clause is present.
</p><p>
For <code class="literal">MERGE</code>, 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.
</p><p>
For <code class="literal">TEMPTABLE</code>, the results from the view are
retrieved into a temporary table, which then is used to execute
the statement.
</p><p>
For <code class="literal">UNDEFINED</code>, MySQL chooses which algorithm to
use. It prefers <code class="literal">MERGE</code> over
<code class="literal">TEMPTABLE</code> if possible, because
<code class="literal">MERGE</code> is usually more efficient and because a
view cannot be updatable if a temporary table is used.
</p><p>
A reason to choose <code class="literal">TEMPTABLE</code> 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
<code class="literal">MERGE</code> algorithm so that other clients that use
the view are not blocked as long.
</p><p>
A view algorithm can be <code class="literal">UNDEFINED</code> for three
reasons:
</p><div class="itemizedlist"><ul type="disc"><li><p>
No <code class="literal">ALGORITHM</code> clause is present in the
<code class="literal">CREATE VIEW</code> statement.
</p></li><li><p>
The <code class="literal">CREATE VIEW</code> statement has an explicit
<code class="literal">ALGORITHM = UNDEFINED</code> clause.
</p></li><li><p>
<code class="literal">ALGORITHM = MERGE</code> 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
<code class="literal">UNDEFINED</code>.
</p></li></ul></div><p>
As mentioned earlier, <code class="literal">MERGE</code> 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 <code class="literal">MERGE</code> algorithm works. The
examples assume that there is a view <code class="literal">v_merge</code>
that has this definition:
</p><pre class="programlisting">CREATE ALGORITHM = MERGE VIEW v_merge (vc1, vc2) AS
SELECT c1, c2 FROM t WHERE c3 > 100;
</pre><p>
Example 1: Suppose that we issue this statement:
</p><pre class="programlisting">SELECT * FROM v_merge;
</pre><p>
MySQL handles the statement as follows:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">v_merge</code> becomes <code class="literal">t</code>
</p></li><li><p>
<code class="literal">*</code> becomes <code class="literal">vc1, vc2</code>,
which corresponds to <code class="literal">c1, c2</code>
</p></li><li><p>
The view <code class="literal">WHERE</code> clause is added
</p></li></ul></div><p>
The resulting statement to be executed becomes:
</p><pre class="programlisting">SELECT c1, c2 FROM t WHERE c3 > 100;
</pre><p>
Example 2: Suppose that we issue this statement:
</p><pre class="programlisting">SELECT * FROM v_merge WHERE vc1 < 100;
</pre><p>
This statement is handled similarly to the previous one, except
that <code class="literal">vc1 < 100</code> becomes <code class="literal">c1 <
100</code> and the view <code class="literal">WHERE</code> clause is
added to the statement <code class="literal">WHERE</code> clause using an
<a href="functions.html#operator_and"><code class="literal">AND</code></a> 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:
</p><pre class="programlisting">SELECT c1, c2 FROM t WHERE (c3 > 100) AND (c1 < 100);
</pre><p>
Effectively, the statement to be executed has a
<code class="literal">WHERE</code> clause of this form:
</p><pre class="programlisting">WHERE (select WHERE) AND (view WHERE)
</pre><p>
The <code class="literal">MERGE</code> algorithm requires a one-to-one
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:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Aggregate functions (<a href="functions.html#function_sum"><code class="literal">SUM()</code></a>,
<a href="functions.html#function_min"><code class="literal">MIN()</code></a>,
<a href="functions.html#function_max"><code class="literal">MAX()</code></a>,
<a href="functions.html#function_count"><code class="literal">COUNT()</code></a>, and so forth)
</p></li><li><p>
<code class="literal">DISTINCT</code>
</p></li><li><p>
<code class="literal">GROUP BY</code>
</p></li><li><p>
<code class="literal">HAVING</code>
</p></li><li><p>
<code class="literal">UNION</code> or <code class="literal">UNION ALL</code>
</p></li><li><p>
Subquery in the select list
</p></li><li><p>
Refers only to literal values (in this case, there is no
underlying table)
</p></li></ul></div><p>
Some views are updatable. That is, you can use them in statements
such as <code class="literal">UPDATE</code>, <code class="literal">DELETE</code>, or
<code class="literal">INSERT</code> to update the contents of the underlying
table. For a view to be updatable, there must be a one-to-one
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:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Aggregate functions (<a href="functions.html#function_sum"><code class="literal">SUM()</code></a>,
<a href="functions.html#function_min"><code class="literal">MIN()</code></a>,
<a href="functions.html#function_max"><code class="literal">MAX()</code></a>,
<a href="functions.html#function_count"><code class="literal">COUNT()</code></a>, and so forth)
</p></li><li><p>
<code class="literal">DISTINCT</code>
</p></li><li><p>
<code class="literal">GROUP BY</code>
</p></li><li><p>
<code class="literal">HAVING</code>
</p></li><li><p>
<code class="literal">UNION</code> or <code class="literal">UNION ALL</code>
</p></li><li><p>
Subquery in the select list
</p></li><li><p>
Certain joins (see additional join discussion later in this
section)
</p></li><li><p>
Non-updatable view in the <code class="literal">FROM</code> clause
</p></li><li><p>
A subquery in the <code class="literal">WHERE</code> clause that refers
to a table in the <code class="literal">FROM</code> clause
</p></li><li><p>
Refers only to literal values (in this case, there is no
underlying table to update)
</p></li><li><p>
<code class="literal">ALGORITHM = TEMPTABLE</code> (use of a temporary
table always makes a view non-updatable)
</p></li></ul></div><p>
With respect to insertability (being updatable with
<code class="literal">INSERT</code> statements), an updatable view is
insertable if it also satisfies these additional requirements for
the view columns:
</p><div class="itemizedlist"><ul type="disc"><li><p>
There must be no duplicate view column names.
</p></li><li><p>
The view must contain all columns in the base table that do
not have a default value.
</p></li><li><p>
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:
</p><pre class="programlisting">3.14159
col1 + 3
UPPER(col2)
col3 / col4
(<em class="replaceable"><code>subquery</code></em>)
</pre></li></ul></div><p>
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:
</p><pre class="programlisting">CREATE VIEW v AS SELECT col1, 1 AS col2 FROM t;
</pre><p>
This view is not insertable because <code class="literal">col2</code> is
derived from an expression. But it is updatable if the update does
not try to update <code class="literal">col2</code>. This update is
allowable:
</p><pre class="programlisting">UPDATE v SET col1 = 0;
</pre><p>
This update is not allowable because it attempts to update a
derived column:
</p><pre class="programlisting">UPDATE v SET col2 = 0;
</pre><p>
It is sometimes possible for a multiple-table view to be
updatable, assuming that it can be processed with the
<code class="literal">MERGE</code> algorithm. For this to work, the view
must use an inner join (not an outer join or a
<code class="literal">UNION</code>). Also, only a single table in the view
definition can be updated, so the <code class="literal">SET</code> clause
must name only columns from one of the tables in the view. Views
that use <code class="literal">UNION ALL</code> are disallowed even though
they might be theoretically updatable, because the implementation
uses temporary tables to process them.
</p><p>
For a multiple-table updatable view, <code class="literal">INSERT</code> can
work if it inserts into a single table. <code class="literal">DELETE</code>
is not supported.
</p><p>
<code class="literal">INSERT DELAYED</code> is not supported for views.
</p><p>
If a table contains an <code class="literal">AUTO_INCREMENT</code> column,
inserting into an insertable view on the table that does not
include the <code class="literal">AUTO_INCREMENT</code> column does not
change the value of
<a href="functions.html#function_last-insert-id"><code class="literal">LAST_INSERT_ID()</code></a>, because the side
effects of inserting default values into columns not part of the
view should not be visible.
</p><p>
The <code class="literal">WITH CHECK OPTION</code> clause can be given for
an updatable view to prevent inserts or updates to rows except
those for which the <code class="literal">WHERE</code> clause in the
<em class="replaceable"><code>select_statement</code></em> is true. The
<code class="literal">WITH CHECK OPTION</code> clause was implemented in
MySQL 5.0.2.
</p><p>
In a <code class="literal">WITH CHECK OPTION</code> clause for an updatable
view, the <code class="literal">LOCAL</code> and <code class="literal">CASCADED</code>
keywords determine the scope of check testing when the view is
defined in terms of another view. The <code class="literal">LOCAL</code>
keyword restricts the <code class="literal">CHECK OPTION</code> only to the
view being defined. <code class="literal">CASCADED</code> causes the checks
for underlying views to be evaluated as well. When neither keyword
is given, the default is <code class="literal">CASCADED</code>. Consider the
definitions for the following table and set of views:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE t1 (a INT);</code></strong>
mysql> <strong class="userinput"><code>CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 2</code></strong>
-> <strong class="userinput"><code>WITH CHECK OPTION;</code></strong>
mysql> <strong class="userinput"><code>CREATE VIEW v2 AS SELECT * FROM v1 WHERE a > 0</code></strong>
-> <strong class="userinput"><code>WITH LOCAL CHECK OPTION;</code></strong>
mysql> <strong class="userinput"><code>CREATE VIEW v3 AS SELECT * FROM v1 WHERE a > 0</code></strong>
-> <strong class="userinput"><code>WITH CASCADED CHECK OPTION;</code></strong>
</pre><p>
Here the <code class="literal">v2</code> and <code class="literal">v3</code> views are
defined in terms of another view, <code class="literal">v1</code>.
<code class="literal">v2</code> has a <code class="literal">LOCAL</code> check option,
so inserts are tested only against the <code class="literal">v2</code>
check. <code class="literal">v3</code> has a <code class="literal">CASCADED</code>
check option, so inserts are tested not only against its own
check, but against those of underlying views. The following
statements illustrate these differences:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>INSERT INTO v2 VALUES (2);</code></strong>
Query OK, 1 row affected (0.00 sec)
mysql> <strong class="userinput"><code>INSERT INTO v3 VALUES (2);</code></strong>
ERROR 1369 (HY000): CHECK OPTION failed 'test.v3'
</pre><p>
MySQL sets a flag, called the view updatability flag, at
<code class="literal">CREATE VIEW</code> time. The flag is set to
<code class="literal">YES</code> (true) if <code class="literal">UPDATE</code> and
<code class="literal">DELETE</code> (and similar operations) are legal for
the view. Otherwise, the flag is set to <code class="literal">NO</code>
(false). The <code class="literal">IS_UPDATABLE</code> column in the
<code class="literal">INFORMATION_SCHEMA.VIEWS</code> table displays the
status of this flag. It means that the server always knows whether
a view is updatable. If the view is not updatable, statements such
<code class="literal">UPDATE</code>, <code class="literal">DELETE</code>, and
<code class="literal">INSERT</code> are illegal and will be rejected. (Note
that even if a view is updatable, it might not be possible to
insert into it, as described elsewhere in this section.)
</p><p>
The updatability of views may be affected by the value of the
<code class="literal">updatable_views_with_limit</code> system variable. See
<a href="server-administration.html#server-system-variables" title="5.2.3. System Variables">Section 5.2.3, “System Variables”</a>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="drop-view"></a>20.3. <code class="literal">DROP VIEW</code> Syntax</h2></div></div></div><a class="indexterm" name="id3010236"></a><pre class="programlisting">DROP VIEW [IF EXISTS]
<em class="replaceable"><code>view_name</code></em> [, <em class="replaceable"><code>view_name</code></em>] ...
[RESTRICT | CASCADE]
</pre><p>
<code class="literal">DROP VIEW</code> removes one or more views. You must
have the <code class="literal">DROP</code> privilege for each view. If any
of the views named in the argument list do not exist, MySQL
returns an error indicating by name which non-existing views it
was unable to drop, but it also drops all of the views in the list
that do exist.
</p><p>
The <code class="literal">IF EXISTS</code> clause prevents an error from
occurring for views that don't exist. When this clause is given, a
<code class="literal">NOTE</code> is generated for each non-existent view.
See <a href="sql-syntax.html#show-warnings" title="12.5.4.28. SHOW WARNINGS Syntax">Section 12.5.4.28, “<code class="literal">SHOW WARNINGS</code> Syntax”</a>.
</p><p>
<code class="literal">RESTRICT</code> and <code class="literal">CASCADE</code>, if
given, are parsed and ignored.
</p><p>
This statement was added in MySQL 5.0.1.
</p></div></div><div class="navfooter"><hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="triggers.html">Prev</a> </td><td width="20%" align="center"> </td><td width="40%" align="right"> <a accesskey="n" href="information-schema.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 19. Triggers </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> Chapter 21. <code class="literal">INFORMATION_SCHEMA</code> Tables</td></tr></table></div></body></html>
distrib
>
Mandriva
>
2008.1
>
x86_64
>
media
>
main-testing
>
by-pkgid
>
b1e2421f2416edfc24c5845fbc1c5a2e
>
files
>
118
