本文已收录在Github,关注我,紧跟本系列专栏文章,咱们下篇再续!

  • 魔都架构师 | 全网30W技术追随者
  • 大厂分布式系统/数据中台实战专家
  • 主导交易系统百万级流量调优 & 车联网平台架构
  • AIGC应用开发先行者 | 区块链落地实践者
  • 以技术驱动创新,我们的征途是改变世界!
  • 实战干货:编程严选网

1 Schema概念

schema在数据库领域,表示数据库对象的集合,包含各种对象如:表、视图、存储过程和索引等。

SQL 标准

一个数据库(Database)可包含多个模式(Schema),模式是数据库对象的逻辑容器。

Oracle

Schema 与用户(User)强绑定。创建用户时就等于创建了一个同名 Schema。用户(user)就是 schema 的拥有者,用户名称与 schema 名通常一致。一般一个用户对应一个集合,为了区分,需给不同集合起名。其实一个用户/角色可拥有多个 schema,多个用户也可以访问同一个 schema(通过权限授权)。

用户的schema名相当于用户名,并作为该用户的缺省schema。所以schema集合看上去像用户名。如访问一个数据表时,若该表未指明属于哪个schema,系统会自动加上默认schema:

PostgreSQL

schem是数据库内的命名空间,可由任一角色拥有;数据库中常见的 public schema 与角色不一一对应,角色可以拥有多个 schema,schema 名不等于用户名。解析未限定对象时依赖 search_path(搜索路径)。

SQL Server

自 SQL Server 2005 起,schema 与数据库用户分离(schema 是命名空间,用户可有默认 schema,如 dbo)。所以 username ≠ schema name(尽管历史上两者关系紧密)。用户可以有默认 Schema,但 Schema 本身是独立对象。

MySQL

databaseschema 视作同义词(CREATE SCHEMA = CREATE DATABASE);MySQL 没有像 PostgreSQL 那样的 schema 命名空间概念。

2 Schema的创建

在不同数据库中要创建的Schema方法不一,但共同点是都支持CREATE SCHEMA语句。MySQL中可通过CREATE SCHEMA创建一个数据库。

不同 DBMS 的 CREATE SCHEMA 语义和可用选项不同,如:

  • PostgreSQL 支持 CREATE SCHEMA name AUTHORIZATION owner
  • Oracle 的 CREATE SCHEMA 语法存在但不常用于创建用户,通常用 CREATE USER 来建立 schema 所属的用户)

Oracle / PostgreSQL / MySQL / SQL Server 在 schema 概念、默认行为和常用语法上的差异:

  • PostgreSQL
CREATE SCHEMA myschema AUTHORIZATION myrole;
SET search_path TO myschema, public;
-- unqualified object -> 按 search_path 解析
  • SQL Server
CREATE SCHEMA Sales AUTHORIZATION dbo;
ALTER USER alice WITH DEFAULT_SCHEMA = Sales;
-- 未限定的表名先在 alice 的默认 schema 中查找,再到 dbo 等
  • MySQL
CREATE DATABASE mydb;   -- 或 CREATE SCHEMA mydb;
USE mydb;
CREATE TABLE t1 (...);
  • Oracle(常见用法)
CREATE USER scott IDENTIFIED BY tiger;
GRANT CONNECT, RESOURCE TO scott;
-- scott 有自己的 schema(对象以 scott. 开头)

3 默认解析规则

不同数据库在你未显式指定 Schema 时,解析对象名的方式不同:

PostgreSQL

使用 search_path 参数决定解析顺序:

SET search_path TO myschema, public;
SELECT * FROM mytable;  -- 优先在 myschema 中查找

默认包含 "$user", public,即先尝试与当前用户名同名的 Schema,再找 public

SQL Server

用户有一个 默认 Schema

ALTER USER alice WITH DEFAULT_SCHEMA = Sales;
SELECT * FROM Orders;   -- 会先到 Sales.Orders 查找

如果对象在默认 Schema 不存在,会尝试 dbo

MySQL

使用 USE dbname 设置当前数据库:

