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]
The CREATE VIEW
statement creates a
new view, or replaces an existing one if the OR
REPLACE
clause is given. If the view does not exist,
CREATE OR REPLACE VIEW
is the same as
CREATE VIEW
. If the view does
exist, CREATE OR REPLACE VIEW
is the same as
ALTER VIEW
.
The select_statement
is a
SELECT
statement that provides the
definition of the view. (When you select from the view, you select
in effect using the SELECT
statement.) select_statement
can select
from base tables or other views.
The view definition is “frozen” at creation time, so
changes to the underlying tables afterward do not affect the view
definition. For example, if a view is defined as SELECT
*
on a table, new columns added to the table later do
not become part of the view.
The ALGORITHM
clause affects how MySQL
processes the view. The DEFINER
and
SQL SECURITY
clauses specify the security
context to be used when checking access privileges at view
invocation time. The WITH CHECK OPTION
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.
The CREATE VIEW
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.
Within a stored program, the definition cannot refer to program parameters or local variables.
The SELECT
statement cannot
refer to prepared statement parameters.
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
CHECK TABLE
statement.
The definition cannot refer to a TEMPORARY
table, and you cannot create a TEMPORARY
view.
Any tables named in the view definition must exist at definition time.
You cannot associate a trigger with a view.
Aliases for column names in the
SELECT
statement are checked
against the maximum column length of 64 characters (not the
maximum alias length of 256 characters).
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 (mycol) AS SELECT 'abc';
Query OK, 0 rows affected (0.01 sec) mysql>SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec) mysql>SELECT "mycol" FROM v;
+-------+ | mycol | +-------+ | mycol | +-------+ 1 row in set (0.01 sec) mysql>SET sql_mode = 'ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec) mysql>SELECT "mycol" FROM v;
+-------+ | mycol | +-------+ | abc | +-------+ 1 row in set (0.00 sec)
The DEFINER
and SQL SECURITY
clauses determine which MySQL account to use when checking access
privileges for the view when a statement is executed that
references the view. The legal SQL SECURITY
characteristic values are DEFINER
and
INVOKER
. These indicate that the required
privileges must be held by the user who defined or invoked the
view, respectively. The default SQL SECURITY
value is DEFINER
.
If a user
value is given for the
DEFINER
clause, 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. The
definer can also be given as
CURRENT_USER
or
CURRENT_USER()
. The default
DEFINER
value is the user who executes the
CREATE VIEW
statement. This is the
same as specifying DEFINER = CURRENT_USER
explicitly.
If you specify the DEFINER
clause, 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.
If the SQL SECURITY
value is
DEFINER
but the definer account does not
exist when the view is referenced, an error occurs.
Within a view definition,
CURRENT_USER
returns the view's
DEFINER
value by default. For views defined
with the SQL SECURITY INVOKER
characteristic,
CURRENT_USER
returns the account
for the view's invoker. For information about user auditing within
views, see Section 5.5.8, “Auditing MySQL Account Activity”.
Within a stored routine that is defined with the SQL
SECURITY DEFINER
characteristic,
CURRENT_USER
returns the routine's
DEFINER
value. This also affects a view defined
within such a program, if the view definition contains a
DEFINER
value of
CURRENT_USER
.
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 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.
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 SQL
SECURITY
characteristic is
DEFINER
or INVOKER
,
respectively.
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 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.
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 must be checked at runtime, and the user who must
possess the privileges is determined by the SQL
SECURITY
values of the view v
and the
function f()
.
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
DEFINER
. The standard says that the definer of the view,
which is the same as the owner of the view's schema, gets
applicable privileges on the view (for example,
SELECT
) and may grant them. MySQL
has no concept of a schema “owner”, so MySQL adds a
clause to identify the definer. The DEFINER clause is an extension
where the intent is to have what the standard has; that is, a
permanent record of who defined the view.
The optional ALGORITHM
clause is a MySQL
extension to standard SQL. It affects how MySQL processes the
view. ALGORITHM
takes three values:
MERGE
, TEMPTABLE
, or
UNDEFINED
. The default algorithm is
UNDEFINED
if no ALGORITHM
clause is present. For more information, see
Section 18.5.2, “View Processing Algorithms”.
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-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 nonupdatable.
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
.
For more information about updatable views and the WITH
CHECK OPTION
clause, see
Section 18.5.3, “Updatable and Insertable Views”.
User Comments
A simple workaround for MySQL's limitation on local variables usage in views is to use a function, which returns variable's value:
create function book_subject
returns varchar(64) as
return @book_subject;
create view thematical_books as
select title
, author
from books
where subject = book_subject();
I ran into the restriction where your view cannot contain a subquery in the FROM clause. My work around was to make my subquery a view instead and then join my table with the view.
I tried:
create view v1 as select * from t1 JOIN (select * from t2 where ...) USING (id) where ..
which gave me the "subquery in FROM clause" error.
Solution/work around:
create view v2 as select * from t2 where ...;
create view v1 as select * from t1 JOIN v2 USING (id) where ...
(example above is an overly simplified version of what I did for demonstration puposes).
Simulating Materialized View
Materialized views are not updated every time they are accessed; they behave as static tables hence are much faster than normal view. Materialized views are useful when the system performs lots of queries on the view while the original data changes infrequently.
Since MySQL currently doesn't support materialized views, here is simple way to simulate materialized views that consists on creating a static table and a robust updating script.
The tipical command for creating a normal view is:
CREATE VIEW my_view AS SELECT <xxxxxx your select expression xxxxx>
In order to create the equivalent to a materialized view you create a table with the same name instead of a view.
Then you create the following MySQL command that can be run periodically, for example every night.
CREATE VIEW my_view AS SELECT <*** your select expression ***>
In order to create the equivalent to a materialized view you create a table with the same name instead of a view.
Then you create the following MySQL command that can be run periodically, for example every night.
CREATE TEMPORARY TABLE tmp_my_view SELECT <*** same select expression as before ***>
LOCK TABLE my_database.my_view WRITE;
DELETE FROM my_database.my_view;
INSERT INTO my_database.my_view SELECT * FROM tmp_my_view;
UNLOCK TABLES;
The previous MySQL script can be run in Unix/Linux system by adding the following command as a cron entry:
mysql -u userid --password=XXXXX -D my_database < update_materialized_view.mysql >/dev/null 2>&1
I was able to simulate a materialized view off of a regular view by executing a simpler script than above.
With any regular view, "myRegularView":
DROP TABLE IF EXISTS `myDatabase`.`myMaterializedView`;
CREATE TABLE `myDatabase`.`myMaterializedView` SELECT * from `myDatabase`.`myRegularView`;
Running this script at whatever interval is appropriate will create a new regular table with the contents of the view at the time of execution.
> DROP TABLE IF EXISTS `myDatabase`.`myMaterializedView`;
> CREATE TABLE `myDatabase`.`myMaterializedView` SELECT * from `myDatabase`.`myRegularView`;
If you do this without locking the table you risk performing CRUD operations against the table while its missing, which will throw errors or show empty results when something is legitimately there. Obviously simply locking the table won't work, because a DROP TABLE will also drop the lock. Since RENAME TABLE locks all the tables involved and is typically a very quick operation, making a working table then using RENAME TABLE to swap the tables will work and remain functional throughout.
For example:
CREATE TABLE new_materialized_view SELECT * from regular_view;
RENAME TABLE materialized_view = old_materialized_view, new_materialized_view = materialized_view;
DROP TABLE IF EXISTS old_materialized_view;
Add your own comment.