title: 视图

视图

TiDB 支持视图,视图是一张虚拟表,该虚拟表的结构由创建视图时的 SELECT 语句定义。使用视图一方面可以对用户只暴露安全的字段及数据,进而保证底层表的敏感字段及数据的安全。另一方面,将频繁出现的复杂查询定义为视图,可以使复杂查询更加简单便捷。

查询视图

查询一个视图和查询一张普通表类似。但是 TiDB 在真正执行查询视图时,会将视图展开成创建视图时定义的 SELECT 语句,进而执行展开后的查询语句。

查看视图的相关信息

通过以下方式,可以查看 view 相关的信息。

使用 SHOW CREATE TABLE view_nameSHOW CREATE VIEW view_name 语句

示例:

  1. show create view v;

使用该语句可以查看 view 对应的创建语句,及创建 view 时对应的 character_set_clientcollation_connection 系统变量值。

  1. +------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
  2. | View | Create View | character_set_client | collation_connection |
  3. +------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
  4. | v | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`127.0.0.1` SQL SECURITY DEFINER VIEW `v` (`a`) AS SELECT `s`.`a` FROM `test`.`t` LEFT JOIN `test`.`s` ON `t`.`a`=`s`.`a` | utf8 | utf8_general_ci |
  5. +------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
  6. 1 row in set (0.00 sec)

查询 INFORMATION_SCHEMA.VIEWS

示例:

  1. select * from information_schema.views;

通过查询该表可以查看 view 的相关元信息,如 TABLE_CATALOGTABLE_SCHEMATABLE_NAMEVIEW_DEFINITIONCHECK_OPTIONIS_UPDATABLEDEFINERSECURITY_TYPECHARACTER_SET_CLIENTCOLLATION_CONNECTION 等。

  1. +---------------+--------------+------------+------------------------------------------------------------------------+--------------+--------------+----------------+---------------+----------------------+----------------------+
  2. | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | VIEW_DEFINITION | CHECK_OPTION | IS_UPDATABLE | DEFINER | SECURITY_TYPE | CHARACTER_SET_CLIENT | COLLATION_CONNECTION |
  3. +---------------+--------------+------------+------------------------------------------------------------------------+--------------+--------------+----------------+---------------+----------------------+----------------------+
  4. | def | test | v | SELECT `s`.`a` FROM `test`.`t` LEFT JOIN `test`.`s` ON `t`.`a`=`s`.`a` | CASCADED | NO | root@127.0.0.1 | DEFINER | utf8 | utf8_general_ci |
  5. +---------------+--------------+------------+------------------------------------------------------------------------+--------------+--------------+----------------+---------------+----------------------+----------------------+
  6. 1 row in set (0.00 sec)

查询 HTTP API

示例:

  1. curl http://127.0.0.1:10080/schema/test/v

通过访问 http://{TiDBIP}:10080/schema/{db}/{view} 可以得到对应 view 的所有元信息。

  1. {
  2. "id": 122,
  3. "name": {
  4. "O": "v",
  5. "L": "v"
  6. },
  7. "charset": "utf8",
  8. "collate": "utf8_general_ci",
  9. "cols": [
  10. {
  11. "id": 1,
  12. "name": {
  13. "O": "a",
  14. "L": "a"
  15. },
  16. "offset": 0,
  17. "origin_default": null,
  18. "default": null,
  19. "default_bit": null,
  20. "default_is_expr": false,
  21. "generated_expr_string": "",
  22. "generated_stored": false,
  23. "dependences": null,
  24. "type": {
  25. "Tp": 0,
  26. "Flag": 0,
  27. "Flen": 0,
  28. "Decimal": 0,
  29. "Charset": "",
  30. "Collate": "",
  31. "Elems": null
  32. },
  33. "state": 5,
  34. "comment": "",
  35. "hidden": false,
  36. "version": 0
  37. }
  38. ],
  39. "index_info": null,
  40. "fk_info": null,
  41. "state": 5,
  42. "pk_is_handle": false,
  43. "is_common_handle": false,
  44. "comment": "",
  45. "auto_inc_id": 0,
  46. "auto_id_cache": 0,
  47. "auto_rand_id": 0,
  48. "max_col_id": 1,
  49. "max_idx_id": 0,
  50. "update_timestamp": 416801600091455490,
  51. "ShardRowIDBits": 0,
  52. "max_shard_row_id_bits": 0,
  53. "auto_random_bits": 0,
  54. "pre_split_regions": 0,
  55. "partition": null,
  56. "compression": "",
  57. "view": {
  58. "view_algorithm": 0,
  59. "view_definer": {
  60. "Username": "root",
  61. "Hostname": "127.0.0.1",
  62. "CurrentUser": false,
  63. "AuthUsername": "root",
  64. "AuthHostname": "%"
  65. },
  66. "view_security": 0,
  67. "view_select": "SELECT `s`.`a` FROM `test`.`t` LEFT JOIN `test`.`s` ON `t`.`a`=`s`.`a`",
  68. "view_checkoption": 1,
  69. "view_cols": null
  70. },
  71. "sequence": null,
  72. "Lock": null,
  73. "version": 3,
  74. "tiflash_replica": null
  75. }

示例

以下例子将创建一个视图,并在该视图上进行查询,最后删除该视图。

  1. create table t(a int, b int);
  1. Query OK, 0 rows affected (0.01 sec)
  1. insert into t values(1, 1),(2,2),(3,3);
  1. Query OK, 3 rows affected (0.00 sec)
  2. Records: 3 Duplicates: 0 Warnings: 0
  1. create table s(a int);
  1. Query OK, 0 rows affected (0.01 sec)
  1. insert into s values(2),(3);
  1. Query OK, 2 rows affected (0.01 sec)
  2. Records: 2 Duplicates: 0 Warnings: 0
  1. create view v as select s.a from t left join s on t.a = s.a;
  1. Query OK, 0 rows affected (0.01 sec)
  1. select * from v;
  1. +------+
  2. | a |
  3. +------+
  4. | NULL |
  5. | 2 |
  6. | 3 |
  7. +------+
  8. 3 rows in set (0.00 sec)
  1. drop view v;
  1. Query OK, 0 rows affected (0.02 sec)

局限性

目前 TiDB 中的视图有以下局限性:

  • 不支持物化视图。
  • TiDB 中视图为只读视图,不支持对视图进行 UPDATEINSERTDELETETRUNCATE 等写入操作。
  • 对已创建的视图仅支持 DROP 的 DDL 操作,即 DROP [VIEW | TABLE]

扩展阅读