CREATE VIEW
Syntax
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 MariaDB 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, specify the name as db_name.view_name
when you create it:
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. They can also be expressions that use functions, constant values, operators, and so forth.
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.
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 theFROM
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 aTEMPORARY
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 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 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 MariaDB 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 MariaDB account specified as '
(the same format used in the user_name
'@'host_name
'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 legal DEFINER
user values:
- If you do not have the
SUPER
privilege, the only legaluser
value is your own account, either specified literally or by usingCURRENT_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. - Although it is possible to create a view with a nonexistent
DEFINER
account, an error occurs when the view is referenced if theSQL SECURITY
value isDEFINER
but the definer account does not exist.
For more information about view security, see , "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 , "Auditing MariaDB 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 routine, 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 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 theSQL SECURITY
characteristic isDEFINER
orINVOKER
, 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 isDEFINER
orINVOKER
. If the security characteristic isDEFINER
, the function runs with the privileges of theDEFINER
account. If the characteristic isINVOKER
, the function runs with the privileges determined by the view'sSQL 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. MariaDB has no concept of a schema "owner", so MariaDB 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 MariaDB extension to standard SQL. It affects how MariaDB 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 , "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 , "Updatable and Insertable Views".