本文已收錄在Github，關(guān)注我，緊跟本系列專(zhuān)欄文章，咱們下篇再續(xù)！

• ?? 魔都架構(gòu)師 | 全網(wǎng)30W技術(shù)追隨者
• ?? 大廠分布式系統(tǒng)/數(shù)據(jù)中臺(tái)實(shí)戰(zhàn)專(zhuān)家
• ?? 主導(dǎo)交易系統(tǒng)百萬(wàn)級(jí)流量調(diào)優(yōu) & 車(chē)聯(lián)網(wǎng)平臺(tái)架構(gòu)
• ?? AIGC應(yīng)用開(kāi)發(fā)先行者 | 區(qū)塊鏈落地實(shí)踐者
• ?? 以技術(shù)驅(qū)動(dòng)創(chuàng)新，我們的征途是改變世界！
• ?? 實(shí)戰(zhàn)干貨：編程嚴(yán)選網(wǎng)

1 Schema概念

schema在數(shù)據(jù)庫(kù)領(lǐng)域，表示數(shù)據(jù)庫(kù)對(duì)象的集合，包含各種對(duì)象如：表、視圖、存儲(chǔ)過(guò)程和索引等。

SQL 標(biāo)準(zhǔn)

一個(gè)數(shù)據(jù)庫(kù)（Database）可包含多個(gè)模式（Schema），模式是數(shù)據(jù)庫(kù)對(duì)象的邏輯容器。

Oracle

Schema 與用戶（User）強(qiáng)綁定。創(chuàng)建用戶時(shí)就等于創(chuàng)建了一個(gè)同名 Schema。用戶（user）就是 schema 的擁有者，用戶名稱(chēng)與 schema 名通常一致。一般一個(gè)用戶對(duì)應(yīng)一個(gè)集合，為了區(qū)分，需給不同集合起名。其實(shí)一個(gè)用戶/角色可擁有多個(gè) schema，多個(gè)用戶也可以訪問(wèn)同一個(gè) schema（通過(guò)權(quán)限授權(quán)）。

用戶的schema名相當(dāng)于用戶名，并作為該用戶的缺省schema。所以schema集合看上去像用戶名。如訪問(wèn)一個(gè)數(shù)據(jù)表時(shí)，若該表未指明屬于哪個(gè)schema，系統(tǒng)會(huì)自動(dòng)加上默認(rèn)schema：

PostgreSQL

schem是數(shù)據(jù)庫(kù)內(nèi)的命名空間，可由任一角色擁有；數(shù)據(jù)庫(kù)中常見(jiàn)的 public schema 與角色不一一對(duì)應(yīng)，角色可以擁有多個(gè) schema，schema 名不等于用戶名。解析未限定對(duì)象時(shí)依賴(lài) search_path（搜索路徑）。

SQL Server

自 SQL Server 2005 起，schema 與數(shù)據(jù)庫(kù)用戶分離（schema 是命名空間，用戶可有默認(rèn) schema，如 dbo）。所以 username ≠ schema name（盡管歷史上兩者關(guān)系緊密）。用戶可以有默認(rèn) Schema，但 Schema 本身是獨(dú)立對(duì)象。

MySQL

把 database 與 schema 視作同義詞（CREATE SCHEMA = CREATE DATABASE）；MySQL 沒(méi)有像 PostgreSQL 那樣的 schema 命名空間概念。

2 Schema的創(chuàng)建

在不同數(shù)據(jù)庫(kù)中要?jiǎng)?chuàng)建的Schema方法不一，但共同點(diǎn)是都支持CREATE SCHEMA語(yǔ)句。MySQL中可通過(guò)CREATE SCHEMA創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)。

不同 DBMS 的 CREATE SCHEMA 語(yǔ)義和可用選項(xiàng)不同，如：

• PostgreSQL 支持 CREATE SCHEMA name AUTHORIZATION owner
• Oracle 的 CREATE SCHEMA 語(yǔ)法存在但不常用于創(chuàng)建用戶，通常用 CREATE USER 來(lái)建立 schema 所屬的用戶）

Oracle / PostgreSQL / MySQL / SQL Server 在 schema 概念、默認(rèn)行為和常用語(yǔ)法上的差異：

