关系数据库中的目录和模式有什么区别?

发布于 2024-11-29 12:50:43 字数 183 浏览 0 评论 0 原文

我曾经认为模式是数据库本身之前的“上层包装”对象。 我的意思是DB.schema。

好吧,目录“包装器”现在很混乱。为什么我们需要目录?目录到底应该用于什么目的?

I used to think schema were the "upper wrapper" object before the database itself.
I mean DB.schema.<what_ever_object_name_under_schema>.

Well, the catalog "wrapper" is now quite confusing. Why should we need a catalog? For what purpose, precisely should the catalog be used?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

飘过的浮云 2024-12-06 12:50:43

Mike Sherrill 的“Cat Recall” 给出了一个很好的答案。我将简单地添加一个示例:Postgres

集群 = Postgres 安装

当您在计算机上安装 Postgres 时,该安装称为集群。这里的“集群”并不是指多台计算机一起工作的硬件意义上。在 Postgres 中,集群指的是您可以使用同一个 Postgres 服务器引擎启动并运行多个不相关的数据库。

集群这个词也由SQL 标准 与 Postgres 中的方式相同。严格遵循 SQL 标准是 Postgres 项目的主要目标。

SQL-92 规范指出:

集群是实现定义的目录集合。

只有一个集群与 SQL 会话关联

这是一种将集群视为数据库服务器(每个目录都是一个数据库)的迟钝说法。

簇>目录>架构>表>专栏和行

因此,在 Postgres 和 SQL 标准中,我们都有这样的包含层次结构:

显示嵌套框的图表,代表端口上的连接如何让您进入包含一个或多个目录(数据库)的集群(数据库服务器) )每个都包含一个或多个模式(命名空间),每个模式都包含表,每个表都有行。

多个集群

此图表示单个集群。对于 Postgres,每台主机(或虚拟操作系统)可以有多个集群。通常会完成多个集群,用于测试和部署新版本的 Postgres(例如:9.09.19.29.39.49.5)。

如果您确实有多个集群,请想象上面的图表是重复的。

不同的端口号允许多个集群同时并排运行。每个集群都将分配有自己的端口号。通常的5432只是默认值,您可以自行设置。每个集群都在自己分配的端口上侦听传入的数据库连接。

示例场景

例如,一家公司可能有两个不同的软件开发团队。一个团队编写软件来管理仓库,而另一个团队则构建软件来管理销售和营销。每个开发团队都有自己的数据库,幸福地不知道其他团队的数据库。

但 IT 运营团队决定在一台计算机(Linux、Mac 等)上运行这两个数据库。所以他们在那个盒子上安装了 Postgres。所以一台数据库服务器(数据库集群)。在该集群中,他们创建两个目录,每个开发团队一个目录:一个名为“warehouse”,另一个名为“sales”。

每个开发团队都使用数十个具有不同目的和访问角色的表。因此,每个开发团队都将他们的表组织成模式。巧合的是,两个开发团队都对会计数据进行了一些跟踪,因此每个团队碰巧都有一个名为“会计”的模式。使用相同的架构名称不是问题,因为每个目录都有自己的命名空间,因此不会发生冲突。

此外,每个团队最终都会创建一个用于会计目的的名为“分类帐”的表。再次强调,没有命名冲突。

您可以将此示例视为层次结构......

  • 计算机(硬件盒或虚拟化服务器)
    • Postgres 9.2 集群(安装)
      • 仓库目录(数据库)
        • 库存架构
          • [...一些表格]
        • 会计架构
          • 账本
          • [...其他一些表]
      • 销售目录(数据库)
        • 销售架构
          • [...一些表格]
        • accounting 架构(与上面的名称重合)
          • ledger 表(与上面同名)
          • [...其他一些表]
    • Postgres 9.3 集群
      • [...其他模式&表]

每个开发团队的软件都会与集群建立连接。这样做时,他们必须指定哪个目录(数据库)是他们的。 Postgres 要求您连接到一个目录,但您不限于该目录。该初始目录只是默认目录,当您的 SQL 语句省略目录名称时使用。

