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

CREATE VIEW - 图1

OrReplace

CREATE VIEW - 图2

ViewAlgorithm

CREATE VIEW - 图3

ViewDefiner

CREATE VIEW - 图4

ViewSQLSecurity

CREATE VIEW - 图5

ViewName

CREATE VIEW - 图6

ViewFieldList

CREATE VIEW - 图7

ViewCheckOption

CREATE VIEW - 图8

  1. CreateViewStmt ::=
  2.     'CREATE' OrReplace ViewAlgorithm ViewDefiner ViewSQLSecurity 'VIEW' ViewName ViewFieldList 'AS' CreateViewSelectOpt ViewCheckOption
  4. OrReplace ::=
  5.     ( 'OR' 'REPLACE' )?
  7. ViewAlgorithm ::=
  8.     ( 'ALGORITHM' '=' ( 'UNDEFINED' | 'MERGE' | 'TEMPTABLE' ) )?
  10. ViewDefiner ::=
  11.     ( 'DEFINER' '=' Username )?
  13. ViewSQLSecurity ::=
  14.     ( 'SQL' 'SECURITY' ( 'DEFINER' | 'INVOKER' ) )?
  16. ViewName ::= TableName
  18. ViewFieldList ::=
  19.     ( '(' Identifier ( ',' Identifier )* ')' )?
  21. ViewCheckOption ::=
  22.     ( 'WITH' ( 'CASCADED' | 'LOCAL' ) 'CHECK' 'OPTION' )?

Examples

  1. mysql> CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, c1 INT NOT NULL);
  2. Query OK, 0 rows affected (0.11 sec)
  4. mysql> INSERT INTO t1 (c1) VALUES (1),(2),(3),(4),(5);
  5. Query OK, 5 rows affected (0.03 sec)
  6. Records: 5  Duplicates: 0  Warnings: 0
  8. mysql> CREATE VIEW v1 AS SELECT * FROM t1 WHERE c1 > 2;
  9. Query OK, 0 rows affected (0.11 sec)
  11. mysql> SELECT * FROM t1;
  12. +----+----+
  13. | id | c1 |
  14. +----+----+
  15. |  1 |  1 |
  16. |  2 |  2 |
  17. |  3 |  3 |
  18. |  4 |  4 |
  19. |  5 |  5 |
  20. +----+----+
  21. 5 rows in set (0.00 sec)
  23. mysql> SELECT * FROM v1;
  24. +----+----+
  25. | id | c1 |
  26. +----+----+
  27. |  3 |  3 |
  28. |  4 |  4 |
  29. |  5 |  5 |
  30. +----+----+
  31. 3 rows in set (0.00 sec)
  33. mysql> INSERT INTO t1 (c1) VALUES (6);
  34. Query OK, 1 row affected (0.01 sec)
  36. mysql> SELECT * FROM v1;
  37. +----+----+
  38. | id | c1 |
  39. +----+----+
  40. |  3 |  3 |
  41. |  4 |  4 |
  42. |  5 |  5 |
  43. |  6 |  6 |
  44. +----+----+
  45. 4 rows in set (0.00 sec)
  47. mysql> INSERT INTO v1 (c1) VALUES (7);
  48. 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