目录，模式，用户和数据库实例之间的关系 [英] Relationship between catalog, schema, user, and database instance

问题描述

要比较不同供应商（Oracle，SQL Server，DB2，MySQL和PostgreSQL）的数据库，如何识别任何对象，需要一个目录？例如，在Java的DatabaseMetadata中，我至少应该指定目录和模式fooPattern。

To compare databases of different vendors (Oracle, SQL Server, DB2, MySQL, and PostgreSQL) how can I identify any object uniquely and do I need a catalog? For instance, In Java's DatabaseMetadata I should specify catalog and schema fooPattern at least.

目录只是数据存储的抽象是真的吗？

Is it true that catalog is just an abstraction of data storage?

推荐答案

在Oracle中

• 服务器实例==数据库==目录==所有由相同管理的数据执行引擎


• schema ==数据库中的命名空间，与用户帐户相同


• user ==模式所有者==命名帐户，与模式相同，谁可以连接到数据库，谁拥有模式并使用可能在其他模式中的对象


• 来标识运行服务器中的任何对象，您需要（模式名称+对象名称）

• server instance == database == catalog == all data managed by same execution engine
• schema == namespace within database, identical to user account
• user == schema owner == named account, identical to schema, who can connect to database, who owns the schema and use objects possibly in other schemas
• to identify any object in running server, you need (schema name + object name)

在PostgreSQL中：

• 服务器instance == db cluster ==由相同执行引擎管理的所有数据


• database == catalog ==数据库集群中的单个数据库，与同一个数据库集群中的其他数据库隔离


• schema ==数据库内的命名空间


• user ==命名帐户，谁可以连接（数据库名称+模式名称+对象名称）

• server instance == db cluster == all data managed by same execution engine
• database == catalog == single database within db cluster, isolated from other databases in same db cluster
• schema == namespace within database
• user == named account, who can connect to database, own and use objects in each allowed database separately
• to identify any object in running server, you need (database name + schema name + object name)

在MySQL中：

• 服务器实例==不用目录识别，只是一组数据库


• database == schema == catalog ==服务器内的命名空间。


• 用户==命名的帐户，谁可以连接到服务器并使用（但不能自己的 - 没有所有权概念）一个或多个数据库中的对象


• 来标识运行服务器中的任何对象，您需要（数据库名称+对象名称）

• server instance == not identified with catalog, just a set of databases
• database == schema == catalog == a namespace within the server.
• user == named account, who is can connect to server and use (but can not own - no concept of ownership) objects in one or more databases
• to identify any object in running server, you need (database name + object name)

在Microsoft SQL Server中：

• 服务器实例==管理数据库集


• database ==服务器内的命名空间限定符，很少被称为目录


• schema == owner ==数据库中的命名空间，与数据库角色绑定，由仅使用 dbo


• user ==命名帐户，谁可以连接到服务器并使用（但不能自己 - 在一个或多个数据库中的模式作为所有者）对象


• 标识运行服务器中的任何对象，您需要（数据库名称+所有者+对象名称）

• server instance == set of managed databases
• database == namespace qualifier within the server, rarely referred to as catalog
• schema == owner == namespace within the database, tied to database roles, by default only dbo is used
• user == named account, who can connect to server and use (but can not own - schema works as owner) objects in one or more databases
• to identify any object in running server, you need (database name + owner + object name)

所以我想回答你的问题是：

So I think answer to your questions is:

1. 这取决于实现，是否需要目录名来标识对象。 目录，模式和数据库的含义因实现而异。

1. It depends on implementation, whether catalog name is needed to identify objects. The meaning of "catalog", "schema" and "database" vary from one implementation to another.

是的，目录是数据存储的抽象。我认为它也应该被定义为一个独立的隔离命名空间，但并不是所有的SQL引擎都这样做。

Yes, a catalog is an abstraction of data storage. I think it should be also defined as a self-contained isolated namespace, but not all SQL engines do it.

数据库和所有供应商都明确定义了模式。 目录有时是数据库（至少在Oracle和Postgres中）的同义词，有时是模式的同义词，有时是两者的同义词。术语目录也经常意味着元数据收集（又称系统表）。

Database and schema are pretty well defined by all vendors. Catalog is sometimes synonymous to "database" (at least in Oracle and Postgres), sometimes synonymous to "schema", and sometimes synonymous to both. The term catalog also often means metadata collection (aka system tables).

这篇关于目录，模式，用户和数据库实例之间的关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！