Rechercher dans le manuel MySQL
13.1.23 CREATE VIEW Syntax
- 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
replaces it.
For information about restrictions on view use, see Section C.5, “Restrictions on Views”.
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 and
is not affected by subsequent changes to the definitions of the
underlying tables. 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, and columns dropped
from the table will result in an error when selecting from 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. If the DEFINER
clause is present, the
privileges required depend on the user
value, as discussed in Section 24.6, “Stored Object Access Control”.
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:
Unqualified table or view names in the
SELECT
statement are also
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.
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, specify the optional
column_list
clause 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.
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:
- +------+-------+-------+
- +------+-------+-------+
- | 3 | 50 | 150 |
- +------+-------+-------+
A view definition is subject to the following restrictions:
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. If, after the view has been created, a table or view that the definition refers to is dropped, 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.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
,
FOR SHARE
, LOCK IN SHARE
MODE
, and PROCEDURE
.
The results obtained from a view may be affected if you change the query processing environment by changing system variables:
- Query OK, 0 rows affected (0.01 sec)
- Query OK, 0 rows affected (0.00 sec)
- +-------+
- | mycol |
- +-------+
- | mycol |
- +-------+
- Query OK, 0 rows affected (0.00 sec)
- +-------+
- | mycol |
- +-------+
- | abc |
- +-------+
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 the DEFINER
clause is present, the
user
value should be a MySQL account
specified as
'
,
user_name
'@'host_name
'CURRENT_USER
, or
CURRENT_USER()
. The permitted
user
values depend on the privileges
you hold, as discussed in
Section 24.6, “Stored Object Access Control”. Also see that section
for additional information about view security.
If the DEFINER
clause is omitted, the default
definer is the user who executes the CREATE
VIEW
statement. This is the same as specifying
DEFINER = CURRENT_USER
explicitly.
Within a view definition, the
CURRENT_USER
function 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.2.22, “SQL-Based 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 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()
:
Suppose that f()
contains a statement such as
this:
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
. For more information, see
Section 24.5.2, “View Processing Algorithms”, as well as
Section 8.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions
with Merging or Materialization”.
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.
A generated column in a view is considered updatable because it is
possible to assign to it. However, if such a column is updated
explicitly, the only permitted value is
DEFAULT
. For information about generated
columns, see Section 13.1.20.9, “CREATE TABLE and Generated Columns”.
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 24.5.3, “Updatable and Insertable Views”, and
Section 24.5.4, “The View WITH CHECK OPTION Clause”.
Deutsche Übersetzung
Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.
Vielen Dank im Voraus.
Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-create-view.html
Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.
Referenzen
Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.