Views
TiDB supports views. A view acts as a virtual table and its table schema is defined by the SELECT statement when you create the view. Using views has the following benefits:
- Exposing only safe fields and data to users to ensure security of sensitive fields and data stored in the underlying table
- Defining complex queries that frequently appear as views to make complex queries easier and more convenient
Query views
Querying a view is similar to querying an ordinary table. However, when TiDB queries a view, it actually queries the SELECT statement associated with the view.
Examples
The following example creates a view, queries this view, and delete this view:
tidb> create table t(a int, b int);
Query OK, 0 rows affected (0.01 sec)
tidb> insert into t values(1, 1),(2,2),(3,3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
tidb> create table s(a int);
Query OK, 0 rows affected (0.01 sec)
tidb> insert into s values(2),(3);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
tidb> create view v as select s.a from t left join s on t.a = s.a;
Query OK, 0 rows affected (0.01 sec)
tidb> select * from v;
+------+
| a |
+------+
| NULL |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
tidb> drop view v;
Query OK, 0 rows affected (0.02 sec)
Limitations
Currently, the TiDB view has the following limitations:
- TiDB does not support materialized views.
- TiDB views are read-only and do not support write operations like
UPDATE,INSERT,DELETEand so on. - For created views, TiDB only support the
DROP [VIEW | TABLE]DDL operation.