Sign InTry Free

CREATE VIEW

The CREATE VIEW statement saves a SELECT statement as a queryable object, similar to a table. Views in TiDB are non-materialized. This means that as a view is queried, TiDB will internally rewrite the query to combine the view definition with the SQL query.

Synopsis

CreateViewStmt
CREATEOrReplaceViewAlgorithmViewDefinerViewSQLSecurityVIEWViewNameViewFieldListASCreateViewSelectOptViewCheckOption
OrReplace
ORREPLACE
ViewAlgorithm
ALGORITHM=UNDEFINEDMERGETEMPTABLE
ViewDefiner
DEFINER=Username
ViewSQLSecurity
SQLSECURITYDEFINERINVOKER
ViewName
TableName
ViewFieldList
(Identifier,)
ViewCheckOption
WITHCASCADEDLOCALCHECKOPTION

Examples

mysql> CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, c1 INT NOT NULL);
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO t1 (c1) VALUES (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.03 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> CREATE VIEW v1 AS SELECT * FROM t1 WHERE c1 > 2;
Query OK, 0 rows affected (0.11 sec)

mysql> SELECT * FROM t1;
+----+----+
| id | c1 |
+----+----+
|  1 |  1 |
|  2 |  2 |
|  3 |  3 |
|  4 |  4 |
|  5 |  5 |
+----+----+
5 rows in set (0.00 sec)

mysql> SELECT * FROM v1;
+----+----+
| id | c1 |
+----+----+
|  3 |  3 |
|  4 |  4 |
|  5 |  5 |
+----+----+
3 rows in set (0.00 sec)

mysql> INSERT INTO t1 (c1) VALUES (6);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM v1;
+----+----+
| id | c1 |
+----+----+
|  3 |  3 |
|  4 |  4 |
|  5 |  5 |
|  6 |  6 |
+----+----+
4 rows in set (0.00 sec)

mysql> INSERT INTO v1 (c1) VALUES (7);
ERROR 1105 (HY000): insert into view v1 is not supported now.

MySQL compatibility

  • Currently, any view in TiDB cannot be inserted or updated (that is, INSERT VIEW and UPDATE VIEW are not supported). WITH CHECK OPTION is only syntactically compatible but does not take effect.
  • Currently, the view in TiDB does not support ALTER VIEW, but you can use CREATE OR REPLACE instead.
  • Currently, the ALGORITHM field is only syntactically compatible in TiDB but does not take effect. TiDB currently only supports the MERGE algorithm.

See also

Download PDFRequest docs changes
Was this page helpful?
Open Source Ecosystem
TiDB
TiKV
TiSpark
Chaos Mesh
© 2022 PingCAP. All Rights Reserved.