Documentation Home
MySQL 5.5 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual

13.1.20 CREATE VIEW Syntax

    [DEFINER = { user | CURRENT_USER }]
    VIEW view_name [(column_list)]
    AS select_statement

The CREATE VIEW statement creates a new view, or replaces an existing view 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. (Selecting from the view selects, in effect, using the SELECT statement.) The select_statement can select from base tables or other views.

The view definition is frozen at creation time. 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. CREATE VIEW might also require the SUPER privilege, depending on the DEFINER value, as described later in this section.

When a view is referenced, privilege checking occurs as described later in this section.

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, use db_name.view_name syntax to qualify the view name with the database name:

mysql> CREATE VIEW test.v AS SELECT * FROM t;

Within a database, base tables and views share the same namespace, so a base table and a view cannot have the same name.

Columns retrieved by the SELECT statement can be simple references to table columns, or expressions that use functions, constant values, operators, and so forth.

A view must have unique column names with no duplicates, just like a base table. 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.

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 appropriate 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 variables or user-defined variables.

  • Within a stored program, the SELECT statement 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. After the 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.

  • 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 permitted 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 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 valid SQL SECURITY characteristic values are DEFINER (the default) and INVOKER. These indicate that the required privileges must be held by the user who defined or invoked the view, respectively.

If a user value is given for the DEFINER clause, it should be a MySQL account specified as 'user_name'@'host_name' (the same format used in the GRANT statement), 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 valid DEFINER user values:

  • If you do not have the SUPER privilege, the only valid 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 valid account name. If the account does not exist, a warning is generated.

  • Although it is possible to create a view with a nonexistent DEFINER account, an error occurs when the view is referenced if the SQL SECURITY value is DEFINER but the definer account does not exist.

For more information about view security, see Section 20.6, “Access Control for Stored Programs and Views”.

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 6.3.13, “SQL-Based MySQL Account Activity Auditing”.

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 routine, if the view definition contains a DEFINER value of CURRENT_USER.

MySQL checks view privileges 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 some privilege for each column in the select list of the definition, and the SELECT privilege for each column used elsewhere in the definition. If the definition refers to a stored function, only the privileges needed to invoke the function can be checked. The privileges required at function invocation time can be checked only as it executes: For different invocations, different execution paths within the function might be taken.

  • The user who references a view must have appropriate privileges to access it (SELECT to select from it, INSERT to insert into it, and so forth.)

  • When a view has been referenced, privileges for objects accessed by the view are checked against the privileges held by the view DEFINER account 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 SQL SECURITY characteristic is DEFINER or INVOKER. If the security characteristic is DEFINER, the function runs with the privileges of the DEFINER account. 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():


Suppose that f() contains a statement such as this:

IF name IS NULL then
  CALL p1();
  CALL p2();

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. This is why the default DEFINER value is the account of the view creator.

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 20.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 20.5.3, “Updatable and Insertable Views”, and Section 20.5.4, “The View WITH CHECK OPTION Clause”.

Download this Manual
User Comments
  Posted by Olexandr Melnyk on June 19, 2007
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();
  Posted by Tim Champion on August 31, 2007
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).
  Posted by Rafael Palacios on November 14, 2007
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;

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

  Posted by Justin Rennell on July 2, 2009
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.
  Posted by Aaron Tavistock on July 9, 2009
> 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;

  Posted by Ravishankar Narayana on October 13, 2010
Keep in mind there is problem with using group by with roll up in views that select using a join on 2 or more tables.

This is reported here..
  Posted by Manolo Guerrero on October 14, 2010
Creating a materialized view emulation as described above looks good, the only problem is that we are inheriting the lack of indexes MySQL views expose.

My solution is to create a correctly indexed table according to my needs, having the exact same structure as the view, and then running something like this:

LOCK TABLES materializedView WRITE;
TRUNCATE materializedView;
INSERT INTO materializedView SELECT * FROM regularView;

That way all indexes from materializedView are preserved on every "refresh".

I'm planning to use this in an application I'm doing right now, where we will have a lot more SELECTs than inserts/updates. If I keep a regular view for my SELECTs, I'll be asking the server to make tons of calculations every time someone needs to know how many items are on stock for product "A", instead, I'll have all SELECTs towards the "materializedView" with correct SKU, Store and Period indexes.

The view "refresh" will occur every time someone runs an INSERT or UPDATE, which will be on a 20 to 1 ratio. (20 Selects for every Update or Insert)

I hope things go as smooth as I'm planning. Greetings ;-)
  Posted by Clifford Janson on November 26, 2013
There is a way to maintain a materialized view without having to do periodic refreshes, by deploying triggers that instantly update the view whenever the underlying table changes. See:
Sign Up Login You must be logged in to post a comment.