MySQL :: MySQL 8.4 参考手册 :: 28.3.47 INFORMATION

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 28 个 INFORMATION_SCHEMA 表 / 28.3 INFORMATION_SCHEMA 总表 / 28.3.47 INFORMATION_SCHEMA VIEWS 表

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 71%

28.3.47 INFORMATION_SCHEMA VIEWS 表28.3.47 The INFORMATION_SCHEMA VIEWS Table

VIEWS 表提供数据库中视图的信息。您必须拥有 SHOW VIEW 权限才能访问此表。The VIEWS table provides information about views in databases. You must have the SHOW VIEW privilege to access this table.

VIEWS 表包含以下列：The VIEWS table has these columns:

TABLE_CATALOG
视图所属目录的名称。此值始终为 def。The name of the catalog to which the view belongs. This value is always def.
TABLE_SCHEMA
视图所属模式（数据库）的名称。The name of the schema (database) to which the view belongs.
TABLE_NAMETABLE_NAME
视图的名称。The name of the view.
VIEW_DEFINITION
提供视图定义的 SELECT 语句。该列包含了 SHOW CREATE VIEW 生成的 Create Table 列中大部分内容。省略 SELECT 之前的词，并省略 WITH CHECK OPTION。假设原始语句是：The SELECT statement that provides the definition of the view. This column has most of what you see in the Create Table column that SHOW CREATE VIEW produces. Skip the words before SELECT and skip the words WITH CHECK OPTION. Suppose that the original statement was:
```
CREATE VIEW v AS
  SELECT s2,s1 FROM t
  WHERE s1 > 5
  ORDER BY s1
  WITH CHECK OPTION;
```
然后视图定义如下：Then the view definition looks like this:
```
SELECT s2,s1 FROM t WHERE s1 > 5 ORDER BY s1
```
CHECK_OPTION
CHECK_OPTION 属性的值。值可以是 NONE、CASCADE 或 LOCAL 之一。The value of the CHECK_OPTION attribute. The value is one of NONE, CASCADE, or LOCAL.
IS_UPDATABLE
MySQL 在 CREATE VIEW 时设置一个名为视图可更新性标志的标志。如果视图允许执行 UPDATE 和 DELETE（以及类似操作），则该标志设置为 YES（真）。否则，该标志设置为 NO（假）。VIEWS 表中的 IS_UPDATABLE 列显示该标志的状态。这意味着服务器始终知道视图是否可更新。MySQL sets a flag, called the view updatability flag, at CREATE VIEW time. The flag is set to YES (true) if UPDATE and DELETE (and similar operations) are legal for the view. Otherwise, the flag is set to NO (false). The IS_UPDATABLE column in the VIEWS table displays the status of this flag. It means that the server always knows whether a view is updatable.
如果一个视图不可更新，则像 UPDATE、DELETE 和 INSERT 这样的语句是非法的，并会被拒绝。（即使视图可以更新，可能也无法向其插入数据；详情请参阅第 27.5.3 节，“可更新和可插入视图”。）If a view is not updatable, statements such UPDATE, DELETE, and INSERT are illegal and are rejected. (Even if a view is updatable, it might not be possible to insert into it; for details, refer to Section 27.5.3, “Updatable and Insertable Views”.)
DEFINERDEFINER
创建该视图的用户账户，格式为 'user_name'@'host_name'。The account of the user who created the view, in 'user_name'@'host_name' format.
SECURITY_TYPE
视图的 SQL SECURITY 特性。值可以是 DEFINER 或 INVOKER。The view SQL SECURITY characteristic. The value is one of DEFINER or INVOKER.
CHARACTER_SET_CLIENT
创建视图时 character_set_client 系统变量的会话值。The session value of the character_set_client system variable when the view was created.
COLLATION_CONNECTION
创建视图时 collation_connection 系统变量的会话值。The session value of the collation_connection system variable when the view was created.

注意事项Notes

MySQL 允许不同的 sql_mode 设置，以告知服务器应支持的 SQL 语法类型。例如，你可以使用 ANSI SQL 模式，以确保 MySQL 在查询中正确解释标准 SQL 的连接运算符双竖线（||）。如果你随后创建了一个连接项目的视图，你可能会担心将 sql_mode 设置更改为与 ANSI 不同的值可能会导致视图无效。但事实并非如此。无论你如何编写视图定义，MySQL 总是以相同的标准形式存储它。下面是一个示例，显示服务器如何将双竖线连接运算符更改为 CONCAT() 函数：MySQL permits different sql_mode settings to tell the server the type of SQL syntax to support. For example, you might use the ANSI SQL mode to ensure MySQL correctly interprets the standard SQL concatenation operator, the double bar (||), in your queries. If you then create a view that concatenates items, you might worry that changing the sql_mode setting to a value different from ANSI could cause the view to become invalid. But this is not the case. No matter how you write out a view definition, MySQL always stores it the same way, in a canonical form. Here is an example that shows how the server changes a double bar concatenation operator to a CONCAT() function:

mysql> SET sql_mode = 'ANSI';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE VIEW test.v AS SELECT 'a' || 'b' as col1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
       WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v';
+----------------------------------+
| VIEW_DEFINITION                  |
+----------------------------------+
| select concat('a','b') AS `col1` |
+----------------------------------+
1 row in set (0.00 sec)

以规范形式存储视图定义的优点是，后来对 sql_mode 值所做的更改不会影响视图的结果。然而，另一个副作用是，服务器会从定义中移除 SELECT 之前的注释。The advantage of storing a view definition in canonical form is that changes made later to the value of sql_mode do not affect the results from the view. However, an additional consequence is that comments prior to SELECT are stripped from the definition by the server.

首页上一级下一节