Movatterモバイル変換

CREATE    [OR REPLACE]    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]    [DEFINER =user]    [SQL SECURITY { DEFINER | INVOKER }]    VIEWview_name [(column_list)]    ASselect_statement    [WITH [CASCADED | LOCAL] CHECK OPTION]CREATE    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]    [DEFINER =user]    [SQL SECURITY { DEFINER | INVOKER }]    [IF NOT EXISTS] VIEWview_name [(column_list)]    ASselect_statement    [WITH [CASCADED | LOCAL] CHECK OPTION]

TheCREATE VIEW statement creates a new view, or replaces an existing view if theOR REPLACE clause is given. If the view does not exist,CREATE OR REPLACE VIEW is the same asCREATE VIEW. If the view does exist,CREATE OR REPLACE VIEW replaces it.

Theselect_statement is aSELECT statement that provides the definition of the view. (Selecting from the view selects, in effect, using theSELECT statement.) Theselect_statement can select from base tables or from other views. TheSELECT statement can use aVALUES statement as its source, or can be replaced with aTABLE statement, as withCREATE TABLE ... SELECT.

IF NOT EXISTS causes the view to be created if it does not already exist. If the view already exists andIF NOT EXISTS is specified, the statement is succeeds with a warning rather than an error; in this case, the view definition is not changed. For example:

mysql> CREATE VIEW v1 AS SELECT c1, c3 FROM t1;Query OK, 0 rows affected (0.01 sec)mysql> CREATE VIEW v1 AS SELECT c1, c3 FROM t1;ERROR 1050 (42S01): Table 'v1' already existsmysql> CREATE VIEW IF NOT EXISTS v1 AS SELECT c1, c3 FROM t1;Query OK, 0 rows affected, 1 warning (0.01 sec)mysql> SHOW WARNINGS;+-------+------+---------------------------+| Level | Code | Message                   |+-------+------+---------------------------+| Note  | 1050 | Table 'v1' already exists |+-------+------+---------------------------+1 row in set (0.00 sec)mysql> SHOW CREATE VIEW v1\G*************************** 1. row ***************************                View: v1         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`vuser`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`c1` AS `c1`,`t1`.`c3` AS `c3` from `t1`character_set_client: utf8mb4collation_connection: utf8mb4_0900_ai_ci1 row in set (0.00 sec)

IF NOT EXISTS andOR REPLACE are mutually exclusive and cannot be used together in the sameCREATE VIEW statement. Attempting to do so causes the statement to be rejected with a syntax error.

For information about restrictions on view use, seeSection 27.10, “Restrictions on 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 asSELECT * on a table, new columns added to the table later do not become part of the view, and columns dropped from the table result in an error when selecting from the view.

TheALGORITHM clause affects how MySQL processes the view. TheDEFINER andSQL SECURITY clauses specify the security context to be used when checking access privileges at view invocation time. TheWITH 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.

TheCREATE VIEW statement requires theCREATE VIEW privilege for the view, and some privilege for each column selected by theSELECT statement. For columns used elsewhere in theSELECT statement, you must have theSELECT privilege. If theOR REPLACE clause is present, you must also have theDROP privilege for the view. If theDEFINER clause is present, the privileges required depend on theuser value, as discussed inSection 27.7, “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, usedb_name.view_name syntax to qualify the view name with the database name:

CREATE VIEW test.v AS SELECT * FROM t;

Unqualified table or view names in theSELECT 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 theSELECT 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 theSELECT statement are used for the view column names. To define explicit names for the view columns, specify the optionalcolumn_list clause as a list of comma-separated identifiers. The number of names incolumn_list must be the same as the number of columns retrieved by theSELECT statement.

A view can be created from many kinds ofSELECT statements. It can refer to base tables or other views. It can use joins,UNION, and subqueries. TheSELECT need not even refer to any tables:

CREATE VIEW v_today (today) AS SELECT CURRENT_DATE;

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:

ORDER BY is permitted in a view definition, but it is ignored if you select from a view using a statement that has its ownORDER 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 aLIMIT clause, and you select from the view using a statement that has its ownLIMIT clause, it is undefined which limit applies. This same principle applies to options such asALL,DISTINCT, orSQL_SMALL_RESULT that follow theSELECT keyword, and to clauses such asINTO,FOR UPDATE,FOR SHARE,LOCK IN SHARE MODE, andPROCEDURE.

The results obtained from a view may be affected if you change the query processing environment by changing system variables:

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)

TheDEFINER andSQL 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 validSQL SECURITY characteristic values areDEFINER (the default) andINVOKER. These indicate that the required privileges must be held by the user who defined or invoked the view, respectively.

If theDEFINER clause is present, theuser value should be a MySQL account specified as'user_name'@'host_name',CURRENT_USER, orCURRENT_USER(). The permitteduser values depend on the privileges you hold, as discussed inSection 27.7, “Stored Object Access Control”. Also see that section for additional information about view security.

If theDEFINER clause is omitted, the default definer is the user who executes theCREATE VIEW statement. This is the same as specifyingDEFINER = CURRENT_USER explicitly.

Within a view definition, theCURRENT_USER function returns the view'sDEFINER value by default. For views defined with theSQL SECURITY INVOKER characteristic,CURRENT_USER returns the account for the view's invoker. For information about user auditing within views, seeSection 8.2.23, “SQL-Based Account Activity Auditing”.

Within a stored routine that is defined with theSQL SECURITY DEFINER characteristic,CURRENT_USER returns the routine'sDEFINER value. This also affects a view defined within such a routine, if the view definition contains aDEFINER value ofCURRENT_USER.

MySQL checks view privileges like this:

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 functionf():

CREATE VIEW v AS SELECT * FROM t WHERE t.id = f(t.name);

Suppose thatf() contains a statement such as this:

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

The privileges required for executing statements withinf() need to be checked whenf() executes. This might mean that privileges are needed forp1() orp2(), depending on the execution path withinf(). Those privileges must be checked at runtime, and the user who must possess the privileges is determined by theSQL SECURITY values of the viewv and the functionf().

TheDEFINER andSQL SECURITY clauses for views are extensions to standard SQL. In standard SQL, views are handled using the rules forSQL 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. TheDEFINER 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 defaultDEFINER value is the account of the view creator.

The optionalALGORITHM clause is a MySQL extension to standard SQL. It affects how MySQL processes the view.ALGORITHM takes three values:MERGE,TEMPTABLE, orUNDEFINED. For more information, seeSection 27.6.2, “View Processing Algorithms”, as well asSection 10.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 asUPDATE,DELETE, orINSERT 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 isDEFAULT. For information about generated columns, seeSection 15.1.20.8, “CREATE TABLE and Generated Columns”.

TheWITH CHECK OPTION clause can be given for an updatable view to prevent inserts or updates to rows except those for which theWHERE clause in theselect_statement is true.

In aWITH CHECK OPTION clause for an updatable view, theLOCAL andCASCADED keywords determine the scope of check testing when the view is defined in terms of another view. TheLOCAL keyword restricts theCHECK 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 isCASCADED.

For more information about updatable views and theWITH CHECK OPTION clause, seeSection 27.6.3, “Updatable and Insertable Views”, andSection 27.6.4, “The View WITH CHECK OPTION Clause”.