• 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 的默認(rèn) schema 中查找，再到 dbo 等

• MySQL

CREATE DATABASE mydb;   -- 或 CREATE SCHEMA mydb;
USE mydb;
CREATE TABLE t1 (...);

• Oracle（常見(jiàn)用法）

CREATE USER scott IDENTIFIED BY tiger;
GRANT CONNECT, RESOURCE TO scott;
-- scott 有自己的 schema（對(duì)象以 scott. 開(kāi)頭）

3 默認(rèn)解析規(guī)則

不同數(shù)據(jù)庫(kù)在你未顯式指定 Schema 時(shí)，解析對(duì)象名的方式不同：

PostgreSQL

使用 search_path 參數(shù)決定解析順序：

SET search_path TO myschema, public;
SELECT * FROM mytable;  -- 優(yōu)先在 myschema 中查找

默認(rèn)包含 "$user", public，即先嘗試與當(dāng)前用戶名同名的 Schema，再找 public。

SQL Server

用戶有一個(gè) 默認(rèn) Schema：

ALTER USER alice WITH DEFAULT_SCHEMA = Sales;
SELECT * FROM Orders;   -- 會(huì)先到 Sales.Orders 查找

如果對(duì)象在默認(rèn) Schema 不存在，會(huì)嘗試 dbo。

MySQL

使用 USE dbname 設(shè)置當(dāng)前數(shù)據(jù)庫(kù)：

USE mydb;
SELECT * FROM mytable;  -- 實(shí)際為 mydb.mytable

沒(méi)有獨(dú)立的 Schema 概念。

Oracle

登錄用戶的名字就是 Schema：

CONNECT scott/tiger;
SELECT * FROM emp;  -- 實(shí)際為 scott.emp

若要訪問(wèn)其他用戶對(duì)象，需要顯式加前綴（如 hr.employees）并具備權(quán)限。

4 權(quán)限與所有權(quán)

創(chuàng)建 Schema

PostgreSQL

CREATE SCHEMA myschema AUTHORIZATION alice;

alice 擁有該 Schema，對(duì)象默認(rèn)歸她所有。

SQL Server

CREATE SCHEMA Sales AUTHORIZATION dbo;

MySQL

CREATE DATABASE mydb;  -- 或 CREATE SCHEMA mydb;

Oracle
通常通過(guò) CREATE USER 創(chuàng)建用戶即可間接創(chuàng)建 Schema。

授權(quán)訪問(wèn)

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;

修改默認(rèn) Schema / 搜索路徑：

• PostgreSQL：SET search_path
• SQL Server：ALTER USER … WITH DEFAULT_SCHEMA
• MySQL：USE dbname
• Oracle：無(wú)直接機(jī)制，登錄用戶默認(rèn)使用自身 Schema

5 Schema vs Database vs Catalog

Database

• SQL 標(biāo)準(zhǔn)：數(shù)據(jù)庫(kù)是 Catalog 的一種實(shí)現(xiàn)，包含多個(gè) Schema。
• 實(shí)際上，不同產(chǎn)品語(yǔ)義不同：
  • PostgreSQL：一個(gè) Database 包含多個(gè) Schema。
  • SQL Server：一個(gè) Database 包含多個(gè) Schema。
  • Oracle：一個(gè)數(shù)據(jù)庫(kù)實(shí)例通常只包含一組用戶 Schema。
  • MySQL：Database 與 Schema 等價(jià)。

Schema

命名空間，組織數(shù)據(jù)庫(kù)對(duì)象。

Catalog

SQL 標(biāo)準(zhǔn)中的更大邏輯容器，常對(duì)應(yīng)一個(gè)數(shù)據(jù)庫(kù)實(shí)例。實(shí)際中較少直接使用。

6 總結(jié)

四大主流數(shù)據(jù)庫(kù) Schema 特性對(duì)照：

• Oracle：用戶就是 Schema
• PostgreSQL：Schema 是獨(dú)立命名空間，靠 search_path 解析
• SQL Server：Schema 獨(dú)立，用戶有默認(rèn) Schema，常見(jiàn)是 dbo
• MySQL：Schema = Database，沒(méi)有獨(dú)立命名空間

本文由博客一文多發(fā)平臺(tái) OpenWrite 發(fā)布！