CREATE VIEW

Sintassi

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { utente | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW nome_vista [(elenco_colonne)]
    AS istruzione_select
    [WITH [CASCADED | LOCAL] CHECK OPTION]

Spiegazione

L'istruzione CREATE VIEW crea una nuova vista, o ne rimpiazza una esistente se la clausola OR REPLACE è presente. Se la vista non esiste, CREATE OR REPLACE VIEW equivale a CREATE VIEW. Se invece esiste, CREATE OR REPLACE VIEW equivale ad ALTER VIEW.

istruzione_select è un'istruzione di tipo SELECT che viene usata come definizione per la vista: quando si selezionano dei valori da essa, sarà come selezionarli dall'istruzione SELECT. istruzione_select può leggere i dati da tabelle fisiche o da altre viste.

La definizione è "congelata" al momento della creazione della vista, e non sarà modificata se si cambierà la struttura delle tabelle sottostanti. Per esempio, se una vista è definita come una SELECT * su una certa tabella, e successivamente a questa tabella verranno aggiunte nuove colonne, queste non faranno parte della vista.

La clausola ALGORITHM influenza il modo in cui MariaDB elabora la vista. Le clausole DEFINER e SQL SECURITY controllano il modo in cui verranno verificati i privilegi per gli accessi alla vista. La clausola WITH CHECK OPTION permette di aggiungere o modificare solo i dati che fanno parte della vista. Queste clausole verranno descritte più avanti nella pagina.

L'istruzione CREATE VIEW richiede il privilegio CREATE VIEW e i privilegi per le colonne selezionate dall'istruzione SELECT. Anche per le altre colonne nominate dalla SELECT occorre il privilegio SELECT. Se la clausola OR REPLACE è specificata, occorre anche il privilegio DROP sulla vista.

Le viste appartengono ai rispettivi database. Per default, le nuove viste vengono create nel database selezionato. Per crearle in un database differente, si specifichi il nome usando la sintassi nome_db.nome_vista.

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

Le tabelle fisiche e le viste condividono lo stesso namespace, perciò non è possibile che all'interno di uno stesso database ci siano una tabella e una vista con lo stesso nome.

I nomi delle colonne delle viste devono essere univoci, così come accade per le tabelle. Per default, i nomi delle colonne selezionate dalla SELECT vengono usati anche nella vista. Per definire esplicitamente dei nomi differenti da usare nella vista, si può utilizzare la clausola opzionale elenco_colonne, separando i nomi con una virgola. Il numero di nomi presenti in elenco_colonne deve corrispondere al numero di colonne estratte dalla SELECT.

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.

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. They were addded in MySQL 5.1.2. 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 'user_name'@'host_name' format (the same format used in the 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 as of MySQL 5.1.12. For older versions, and 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 http://dev.mysql.com/doc/refman/5.1/en/account-activity-auditing.html.

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.

As of MySQL 5.1.2 (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 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.

Prior to MySQL 5.1.2 (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 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 INVOKER.

If you invoke a view that was created before MySQL 5.1.2, it is treated as though it was created with a SQL SECURITY DEFINER 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. 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 http://dev.mysql.com/doc/refman/5.1/en/view-algorithms.html.

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 non-updatable.

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 http://dev.mysql.com/doc/refman/5.1/en/view-updatability.html.