2 Star 1 Fork 0

平凯星辰(北京)科技有限公司/docs-cn

加入 Gitee
与超过 1200万 开发者一起发现、参与优秀开源项目,私有仓库也完全免费 :)
免费加入
文件
.github
.vaunt
benchmark
best-practices
br
clinic
config-templates
dashboard
develop
dm
faq
functions-and-operators
information-schema
media
mysql-schema
performance-schema
releases
resources
scripts
sql-statements
storage-engine
sync-diff-inspector
sys-schema
templates
ticdc
tidb-lightning
tiflash
tiproxy
tiup
.gitignore
.markdownlint.yaml
.zhlint.yaml
CONTRIBUTING.md
LICENSE
OWNERS
OWNERS_ALIASES
README.md
TOC.md
_docHome.md
_index.md
accelerated-table-creation.md
agg-distinct-optimization.md
alert-rules.md
analyze-slow-queries.md
as-of-timestamp.md
auto-increment.md
auto-random.md
backup-and-restore-using-dumpling-lightning.md
basic-features.md
basic-sql-operations.md
best-practices-for-security-configuration.md
best-practices-on-public-cloud.md
binary-package.md
blocklist-control-plan.md
cached-tables.md
certificate-authentication.md
character-set-and-collation.md
character-set-gbk.md
check-before-deployment.md
choose-index.md
clustered-indexes.md
column-pruning.md
command-line-flags-for-pd-configuration.md
command-line-flags-for-scheduling-configuration.md
command-line-flags-for-tidb-configuration.md
command-line-flags-for-tikv-configuration.md
command-line-flags-for-tso-configuration.md
comment-syntax.md
configure-load-base-split.md
configure-memory-usage.md
configure-placement-rules.md
configure-store-limit.md
configure-time-zone.md
constraints.md
control-execution-plan.md
coprocessor-cache.md
correlated-subquery-optimization.md
cost-model.md
credits.md
daily-check.md
data-type-date-and-time.md
data-type-default-values.md
data-type-json.md
data-type-numeric.md
data-type-overview.md
data-type-string.md
ddl-introduction.md
deploy-monitoring-services.md
derive-topn-from-window.md
download-ecosystem-tools.md
dr-backup-restore.md
dr-multi-replica.md
dr-secondary-cluster.md
dr-solution-introduction.md
dumpling-overview.md
dynamic-config.md
ecosystem-tool-user-case.md
ecosystem-tool-user-guide.md
enable-disk-spill-encrypt.md
enable-tls-between-clients-and-servers.md
enable-tls-between-components.md
encryption-at-rest.md
error-codes.md
explain-aggregation.md
explain-index-merge.md
explain-indexes.md
explain-joins.md
explain-mpp.md
explain-overview.md
explain-partitions.md
explain-subqueries.md
explain-views.md
explain-walkthrough.md
explore-htap.md
exporting-grafana-snapshots.md
expression-syntax.md
extended-statistics.md
external-storage-uri.md
filter-binlog-event.md
filter-dml-event.md
follower-read.md
foreign-key.md
garbage-collection-configuration.md
garbage-collection-overview.md
generate-self-signed-certificates.md
generated-columns.md
geo-distributed-deployment-topology.md
get-started-with-tidb-lightning.md
glossary.md
grafana-overview-dashboard.md
grafana-pd-dashboard.md
grafana-performance-overview-dashboard.md
grafana-resource-control-dashboard.md
grafana-tidb-dashboard.md
grafana-tikv-dashboard.md
hardware-and-software-requirements.md
hybrid-deployment-topology.md
identify-expensive-queries.md
identify-slow-queries.md
integration-overview.md
join-reorder.md
keywords.md
latency-breakdown.md
literal-values.md
log-redaction.md
maintain-tidb-using-tiup.md
max-min-eliminate.md
metadata-lock.md
metrics-schema.md
migrate-aurora-to-tidb.md
migrate-from-csv-files-to-tidb.md
migrate-from-mariadb.md
migrate-from-parquet-files-to-tidb.md
migrate-from-sql-files-to-tidb.md
migrate-from-tidb-to-mysql.md
migrate-from-tidb-to-tidb.md
migrate-from-vitess.md
migrate-large-mysql-shards-to-tidb.md
migrate-large-mysql-to-tidb.md
migrate-small-mysql-shards-to-tidb.md
migrate-small-mysql-to-tidb.md
migrate-with-more-columns-downstream.md
migrate-with-pt-ghost.md
migration-overview.md
migration-tools.md
minimal-deployment-topology.md
multi-data-centers-in-one-city-deployment.md
mysql-compatibility.md
non-transactional-dml.md
online-unsafe-recovery.md
optimistic-transaction.md
optimizer-fix-controls.md
optimizer-hints.md
oracle-functions-to-tidb.md
overview.md
partition-pruning.md
partitioned-raft-kv.md
partitioned-table.md
password-management.md
pd-configuration-file.md
pd-control.md
pd-microservices-deployment-topology.md
pd-microservices.md
pd-recover.md
performance-tuning-methods.md
performance-tuning-overview.md
performance-tuning-practices.md
pessimistic-transaction.md
placement-rules-in-sql.md
post-installation-check.md
predicate-push-down.md
privilege-management.md
production-deployment-using-tiup.md
quick-start-with-htap.md
quick-start-with-tidb.md
read-historical-data.md
replicate-between-primary-and-secondary-clusters.md
replicate-data-to-kafka.md
role-based-access-control.md
runtime-filter.md
scale-microservices-using-tiup.md
scale-tidb-using-tiup.md
schedule-replicas-by-topology-labels.md
scheduling-configuration-file.md
schema-cache.md
schema-object-names.md
security-compatibility-with-mysql.md
shard-row-id-bits.md
smooth-upgrade-tidb.md
sql-logical-optimization.md
sql-mode.md
sql-non-prepared-plan-cache.md
sql-optimization-concepts.md
sql-physical-optimization.md
sql-plan-management.md
sql-plan-replayer.md
sql-prepared-plan-cache.md
sql-tuning-overview.md
stale-read.md
statement-summary-tables.md
statistics.md
status-variables.md
subquery-optimization.md
support.md
system-variables.md
table-attributes.md
table-filter.md
telemetry.md
temporary-tables.md
three-data-centers-in-two-cities-deployment.md
ticdc-deployment-topology.md
ticdc-performance-tuning-methods.md
tidb-architecture.md
tidb-computing.md
tidb-configuration-file.md
tidb-control.md
tidb-distributed-execution-framework.md
tidb-external-ts.md
tidb-global-sort.md
tidb-in-kubernetes.md
tidb-limitations.md
tidb-monitoring-api.md
tidb-monitoring-framework.md
tidb-operator-overview.md
tidb-read-staleness.md
tidb-resource-control.md
tidb-roadmap.md
tidb-scheduling.md
tidb-storage.md
tidb-troubleshooting-map.md
tiflash-deployment-topology.md
tiflash-performance-tuning-methods.md
tiflash-upgrade-guide.md
tikv-configuration-file.md
tikv-control.md
tikv-in-memory-engine.md
tikv-overview.md
time-to-live.md
tispark-deployment-topology.md
tispark-overview.md
topn-limit-push-down.md
transaction-isolation-levels.md
transaction-overview.md
troubleshoot-cpu-issues.md
troubleshoot-data-inconsistency-errors.md
troubleshoot-high-disk-io.md
troubleshoot-hot-spot-issues.md
troubleshoot-lock-conflicts.md
troubleshoot-stale-read.md
troubleshoot-tidb-cluster.md
troubleshoot-tidb-oom.md
troubleshoot-write-conflicts.md
tso-configuration-file.md
tso.md
tune-operating-system.md
tune-region-performance.md
tune-tikv-memory-performance.md
tune-tikv-thread-performance.md
two-data-centers-in-one-city-deployment.md
upgrade-monitoring-services.md
upgrade-tidb-using-tiup.md
user-account-management.md
user-defined-variables.md
vector-search-data-types.md
vector-search-functions-and-operators.md
vector-search-get-started-using-python.md
vector-search-get-started-using-sql.md
vector-search-improve-performance.md
vector-search-index.md
vector-search-integrate-with-django-orm.md
vector-search-integrate-with-jinaai-embedding.md
vector-search-integrate-with-langchain.md
vector-search-integrate-with-llamaindex.md
vector-search-integrate-with-peewee.md
vector-search-integrate-with-sqlalchemy.md
vector-search-integration-overview.md
vector-search-limitations.md
vector-search-overview.md
views.md
wrong-index-solution.md
克隆/下载
views.md 7.20 KB
一键复制 编辑 原始数据 按行查看 历史
Aolin 提交于 9个月前 . *: add summary to docs (#17018)
titlealiasessummary
视图
/docs-cn/dev/views//docs-cn/dev/reference/sql/view/
TiDB 支持视图,视图是虚拟表,结构由创建时的 SELECT 语句定义。使用视图可保证数据安全,简化复杂查询。查询视图类似查询表,TiDB 执行查询时会展开视图。可通过 SHOW CREATE TABLE 或 SHOW CREATE VIEW 查看视图创建语句及相关信息。也可查询 INFORMATION_SCHEMA.VIEWS 表或访问 HTTP API 获取视图元信息。视图有局限性,不支持物化视图,且为只读视图,不支持写入操作。已创建的视图仅支持 DROP 操作。

视图

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

查询视图

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

查看视图的相关信息

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

使用 SHOW CREATE TABLE view_nameSHOW CREATE VIEW view_name 语句

示例:

{{< copyable "sql" >}}

show create view v;

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

+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View                                                                                                                                                         | character_set_client | collation_connection |
+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| 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      |
+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

查询 INFORMATION_SCHEMA.VIEWS

示例:

{{< copyable "sql" >}}

select * from information_schema.views;

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

+---------------+--------------+------------+------------------------------------------------------------------------+--------------+--------------+----------------+---------------+----------------------+----------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | VIEW_DEFINITION                                                        | CHECK_OPTION | IS_UPDATABLE | DEFINER        | SECURITY_TYPE | CHARACTER_SET_CLIENT | COLLATION_CONNECTION |
+---------------+--------------+------------+------------------------------------------------------------------------+--------------+--------------+----------------+---------------+----------------------+----------------------+
| 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      |
+---------------+--------------+------------+------------------------------------------------------------------------+--------------+--------------+----------------+---------------+----------------------+----------------------+
1 row in set (0.00 sec)

查询 HTTP API

示例:

{{< copyable "" >}}

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

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

{
 "id": 122,
 "name": {
  "O": "v",
  "L": "v"
 },
 "charset": "utf8",
 "collate": "utf8_general_ci",
 "cols": [
  {
   "id": 1,
   "name": {
    "O": "a",
    "L": "a"
   },
   "offset": 0,
   "origin_default": null,
   "default": null,
   "default_bit": null,
   "default_is_expr": false,
   "generated_expr_string": "",
   "generated_stored": false,
   "dependences": null,
   "type": {
    "Tp": 0,
    "Flag": 0,
    "Flen": 0,
    "Decimal": 0,
    "Charset": "",
    "Collate": "",
    "Elems": null
   },
   "state": 5,
   "comment": "",
   "hidden": false,
   "version": 0
  }
 ],
 "index_info": null,
 "fk_info": null,
 "state": 5,
 "pk_is_handle": false,
 "is_common_handle": false,
 "comment": "",
 "auto_inc_id": 0,
 "auto_id_cache": 0,
 "auto_rand_id": 0,
 "max_col_id": 1,
 "max_idx_id": 0,
 "update_timestamp": 416801600091455490,
 "ShardRowIDBits": 0,
 "max_shard_row_id_bits": 0,
 "auto_random_bits": 0,
 "pre_split_regions": 0,
 "partition": null,
 "compression": "",
 "view": {
  "view_algorithm": 0,
  "view_definer": {
   "Username": "root",
   "Hostname": "127.0.0.1",
   "CurrentUser": false,
   "AuthUsername": "root",
   "AuthHostname": "%"
  },
  "view_security": 0,
  "view_select": "SELECT `s`.`a` FROM `test`.`t` LEFT JOIN `test`.`s` ON `t`.`a`=`s`.`a`",
  "view_checkoption": 1,
  "view_cols": null
 },
 "sequence": null,
 "Lock": null,
 "version": 3,
 "tiflash_replica": null
}

示例

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

{{< copyable "sql" >}}

create table t(a int, b int);
Query OK, 0 rows affected (0.01 sec)

{{< copyable "sql" >}}

insert into t values(1, 1),(2,2),(3,3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

{{< copyable "sql" >}}

create table s(a int);
Query OK, 0 rows affected (0.01 sec)

{{< copyable "sql" >}}

insert into s values(2),(3);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

{{< copyable "sql" >}}

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)

{{< copyable "sql" >}}

select * from v;
+------+
| a    |
+------+
| NULL |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

{{< copyable "sql" >}}

drop view v;
Query OK, 0 rows affected (0.02 sec)

局限性

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

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

扩展阅读

Loading...
马建仓 AI 助手
尝试更多
代码解读
代码找茬
代码优化
1
https://gitee.com/pingcap/docs-cn.git
git@gitee.com:pingcap/docs-cn.git
pingcap
docs-cn
docs-cn
master

搜索帮助