USE mydb;
SELECT * FROM mytable;  -- 实际为 mydb.mytable

没有独立的 Schema 概念。

Oracle

登录用户的名字就是 Schema:

CONNECT scott/tiger;
SELECT * FROM emp;  -- 实际为 scott.emp

若要访问其他用户对象,需要显式加前缀(如 hr.employees)并具备权限。

4 权限与所有权

创建 Schema

PostgreSQL

CREATE SCHEMA myschema AUTHORIZATION alice;

alice 拥有该 Schema,对象默认归她所有。

SQL Server

CREATE SCHEMA Sales AUTHORIZATION dbo;

MySQL

CREATE DATABASE mydb;  -- 或 CREATE SCHEMA mydb;

Oracle 通常通过 CREATE USER 创建用户即可间接创建 Schema。

授权访问

PostgreSQL

GRANT USAGE ON SCHEMA myschema TO bob;
GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO bob;

SQL Server

GRANT SELECT ON SCHEMA::Sales TO bob;

Oracle

GRANT SELECT ON scott.emp TO hr;

修改默认 Schema / 搜索路径

  • PostgreSQL:SET search_path
  • SQL Server:ALTER USER … WITH DEFAULT_SCHEMA
  • MySQL:USE dbname
  • Oracle:无直接机制,登录用户默认使用自身 Schema

5 Schema vs Database vs Catalog

Database

  • SQL 标准:数据库是 Catalog 的一种实现,包含多个 Schema。
  • 实际上,不同产品语义不同:
    • PostgreSQL:一个 Database 包含多个 Schema。
    • SQL Server:一个 Database 包含多个 Schema。
    • Oracle:一个数据库实例通常只包含一组用户 Schema。
    • MySQL:Database 与 Schema 等价

Schema

命名空间,组织数据库对象。

Catalog

SQL 标准中的更大逻辑容器,常对应一个数据库实例。实际中较少直接使用。

6 总结

四大主流数据库 Schema 特性对照:

特性OraclePostgreSQLSQL ServerMySQL
Schema 与用户关系用户 = Schema(1:1),创建用户即创建 SchemaSchema 独立于用户,用户可拥有多个 Schema自 2005 起,Schema 与用户分离;用户有默认 SchemaSchema = Database;无独立 Schema 概念
默认 Schema 行为登录用户即默认 Schema,例如 scott.empsearch_path 决定,默认 "$user", public用户默认 Schema(可改),否则 fallback 到 dboUSE dbname 设定当前数据库,表解析为 dbname.table
创建方式CREATE USER scott IDENTIFIED BY pwd;(即创建 Schema)CREATE SCHEMA myschema AUTHORIZATION alice;CREATE SCHEMA Sales AUTHORIZATION dbo;CREATE DATABASE mydb;CREATE SCHEMA mydb;
跨 Schema 访问hr.employees(需权限)other_schema.table(需 USAGE 权限)Sales.Orders(需权限)otherdb.table(需权限)
权限控制GRANT SELECT ON scott.emp TO hr;GRANT USAGE ON SCHEMA myschema TO bob;``GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO bob;GRANT SELECT ON SCHEMA::Sales TO bob;GRANT SELECT ON mydb.mytable TO 'bob'@'host';
修改默认 Schema不支持(用户即 Schema)SET search_path TO myschema, public;ALTER USER alice WITH DEFAULT_SCHEMA = Sales;USE dbname;
术语说明Schema ≈ UserDatabase ⊃ SchemaDatabase ⊃ SchemaSchema = Database
  • Oracle:用户就是 Schema
  • PostgreSQL:Schema 是独立命名空间,靠 search_path 解析
  • SQL Server:Schema 独立,用户有默认 Schema,常见是 dbo
  • MySQL:Schema = Database,没有独立命名空间
本站提供的所有下载资源均来自互联网,仅提供学习交流使用,版权归原作者所有。如需商业使用,请联系原作者获得授权。 如您发现有涉嫌侵权的内容,请联系我们 邮箱:[email protected]