CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = user]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

CREATE VIEW 语句创建新视图，或替换现有视图，前提是给出 OR REPLACE 子句。如果视图不存在，CREATE OR REPLACE VIEW 与 CREATE VIEW。如果视图存在，CREATE OR REPLACE VIEWThe 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.

有关视图使用限制的信息，请参见 第 27.9 节，“视图限制”。For information about restrictions on view use, see Section 27.9, “Restrictions on Views”.

该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 from other views. The SELECT statement can use a VALUES statement as its source, or can be replaced with a TABLE statement, as with CREATE TABLE ... SELECT.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 from other views. The SELECT statement can use a VALUES statement as its source, or can be replaced with a TABLE statement, as with CREATE TABLE ... SELECT.

视图定义为创建时的 “frozen”，不受底层表定义后续更改的影响。例如，如果视图定义为 SELECT *表上，后续添加到表中的列不会成为视图的一部分，且从表中删除的列在选择视图时会导致错误。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 result in an error when selecting from the view.

ALGORITHM 子句影响 MySQL 处理视图的方式。DEFINER 和 SQL SECURITY 子句指定了在视图调用时检查访问权限时所用的安全上下文。WITH CHECK OPTION句可用于限制视图引用的表格中对行的插入或更新。这些子句将在本节后面详细描述。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.

该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子句，所需的权限取决于user值，如第 27.6 节，“存储对象访问控制”中所讨论的.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 27.6, “Stored Object Access Control”.

当引用视图时，权限检查会如本节后文所述。When a view is referenced, privilege checking occurs as described later in this section.

视图属于数据库。默认情况下，在默认数据库中创建新视图。要在给定数据库中显式创建视图，可以使用 db_name.view_name 语法来用数据库名称来补述视图名称：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:

CREATE VIEW test.v AS SELECT * FROM t;

Unqualified table 或视图名称在 SELECT 语句也可根据默认数据库进行解释。视图可以通过用相应的数据库名称对表或视图名进行限定，来引用其他数据库中的表或视图。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.

通过 SELECT 语句检索的列可以是简单引用表列，也可以是使用函数、常数值、运算符等的表达式。Columns retrieved by the SELECT statement can be simple references to table columns, or expressions that use functions, constant values, operators, and so forth.

视图必须具有唯一的列名，没有重复，就像基表一样。默认情况下，通过SELECT语句检索的列名用于视图列名。要为视图列定义显式名称，请指定可选的column_list子句作为逗号分隔标识符的列表。列表中的名称数量必须与column_list语句检索的列数相同。SELECT在定义具有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.

视图可以由多种 SELECT 语句”创建。它可以引用基表或其他视图。它可以使用连接，UNION，以及子查询。SELECT 甚至不必引用任何表：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:

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，但如果使用自己的 ORDER BY 语句从视图中选择数据，则会被忽略。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.

对于定义中的其他选项或子句，它们会添加到引用视图的语句的选项或子句中，但其效果未定义。例如，如果视图定义包含 LIMIT 子句，而您使用自己的 LIMIT 子句从视图中选择数据，则不确定哪个限制适用。此原则同样适用于 SELECT 关键字后的选项，如 ALL、DISTINCT 或 SQL_SMALL_RESULT，以及如 INTO、FOR UPDATE、FOR SHARE、LOCK IN SHARE MODE 和 PROCEDURE 的子句。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:

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)

DEFINER 和 SQL SECURITY 子句确定执行引用视图的语句时，用于检查视图访问权限的 MySQL 帐户。有效的 SQL SECURITY 特性值为 DEFINER（默认）和 INVOKER。这些表示所需权限必须分别由定义视图的用户或调用视图的用户持有。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.

如果存在 DEFINER 子句，user 的值应为 MySQL 帐号，格式为 'user_name'@'host_name'、CURRENT_USER 或 CURRENT_USER()。允许的 user 值取决于您持有的权限，如 第 27.6 节，“存储对象访问控制” 中所述。有关视图安全性的更多信息，也请参阅该节。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 27.6, “Stored Object Access Control”. Also see that section for additional information about view security.

如果省略 DEFINER 子句，默认的定义者是执行 CREATE VIEW 语句的用户。这与显式指定 DEFINER = CURRENT_USER 是相同的。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.

在视图定义中，CURRENT_USER 函数默认返回视图的 DEFINER 值。对于使用 SQL SECURITY INVOKER 特性的视图，CURRENT_USER 返回视图调用者的帐户。有关在视图中进行用户审计的信息，请参阅 第 8.2.23 节，“基于 SQL 的帐户活动审计”。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 8.2.23, “SQL-Based Account Activity Auditing”.

特性的存储例程中，SQL SECURITY DEFINER返回例程的CURRENT_USER值。如果视图定义包含DEFINER值，这也会影响在此类例程中定义的视图。DEFINER删除表中名为CURRENT_USER.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 检查视图权限的方法如下：MySQL checks view privileges like this:

示例：视图可能依赖于存储函数，而该函数可能调用其他存储过程。例如，下面的视图调用了一个存储函数f()：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);

假设f()包含如下语句：Suppose that f() contains a statement such as this:

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

执行f()中的语句所需的权限必须在执行f()时进行检查。这可能意味着执行路径中需要p1()或p2()的权限，具体取决于f()中的执行路径。必须在运行时检查这些权限，而必须拥有这些权限的用户由视图v和函数f()的SQL SECURITY值确定。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().

该DEFINER以及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 “主人”, 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 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.

可选的ALGORITHM子句是 MySQL 对标准 SQL 的扩展。它影响 MySQL 处理视图的方式。ALGORITHM有三个值：MERGE、TEMPTABLE或UNDEFINED。更多信息，请参见第 27.5.2 节，“视图处理算法”，以及第 10.2.2.4 节，“通过合并或物化优化派生表、视图引用和公共表表达式”。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 27.5.2, “View Processing Algorithms”, as well as Section 10.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization”.

有些视图是可更新的。也就是说，你可以将它们用于诸如 UPDATE DELETE，或 INSERT”来更新底层表的内容。要让视图可更新，视图中的行与底层表中的行之间必须有一对一的关系。还有一些其他结构使视图不可更新。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.

视图中的生成列被视为可更新，因为可以赋入该列。然而，如果该列被显式更新，唯一允许的值是 DEFAULT有关生成列的信息，请参见 第 15.1.20.8 节，“创建 TABLE 和生成列”。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 15.1.20.8, “CREATE TABLE and Generated Columns”.

WITH CHECK OPTION句可用于可更新视图，以防止对行的插入或更新，除非在 类中 WHERE 子句select_statement为真的情况下。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.

在可更新视图的 WITH CHECK OPTION句中，LOCAL 和 CASCADED 关键字决定了当视图以另一个视图定义时，检查测试的范围。LOCAL 关键字限制 CHECK OPTION仅限于定义的视图。CASCADED 也会导致对底层视图的检查进行评估。当这两个关键词都未给出时，默认为 CASCADEDIn 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.

关于可更新视图和 WITH CHECK OPTION 子句的更多信息，请参见 第 27.5.3 节，“可更新和可插入视图”，以及 第 27.5.4 节，“带检查选项的视图子句”。For more information about updatable views and the WITH CHECK OPTION clause, see Section 27.5.3, “Updatable and Insertable Views”, and Section 27.5.4, “The View WITH CHECK OPTION Clause”.