logo标签列表

目录、模式、用户和数据库实例之间的关系

mysqlsql-serveroraclepostgresqldb2
72

如何比较不同厂商的数据库（如Oracle、SQL Server、DB2、MySQL和PostgreSQL）中的对象并确定是否需要目录？例如，在Java的DatabaseMetadata中，我至少应该指定目录和模式fooPattern。

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

https://dev59.com/9Gw05IYBdhLWcg3wy012 - gavenkoa
4个回答
147
  • 在Oracle中：
    • 服务器实例 == 数据库 == 目录 == 由同一执行引擎管理的所有数据。
    • 模式 == 数据库内的命名空间，与用户帐户相同。
    • 用户 == 模式所有者 == 命名帐户，与模式相同，可以连接到数据库，拥有模式并使用其他模式中的对象。
    • 要在正在运行的服务器中标识任何对象，您需要（模式名称+对象名称）。
  • 在PostgreSQL中：
    • 服务器实例 == 数据库集群 == 由同一执行引擎管理的所有数据。
    • 数据库 == 目录 == 数据库集群中的单个数据库，与同一数据库集群中的其他数据库隔离。
    • 模式 == 数据库内的命名空间，默认情况下使用 public。
    • 用户 == 命名帐户，可以连接到数据库，在每个允许的数据库中拥有和使用对象。
    • 要在正在运行的服务器中标识任何对象，您需要（数据库名称 + 模式名称 + 对象名称）。
  • 在MySQL中：
    • 服务器实例 == 不与目录标识，只是一组数据库。
    • 数据库 == 模式 == 目录 == 服务器内的命名空间。
    • 用户 == 命名帐户，可以连接到服务器并在一个或多个数据库中使用对象（但没有 拥有 的概念）。
    • 要在正在运行的服务器中标识任何对象，您需要（数据库名称 + 对象名称）。
  • 在Microsoft SQL Server中：
    • 服务器实例 == 托管数据库集合。
    • 数据库 == 服务器内的命名空间限定符，很少称为目录。
    • 模式 == 所有者 == 数据库内的命名空间，与数据库角色绑定，默认情况下使用 dbo。
    • 用户 == 命名帐户，可以连接到服务器并在一个或多个数据库中使用对象（但不能 拥有 - 模式作为所有者）。
    • 要在正在运行的服务器中标识任何对象，您需要（数据库名称 + 所有者 + 对象名称）。

  • 取决于具体实现，是否需要目录名称来标识对象。在不同的实现中，“目录”、“模式”和“数据库”的含义各不相同。

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

  • 所有的供应商都对“数据库”和“模式”有很好的定义。“目录”有时是“数据库”的同义词（至少在Oracle和Postgres中），有时是“模式”的同义词，有时同时是两者的同义词。术语“目录”通常还指元数据收集（也称系统表）。

  • “模式”是程序员在SQL数据库中组织工件时应使用的内容，因为它代表带有访问控制层的逻辑命名空间。

    • 3
    大部分情况下没问题。我会稍微解释一下Oracle。在Oracle的术语中，“数据库”实际上是一个可被一个或多个“实例”访问的文件集。这就是RAC的工作原理，据我所知，显著不同于其他引擎的聚集方式。此外，考虑到大多数数据库引擎通常只能在一个连接上访问一个“目录”，我不确定目录有什么用处。在JDBC中，模式字段标识了这个级别，在所有流行的引擎中都是如此，尤其是MySQL、PostgreSQL、Oracle和SQL Server。每个引擎都需要一个不同的JDBC URL来获取不同的“目录”。 - PlexQ
    2
    对于MySQL，在使用JDBC时，它似乎将模式标识为目录：TABLE_CAT:iserver, TABLE_SCHEM:null, TABLE_NAME:accountgroups, TABLE_TYPE:TABLE, REMARKS:。TABLE_SCHEM字段为空。根据此输出，我希望在模式字段中看到与目录字段相同的内容。JDBC定义可能有误，或者MYSQL目录与模式相同吗？ - Sam Goldberg
    2
    我同意@SamGoldberg的观点。另一个证据是SHOW SCHEMAS是SHOW DATABASES的同义词（http://dev.mysql.com/doc/refman/5.1/en/show-databases.html）。已更新上面的答案以反映这一点。 - Joe M
    @filiprem - 是否有任何文档或论文解释这些差异的原因以及为什么他们决定不使用相同的术语？例如，为什么在MySql中调用_getCatalog_返回架构/命名空间，在Oracle中却返回数据库。 - Victor
    @Victor，这是历史。数据库软件自1970年代以来就开始开发了。行业标准正在缓慢形成。有些决定极其难以撤销/更改 :-) - filiprem
    我已经在 https://github.com/iwis/SQL-notes/ 上的 DBMS organization.pdf 文件中插入了这个答案的图片。所使用的颜色的含义在 这里 描述。详细信息请参见 DBMS organization - DB, schema, user, connecting, SET ROLE, referencing objects, synonym, tablespace.txt 文件。 - iwis
    5
    对于DB2，架构用作命名空间。因此，如果您想在数据库中唯一标识对象，则应说 *schema.object_name*。这是实现多租户的非常方便的方法。您可以为数据库中的每个租户单独设置一个架构。这从安全和管理方面提供了很好的关注点分离。在单个DB2数据库中，您可以有32K个模式。
    在DB2中，目录只是包含有关数据库的元数据的系统表的集合。通常，直接访问目录对象被认为是一种不良做法。最好使用API（例如JDBC）提供的工具来探索目录和其中包含的元数据。
    DB2还具有其他抽象层。您可以在同一台机器上运行多个DB2实例。每个实例可以管理256个单独的数据库（每个数据库都有32K个模式）。服务器上DB2实例的数量仅受可用内存量限制。我们曾经在Amazon EC2 m1.large上运行120个DB2实例（每个实例具有一个数据库和10个连接）。
    您还可以在单个服务器上安装多个DB2版本。当测试要迁移到的新版本时，这很有用。我发现这很令人困惑，常常忘记切换到正确的安装程序。
    1
    然而，在将多个实例放在一台机器上时，以及使用SQL Server时，甚至在使用多个数据库时，需要考虑事务日志。拥有单独的事务日志的主要好处之一是它按顺序写入存储设备，这对于基于磁盘的存储来说比在写入/读取之间执行查找要快得多。后台进程收集脏数据库块并将它们写入磁盘，优化写入顺序，除非强制进行检查点，导致刷新所有脏块。 - PlexQ
    这个答案是关于Db2 LUW的，它为每个数据库单独设置了事务日志路径。 - Paul Vernon
    我也不相信数据库中模式的数量有限制。我不知道为什么有人会想要这么多，但我刚在我的本地 Db2 LUW 11.1.3.3 数据库中创建了 33000 个模式，并且没有收到来自 DBMS 的投诉。db2 -x“select count (*) from syscat.schemata”返回33069。 - Paul Vernon
    0

    我正在以客户端（驱动程序）的视角分享我的结果。

    • product：调用getProductName()的结果
    • c_term：调用getCataglogTerm()的结果
    • s_term：调用getSchemaTerm()的结果
    • T_CAT：调用getTables(null, null, "%", null)后，TABLE_CAT的不同值
    • T_SCHEM：调用getTables(null, null, "%", null)后，TABLE_SCHEM的不同值
    产品 c_term s_term T_CAT T_SCHEM
    Apache Derby CATALOG SCHEMA <empty> SYS SYSIBM
    H2 catalog schema TEST INFORMATION_SCHEMA
    HSQL Database Engine CATALOG SCHEMA PUBLIC INFORMATION_SCHEMA, SYSTEM_LOBS
    SQLite catalog schema null null
    MySQL database <empty> performance_schema, information_schema null
    MariaDB database schema information_schema null
    PostgreSQL database schema pg_catalog, information_schema pg_toast
    网页内容由stack overflow 提供, 点击上面的
    可以查看英文原文，
    原文链接