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

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

我曾经认为模式是数据库本身之前的“上层包装器”对象。我的意思是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?

嗯，目录“包装器”现在相当混乱。为什么我们需要目录?究竟为了什么目的，应该使用目录?

2 个解决方案

#1

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.

《数据库系统概论》，第七版，C.J.日期，第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.

编目是在sql环境中命名的模式集合。一个sql环境包含零或更多的目录。目录包含一个或多个模式，但始终包含一个名为INFORMATION_SCHEMA的模式，该模式包含信息模式的视图和域。

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

数据库语言SQL，(建议修订的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.

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

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.

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

#2

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

Mike Sherrill的《猫的回忆》给出了一个很好的答案。我将只添加一个示例: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 multiple unrelated databases all up and running using the same Postgres server engine.

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

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.

单词集群也由SQL标准定义，其定义方式与Postgres相同。紧跟SQL标准是Postgres项目的主要目标。

The SQL-92 specification says:

sql - 92规范说:

A cluster is an implementation-defined collection of catalogs.

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

and

和

Exactly one cluster is associated with an SQL-session

恰好有一个集群与sql会话相关联

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:

所以在Postgres和SQL标准中我们都有这个包含层次结构

• 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)
• 目录的模式。(Schema =表的名称空间，安全边界)
• 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).
• 行具有由列定义的值。这些值是您的应用程序和用户关心的业务数据，如人名、发票到期日、产品价格、玩家的高分。列定义值的数据类型(文本、日期、数字等)。

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).

此图表示单个集群。对于Postgres，每个主机(或虚拟OS)可以有多个集群。通常使用多个集群进行测试和部署新版本的Postgres(例如: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.

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

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'.

但是IT运营团队决定在一个电脑机箱(Linux、Mac等等)上运行这两个数据库。他们在那个盒子上安装了Postgres。一个数据库服务器(数据库集群)。在这个集群中，他们创建两个目录，每个开发团队的目录:一个名为“warehouse”，一个名为“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.

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

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.2集群(安装)仓库目录(数据库)库存模式[…有些表会计模式分类表[…其他表销售目录(数据库)销售模式[…有些表会计模式(与上面的名称相同)分类表(与上面的名称相同)[…其他表]
  • Postgres 9.3 cluster
    • [… other schemas & tables]
    • [……其他图式和表格]
  • Postgres 9.3集群[……其他模式和表]
• 计算机(硬件盒子或虚拟服务器)Postgres 9.2集群(安装)仓库目录(数据库)库存模式[…一些表)会计模式分类表[…一些其他表)销售目录(数据库)模式[…一些表)会计模式名称相同(巧合)分类表名称相同(巧合)[…一些其他表)Postgres 9.3集群[…其它模式和表)

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.

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

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.

因此，如果开发团队需要访问其他团队的表，那么如果数据库管理员给了他们访问表的权限，他们可能会这样做。访问是通过模式:catalog.schema.table中的显式命名进行的。因此，如果“仓库”团队需要查看另一个团队(“销售”团队)的分类帐，他们会使用sales.accounting.分类帐编写SQL语句。为了获得自己的分类帐，他们只写帐户。如果他们在同一段源代码中访问了两个ledgers，他们可以选择避免混淆，包括他们自己的(可选的)目录名、仓库。分类帐和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.

您可能在更一般的意义上听到“模式”这个词，这意味着特定数据库的表结构的整个设计。相比之下，在SQL标准中，这个词的意思是在>集群>模式>表层次结构中特定的层。

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

Postgres既使用word数据库，也使用各种地方的目录，如CREATE database命令。

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.

并不是所有的数据库系统都提供了>目录>模式>表的完整层次结构。有些只有一个目录(数据库)。有些没有模式，只有一组表。Postgres是一个非常强大的产品。