Important

You are viewing the documentation of TiDB v6.0 (DMR). PingCAP does not provide bug fixes for v6.0. Any bugs will be fixed in future releases.

For general purposes, please use the latest LTS version of the TiDB database.

VIEWS

The VIEWS table provides information about SQL views.

USE information_schema;
DESC views;
+----------------------+--------------+------+------+---------+-------+
| Field                | Type         | Null | Key  | Default | Extra |
+----------------------+--------------+------+------+---------+-------+
| TABLE_CATALOG        | varchar(512) | NO   |      | NULL    |       |
| TABLE_SCHEMA         | varchar(64)  | NO   |      | NULL    |       |
| TABLE_NAME           | varchar(64)  | NO   |      | NULL    |       |
| VIEW_DEFINITION      | longblob     | NO   |      | NULL    |       |
| CHECK_OPTION         | varchar(8)   | NO   |      | NULL    |       |
| IS_UPDATABLE         | varchar(3)   | NO   |      | NULL    |       |
| DEFINER              | varchar(77)  | NO   |      | NULL    |       |
| SECURITY_TYPE        | varchar(7)   | NO   |      | NULL    |       |
| CHARACTER_SET_CLIENT | varchar(32)  | NO   |      | NULL    |       |
| COLLATION_CONNECTION | varchar(32)  | NO   |      | NULL    |       |
+----------------------+--------------+------+------+---------+-------+
10 rows in set (0.00 sec)
CREATE VIEW test.v1 AS SELECT 1;
SELECT * FROM views\G
*************************** 1. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: test
          TABLE_NAME: v1
     VIEW_DEFINITION: SELECT 1
        CHECK_OPTION: CASCADED
        IS_UPDATABLE: NO
             DEFINER: root@127.0.0.1
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8mb4
COLLATION_CONNECTION: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

Fields in the VIEWS table are described as follows:

  • TABLE_CATALOG: The name of the catalog to which the view belongs. This value is always def.
  • TABLE_SCHEMA: The name of the schema to which the view belongs.
  • TABLE_NAME: The view name.
  • VIEW_DEFINITION: The definition of view, which is made by the SELECT statement when the view is created.
  • CHECK_OPTION: The CHECK_OPTION value. The value options are NONE, CASCADE, and LOCAL.
  • IS_UPDATABLE: Whether UPDATE/INSERT/DELETE is applicable to the view. In TiDB, the value is always NO.
  • DEFINER: The name of the user who creates the view, which is in the format of 'user_name'@'host_name'.
  • SECURITY_TYPE: The value of SQL SECURITY. The value options are DEFINER and INVOKER.
  • CHARACTER_SET_CLIENT: The value of the character_set_client session variable when the view is created.
  • COLLATION_CONNECTION: The value of the collation_connection session variable when the view is created.
Was this page helpful?