Views

This document describes how to use views in TiDB.

Overview

TiDB supports views. A view acts as a virtual table, whose schema is defined by the SELECT statement that creates the view.

You can create views to expose only safe fields and data to users, which ensures the security of sensitive fields and data in the underlying tables.
You can create views for complex queries that are frequently used to make complex queries easier and more convenient.

Create a view

In TiDB, a complex query can be defined as a view with the CREATE VIEW statement. The syntax is as follows:

CREATE VIEW view_name AS query;

Note that you cannot create a view with the same name as an existing view or table.

For example, the multi-table join query gets a list of books with average ratings by joining the books table and the ratings table through a JOIN statement.

For the convenience of subsequent queries, you can define the query as a view using the following statement:

CREATE VIEW book_with_ratings AS
SELECT b.id AS book_id, ANY_VALUE(b.title) AS book_title, AVG(r.score) AS average_score
FROM books b
LEFT JOIN ratings r ON b.id = r.book_id
GROUP BY b.id;

Query views

Once a view is created, you can use the SELECT statement to query the view just like a normal table.

SELECT * FROM book_with_ratings LIMIT 10;

When TiDB queries a view, it queries the SELECT statement associated with the view.

Update views

Currently, the view in TiDB does not support the ALTER VIEW view_name AS query;, you can "update" a view in the following two ways:

Delete the old view with the DROP VIEW view_name; statement, and then update the view by creating a new view with the CREATE VIEW view_name AS query; statement.
Use the CREATE OR REPLACE VIEW view_name AS query; statement to overwrite an existing view with the same name.

CREATE OR REPLACE VIEW book_with_ratings AS
SELECT b.id AS book_id, ANY_VALUE(b.title), ANY_VALUE(b.published_at) AS book_title, AVG(r.score) AS average_score
FROM books b
LEFT JOIN ratings r ON b.id = r.book_id
GROUP BY b.id;

Using the `SHOW CREATE TABLE|VIEW view_name` statement

SHOW CREATE VIEW book_with_ratings\G

The result is as follows:

*************************** 1. row ***************************
                View: book_with_ratings
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `book_with_ratings` (`book_id`, `ANY_VALUE(b.title)`, `book_title`, `average_score`) AS SELECT `b`.`id` AS `book_id`,ANY_VALUE(`b`.`title`) AS `ANY_VALUE(b.title)`,ANY_VALUE(`b`.`published_at`) AS `book_title`,AVG(`r`.`score`) AS `average_score` FROM `bookshop`.`books` AS `b` LEFT JOIN `bookshop`.`ratings` AS `r` ON `b`.`id`=`r`.`book_id` GROUP BY `b`.`id`
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
1 row in set (0.00 sec)

Query the `INFORMATION_SCHEMA.VIEWS` table

SELECT * FROM information_schema.views WHERE TABLE_NAME = 'book_with_ratings'\G

The result is as follows:

*************************** 1. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: bookshop
          TABLE_NAME: book_with_ratings
     VIEW_DEFINITION: SELECT `b`.`id` AS `book_id`,ANY_VALUE(`b`.`title`) AS `ANY_VALUE(b.title)`,ANY_VALUE(`b`.`published_at`) AS `book_title`,AVG(`r`.`score`) AS `average_score` FROM `bookshop`.`books` AS `b` LEFT JOIN `bookshop`.`ratings` AS `r` ON `b`.`id`=`r`.`book_id` GROUP BY `b`.`id`
        CHECK_OPTION: CASCADED
        IS_UPDATABLE: NO
             DEFINER: root@%
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8mb4
COLLATION_CONNECTION: utf8mb4_general_ci
1 row in set (0.00 sec)

Drop views

Use the DROP VIEW view_name; statement to drop a view.

DROP VIEW book_with_ratings;

Limitation

For limitations of views in TiDB, see Limitations of Views.

Was this page helpful?

Download PDF Request docs changes Ask questions on Discord

Playground

New

One-stop & interactive experience of TiDB's capabilities WITHOUT registration.