因此,如果开发团队需要访问其他团队的表,只要数据库管理员给了他们 /static/ddl-priv.html" rel="noreferrer">权限 这样做。访问是通过以下模式中的显式命名进行的:catalog.schema.table。因此,如果“仓库”团队需要查看其他团队(“销售”团队)的分类帐,他们会使用 sales.accounting.ledger 编写 SQL 语句。要访问自己的分类账,他们只需编写accounting.ledger。如果他们在同一段源代码中访问两个分类帐,他们可以选择通过包含自己的(可选)目录名称来避免混淆,即 warehouse.accounting.ledgersales.accounting.ledger


顺便说一下......

您可能听说过“架构”这个词具有更一般的含义,它意味着特定数据库表结构的整个设计。相比之下,在 SQL 标准中,该词特指集群中的特定层。目录>架构>表层次结构。

Postgres 在不同的地方都使用“数据库”和“目录”一词,例如创建数据库 命令。

并非所有数据库系统都提供Cluster>的完整层次结构。目录>架构>表。有些只有一个目录(数据库)。有些没有架构,只有一组表。 Postgres 是一款功能异常强大的产品。

Mike Sherrill 'Cat Recall' gave an excellent answer. I'll add simply one example: Postgres.

Cluster = A Postgres Installation

When you install Postgres on a machine, that installation is called a cluster. ‘Cluster’ here is not meant in the hardware sense of multiple computers working together. In Postgres, cluster refers to the fact that you can have multiple unrelated databases all up and running using the same Postgres server engine.

The word cluster is also defined by the SQL Standard in the same way as in Postgres. Closely following the SQL Standard is a primary goal of the Postgres project.

The SQL-92 specification says:

A cluster is an implementation-defined collection of catalogs.

and

Exactly one cluster is associated with an SQL-session

That's an obtuse way of saying a cluster is a database server (each catalog is a database).

Cluster > Catalog > Schema > Table > Columns & Rows

So in both Postgres and the SQL Standard we have this containment hierarchy:

  • A computer may have one cluster or multiple.
  • A database server is a cluster.
  • A cluster has catalogs. ( Catalog = Database )
  • Catalogs have schemas. (Schema = namespace of tables, and security boundary)
  • Schemas have tables.
  • Tables have rows.
  • Rows have values, defined by columns.
    Those values are the business data your apps and users care about such as person's name, invoice due date, product price, gamer’s high score. The column defines the data type of the values (text, date, number, and so on).

Diagram showing nesting boxes representing how connecting on a port gets you to cluster (a database server) which contains one or more Catalogs (a database) each of which contains one or more Schemas (a namespace) each of which contains tables each of which has rows.

Multiple Clusters

This diagram represents a single cluster. In the case of Postgres, you can have more than one cluster per host computer (or virtual OS). Multiple clusters is commonly done, for testing and deploying new versions of Postgres (ex: 9.0, 9.1, 9.2, 9.3, 9.4, 9.5).

If you did have multiple clusters, imagine the diagram above duplicated.

Different port numbers allow the multiple clusters to live side-by-side all up and running at the same time. Each cluster would be assigned its own port number. The usual 5432 is only the default, and can be set by you. Each cluster is listening on its own assigned port for incoming database connections.

Example Scenario

For example, a company could have two different software development teams. One writes software to manage the warehouses while the other team builds software to manage sales and marketing. Each dev team has their own database, blissfully unaware of the other’s.

But the IT operations team took a decision to run both databases on a single computer box (Linux, Mac, whatever). So on that box they installed Postgres. So one database server (database cluster). In that cluster, they create two catalogs, a catalog for each dev team: one named 'warehouse' and one named 'sales'.

Each dev team uses many dozens of tables with different purposes and access roles. So each dev team organizes their tables into schemas. By coincidence, both dev teams do some tracking of accounting data, so each team happens to have a schema named 'accounting'. Using the same schema name is not a problem because the catalogs each have their own namespace so no collision.

Furthermore, each team eventually creates a table for accounting purposes named 'ledger'. Again, no naming collision.

You can think of this example as a hierarchy…

  • Computer (hardware box or virtualized server)
    • Postgres 9.2 cluster (installation)
      • warehouse catalog (database)
        • inventory schema
          • [… some tables]
        • accounting schema
          • ledger table
          • [… some other tables]
      • sales catalog (database)
        • selling schema
          • [… some tables]
        • accounting schema (coincidental same name as above)
          • ledger table (coincidental same name as above)
          • [… some other tables]
    • Postgres 9.3 cluster
      • [… other schemas & tables]

Each dev team's software makes a connection to the cluster. When doing so, they must specify which catalog (database) is theirs. Postgres requires that you connect to one catalog, but you are not limited to that catalog. That initial catalog is merely a default, used when your SQL statements omit the name of a catalog.

So if the dev team ever needs to access the other team's tables, they may do so if the database administrator has given them privileges to do so. Access is made with explicit naming in the pattern: catalog.schema.table. So if the 'warehouse' team needs to see the other team’s ('sales' team) ledger, they write SQL statements with sales.accounting.ledger. To access their own ledger, they merely write accounting.ledger. If they access both ledgers in the same piece of source code, they may choose to avoid confusion by including their own (optional) catalog name, warehouse.accounting.ledger versus sales.accounting.ledger.


By the way…

You may hear the word schema used in a more general sense, meaning the entire design of a particular database's table structure. By contrast, in the SQL Standard the word means specifically the particular layer in the Cluster > Catalog > Schema > Table hierarchy.

Postgres uses both the word database as well as catalog in various places such as the CREATE DATABASE command.

Not all database system provides this full hierarchy of Cluster > Catalog > Schema > Table. Some have only a single catalog (database). Some have no schema, just one set of tables. Postgres is an exceptionally powerful product.

錯遇了你 2024-12-06 12:50:43

从关系的角度来看:

目录是保存所有各种模式(外部、概念、内部)和所有相应映射(外部/概念、概念/内部)的地方。

换句话说,目录包含有关系统本身感兴趣的各种对象的详细信息(有时称为描述符信息元数据)。

例如,优化器使用有关索引和其他物理存储结构的目录信息以及许多其他信息来帮助它决定如何实现用户请求。同样,安全子系统首先使用有关用户和安全约束的目录信息来批准或拒绝此类请求。

数据库系统简介,第 7 版,CJ Date,第 69-70 页。


From the SQL standard point of view :

目录是 SQL 环境中模式的命名集合。一个
SQL 环境包含零个或多个目录。目录包含
一个或多个模式,但始终包含一个名为
INFORMATION_SCHEMA 包含视图和域
信息架构。

数据库语言 SQL,(DIS 9075 的拟议修订文本) ,第 45 页


From the SQL point of view :

目录通常与数据库同义。在大多数 SQL dbms 中,如果查询 information_schema 视图,您会发现“table_catalog”列中的值映射到数据库的名称。

如果您发现您的平台使用目录的方式比这三个定义中的任何一个都更广泛,那么它可能指的是比数据库更广泛的东西——数据库集群、服务器或服务器簇。但我对此表示怀疑,因为您可以在平台的文档中轻松找到这一点。

From the relational point of view :

The catalog is the place where--among other things--all of the various schemas (external, conceptual, internal) and all of the corresponding mappings (external/conceptual, conceptual/internal) are kept.

In other words, the catalog contains detailed information (sometimes called descriptor information or metadata) regarding the various objects that are of interest to the system itself.

For example, the optimizer uses catalog information about indexes and other physical storage structures, as well as much other information, to help it decide how to implement user requests. Likewise, the security subsystem uses catalog information about users and security constraints to grant or deny such requests in the first place.

An Introduction to Database Systems, 7th ed., C.J. Date, p 69-70.


From the SQL standard point of view :

Catalogs are named collections of schemas in an SQL-environment. An
SQL-environment contains zero or more catalogs. A catalog contains
one or more schemas, but always contains a schema named
INFORMATION_SCHEMA that contains the views and domains of the
Information Schema.

Database Language SQL, (Proposed revised text of DIS 9075), p 45


From the SQL point of view :

A catalog is often synonymous with database. In most SQL dbms, if you query the information_schema views, you'll find that values in the "table_catalog" column map to the name of a database.

If you find your platform using catalog in a broader way than any of these three definitions, it might be referring to something broader than a database--a database cluster, a server, or a server cluster. But I kind of doubt that, since you'd have found that easily in your platform's documentation.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文