数据库命名空间机制对比

本文档对比主流数据库（PostgreSQL、Oracle、MySQL、SQL Server）的 schema/命名空间机制。

1. PostgreSQL

核心概念

PostgreSQL 的 schema 是真正的命名空间概念，类似于操作系统中的目录，用于组织数据库对象（表、视图、函数、类型等）。每个数据库可以包含多个 schema，schema 之间相互隔离。

search_path 机制

search_path 是 PostgreSQL 最核心的命名空间配置参数，它决定了查找对象时的搜索顺序。

-- 查看当前 search_path
SHOW search_path;

-- 设置搜索路径（会话级别）
SET search_path TO myschema, public;

-- 设置永久默认值（需要管理员权限）
ALTER DATABASE dbname SET search_path TO myschema, public;

默认值: "$user", public

• $user: 首先查找与当前用户名同名的 schema（如果存在且有权限）
• public: 其次查找 public schema

特殊规则:

1. pg_catalog 始终隐式包含在搜索路径的最前面，无论是否显式声明
2. 临时表 schema pg_temp 如果存在，会在 pg_catalog 之后自动搜索
3. 路径中不存在的 schema 或用户没有 USAGE 权限的 schema 会被静默忽略

-- 即使不设置，pg_catalog 也会被隐式搜索
SELECT current_schemas(false);  -- 显示显式设置的路径
SELECT current_schemas(true);   -- 包含隐式搜索的 pg_catalog 和 pg_temp

对象解析规则

当执行 SELECT * FROM orders 时：

1. 按 search_path 顺序依次搜索各 schema
2. 第一个匹配的对象被使用，即使后续 schema 有同名对象也会被忽略
3. 如果在所有路径中都找不到，抛出错误

安全风险: search_path 中包含 public 意味着任何在该 schema 有 CREATE 权限的用户都可以创建与你的表同名的对象来”劫持”你的查询。这就是为什么生产环境建议从 search_path 中移除 public。

Schema 操作

-- 创建 schema
CREATE SCHEMA myschema;

-- 创建 schema（指定所有者）
CREATE SCHEMA myschema AUTHORIZATION username;

-- 删除 schema（必须先删除其中的对象）
DROP SCHEMA myschema CASCADE;

-- 将对象移动到另一个 schema
ALTER TABLE mytable SET SCHEMA myschema;

创建对象时的默认行为

创建对象时（不带 schema 前缀），对象会被创建在 search_path 中第一个存在且有效的 schema。

SET search_path TO myschema, public;
CREATE TABLE orders (id int);  -- 实际创建在 myschema.orders

2. Oracle

核心概念

Oracle 的 schema 与用户紧密绑定：创建用户即创建 schema。每个用户拥有一个同名的 schema，其中包含该用户的所有数据库对象。

-- 这会同时创建用户和 schema
CREATE USER myschema IDENTIFIED BY password;

命名空间解析

Oracle 没有 search_path 机制。对象解析采用不同的策略：

1. 首先在当前用户的 schema 中搜索
2. 如果找不到，再搜索 公共同义词（PUBLIC SYNONYM） 指向的对象
3. 必须显式使用 schema.object 格式访问其他 schema 的对象

-- 解析顺序
SELECT * FROM orders;              -- 查找 current_user.orders
SELECT * FROM myschema.orders;    -- 直接指定 schema

-- 创建同义词方便访问其他 schema
CREATE PUBLIC SYNONYM orders FOR myschema.orders;

切换当前 Schema

Oracle 提供 ALTER SESSION SET CURRENT_SCHEMA 来改变会话的默认 schema，但仅影响对象解析，不影响权限：

-- 切换默认 schema（仅影响对象解析）
ALTER SESSION SET CURRENT_SCHEMA = myschema;

-- 之后执行
SELECT * FROM orders;  -- 实际查询 myschema.orders

关键区别: 切换 CURRENT_SCHEMA 后，你仍然以原用户身份操作，继承的是原用户的权限，而非目标 schema 所属用户的权限。

Schema 与用户的关系

-- 创建 schema（实际是创建用户）
CREATE USER myschema IDENTIFIED BY password;

-- 删除 schema（实际是删除用户，同时删除所有对象）
DROP USER myschema CASCADE;

-- 创建对象
CREATE TABLE myschema.orders (id int);  -- 在 myschema 中创建

3. MySQL

核心概念

在 MySQL 中，Schema 和 Database 是同义词，完全等价。MySQL 本身没有真正的 schema 概念（即命名空间层次），Database 就是最顶层的设计。

-- 这两条语句等价
CREATE SCHEMA mydb;
CREATE DATABASE mydb;

USE 语句

MySQL 通过 USE 语句设置当前数据库（相当于默认 schema），但不是一个搜索路径：

USE mydb;  -- 设置当前数据库

SELECT * FROM orders;  -- 实际查询 mydb.orders

局限性

1. 无搜索路径: MySQL 不支持同时搜索多个 schema/数据库
2. 跨库访问必须使用完全限定名: SELECT * FROM otherdb.orders
3. 连接时确定: 每个连接只能工作在一个数据库上下文中
4. 无动态切换机制: 切换数据库用 USE 语句

-- 如果忘记 USE，会收到错误
SELECT * FROM orders;
-- ERROR 1046 (3D000): No database selected

-- 必须明确指定
SELECT * FROM mydb.orders;
-- 或先 USE mydb

与 PostgreSQL 的对比

4. SQL Server

核心概念

SQL Server 从 2005 版本开始将 schema 与用户分离。Schema 是独立于用户的数据库对象容器，所有者可以是用户或角色，一个用户可以拥有多个 schema。

-- 创建 schema
CREATE SCHEMA Sales;

-- 创建用户（不自动创建 schema）
CREATE USER Mary WITHOUT LOGIN;

-- 为用户设置默认 schema
ALTER USER Mary WITH DEFAULT_SCHEMA = Sales;

默认 Schema 解析

SQL Server 对未限定的对象名使用两级搜索：

1. 用户的默认 schema
2. dbo schema（兜底）

-- 解析顺序
SELECT * FROM orders;
-- 1. 先在 user_default_schema.orders 查找
-- 2. 再在 dbo.orders 查找
-- 3. 都找不到则报错

系统角色成员: sysadmin 固定服务器角色的成员默认 schema 始终为 dbo。

ALTER SCHEMA 操作

-- 在 schema 之间移动对象
ALTER SCHEMA Sales TRANSFER OBJECT::dbo.Region;

-- 转让 schema 所有权
ALTER AUTHORIZATION ON SCHEMA::Sales TO new_owner;

DEFAULT_SCHEMA 设置

-- 创建用户时指定
CREATE USER Mary WITH DEFAULT_SCHEMA = Sales;

-- 修改用户默认 schema
ALTER USER Mary WITH DEFAULT_SCHEMA = Purchasing;

-- 查看用户默认 schema
SELECT name, default_schema_name
FROM sys.database_principals
WHERE type = 'S';

5. 横向对比

关键差异总结

PostgreSQL 最灵活：

• 支持显式的多 schema 搜索路径
• pg_catalog 始终隐式优先搜索
• 可以同时配置多个 schema 按优先级查找

Oracle 强制限定：

• 无搜索路径，必须显式限定或通过同义词
• schema 与用户强绑定

MySQL 最简单：

• Schema = Database，无层次化命名空间
• 连接时绑定单一数据库上下文

SQL Server 分离设计：

• Schema 与用户/角色分离
• 默认两级搜索（用户默认 schema → dbo）

6. 实际应用建议

迁移场景

安全建议

1. PostgreSQL 生产环境:

   -- 移除 public 从 search_path，避免同名对象劫持
   SET search_path TO myschema, pg_catalog;
   
   -- 对于 SECURITY DEFINER 函数，明确指定 search_path
   CREATE FUNCTION myfunc() RETURNS int AS $$
   BEGIN
     RETURN 1;
   END;
   $$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = myschema, pg_catalog;

2. 避免安全风险:

   • 不要在 search_path 中包含不信任的 schema
   • 生产环境移除 public schema 的 CREATE 权限
   • 使用 SECURITY DEFINER 函数时显式设置 search_path

跨数据库兼容设计

如果需要编写跨数据库的应用代码：

-- PostgreSQL
SET search_path TO myschema, public;

-- Oracle (需要兼容层或工具)
ALTER SESSION SET CURRENT_SCHEMA = myschema;

-- MySQL (切换数据库)
USE myschema;

-- SQL Server (设置默认 schema)
EXEC sp_defaultdb @loginame = 'username', @defdb = 'myschema';
-- 或
ALTER USER username WITH DEFAULT_SCHEMA = myschema;

6. 跨数据库 Schema 迁移适配

跨数据库迁移时，schema 适配是核心挑战之一。不同数据库的 schema 机制差异巨大，需要系统性分析和转换。

6.1 Schema 映射的核心问题

概念层级差异

迁移路径中的典型问题

MySQL → PostgreSQL

-- MySQL: USE database 是切换上下文
USE mydb;
CREATE TABLE orders (...);

-- PostgreSQL: 需要创建 schema 并设置 search_path
CREATE SCHEMA mydb;
SET search_path TO mydb, public;
CREATE TABLE orders (...);

Oracle → PostgreSQL

-- Oracle: 用户即 schema
CREATE USER sales IDENTIFIED BY password;
CREATE TABLE sales.orders (...);  -- orders 在 sales schema 中

-- PostgreSQL: 需要显式创建 schema
CREATE SCHEMA sales;
SET search_path TO sales, public;
CREATE TABLE orders (...);  -- 不带前缀，创建在 search_path 第一位

SQL Server → PostgreSQL

-- SQL Server: 默认 schema 为 dbo
CREATE TABLE orders (...);  -- 实际创建在 dbo.orders

-- PostgreSQL: public 是默认 schema
CREATE TABLE orders (...);  -- 实际创建在 public.orders
-- 需要注意：如果 search_path 是 "$user", public
-- 且用户名为 dbo，会优先查找 dbo schema

6.2 数据类型映射

关键差异提醒:

• Oracle 的 DATE 类型包含时间组分，PostgreSQL 的 DATE 不包含
• MySQL 的 TIMESTAMP 范围受限 (1970-2038)，PostgreSQL 无此限制
• PostgreSQL 的 SERIAL 等效于 INTEGER + SEQUENCE，不是真正的自增

6.3 迁移工具生态

通用迁移工具

版本管理工具（不改变数据库类型）

6.4 国产数据库特殊适配

openGauss 兼容模式

openGauss 在创建数据库时可以指定兼容模式：

-- A = Oracle 兼容
-- B = MySQL 兼容
-- C = Teradata 兼容
-- PG = PostgreSQL 兼容（默认）

CREATE DATABASE db1 WITH DBCOMPATIBILITY = 'PG';

PG 模式下的注意事项:

• 大部分语法与 PostgreSQL 兼容
• 触发器等语法参考 Oracle 风格
• search_path 机制与 PostgreSQL 一致

Kingbase 多模式兼容

Kingbase 支持四种数据库兼容模式：

-- 创建兼容 Oracle 的数据库
CREATE DATABASE kingbase_db DBCOMPATIBILITY = 'A';

-- 创建兼容 PostgreSQL 的数据库
CREATE DATABASE kingbase_db DBCOMPATIBILITY = 'PG';

Kingbase → openGauss 迁移注意事项:

1. 去掉 CHARACTER VARYING 中的 CHARACTER 关键字
2. 去掉 BYTEA 中的 BYTE 前缀
3. 去掉表结构前的 DROP TABLE 等语句（按需处理）
4. 双引号处理：openGauss 不加引号默认小写且大小写不敏感
5. 触发器语法需要按 Oracle 风格适配
6. IF(condition, true_val, false_val) 函数需改为 CASE WHEN

-- Kingbase/MySQL 语法
IF(matter_type = 'jg', 'case_concert_examine_jg', 'case_concert_examine_zf')

-- openGauss/Oracle 语法
CASE WHEN matter_type = 'jg' THEN 'case_concert_examine_jg' ELSE 'case_concert_examine_zf' END

6.5 Schema 迁移最佳实践

1. 评估阶段

-- 在源数据库收集 schema 信息
-- PostgreSQL
SELECT schema_name FROM information_schema.schemata;
SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog', 'information_schema');

-- Oracle
SELECT username FROM dba_users WHERE username NOT IN ('SYS', 'SYSTEM');
SELECT owner, table_name FROM dba_tables WHERE owner = 'SCHEMA_NAME';

-- MySQL
SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.tables WHERE TABLE_SCHEMA = 'db_name';

-- SQL Server
SELECT s.name AS SchemaName, t.name AS TableName
FROM sys.tables t JOIN sys.schemas s ON t.schema_id = s.schema_id;

2. 映射设计

建议建立映射表：

3. 搜索路径适配

PostgreSQL 目标库:

-- 在迁移后设置合理的 search_path
ALTER DATABASE target_db SET search_path TO sales, public;

-- 如果源是 Oracle（用户即 schema），可能需要批量创建 schema
DO $$
DECLARE
  r RECORD;
BEGIN
  FOR r IN SELECT DISTINCT owner FROM all_tables WHERE owner NOT IN ('SYS', 'SYSTEM', 'OUTLN')
  LOOP
    EXECUTE 'CREATE SCHEMA IF NOT EXISTS ' || quote_ident(LOWER(r.owner));
  END LOOP;
END $$;

4. 验证检查

-- 验证对象数量
SELECT 'tables' AS object_type, COUNT(*) FROM information_schema.tables WHERE table_schema = 'target_schema'
UNION ALL
SELECT 'views', COUNT(*) FROM information_schema.views WHERE table_schema = 'target_schema'
UNION ALL
SELECT 'sequences', COUNT(*) FROM information_schema.sequences WHERE sequence_schema = 'target_schema';

-- 验证外键关系（关键！）
SELECT
    tc.table_name,
    kcu.column_name,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
    ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
    ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
AND tc.table_schema = 'target_schema';

6.6 迁移决策树

源数据库类型是什么？
├── MySQL
│   └── 目标想用 PostgreSQL 特性？
│       ├── 是 → 将 Database 映射为 Schema，设置 search_path
│       └── 否 → 保持 MySQL 或选择兼容 MySQL 的数据库
├── Oracle
│   └── 目标数据库？
│       ├── PostgreSQL → Ora2Pg 或手动映射，注意 NUMBER 类型精度
│       ├── openGauss/Kingbase(A模式) → 兼容较好，语法适配为主
│       └── MySQL → 挑战大，部分功能需重构
├── SQL Server
│   └── 目标数据库？
│       ├── PostgreSQL → AWS SCT 或 SchemaForge
│       └── openGauss/Kingbase(PG模式) → 语法适配
└── PostgreSQL → openGauss/Kingbase
    └── 直接迁移，注意版本差异和 SQL 语法细节

7. 横向对比

关键差异总结

PostgreSQL 最灵活：

• 支持显式的多 schema 搜索路径
• pg_catalog 始终隐式优先搜索
• 可以同时配置多个 schema 按优先级查找

Oracle 强制限定：

• 无搜索路径，必须显式限定或通过同义词
• schema 与用户强绑定

MySQL 最简单：

• Schema = Database，无层次化命名空间
• 连接时绑定单一数据库上下文

SQL Server 分离设计：

• Schema 与用户/角色分离
• 默认两级搜索（用户默认 schema → dbo）

8. 实际应用建议

迁移场景

安全建议

1. PostgreSQL 生产环境:

   -- 移除 public 从 search_path，避免同名对象劫持
   SET search_path TO myschema, pg_catalog;
   
   -- 对于 SECURITY DEFINER 函数，明确指定 search_path
   CREATE FUNCTION myfunc() RETURNS int AS $$
   BEGIN
     RETURN 1;
   END;
   $$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = myschema, pg_catalog;

2. 避免安全风险:

   • 不要在 search_path 中包含不信任的 schema
   • 生产环境移除 public schema 的 CREATE 权限
   • 使用 SECURITY DEFINER 函数时显式设置 search_path

跨数据库兼容设计

如果需要编写跨数据库的应用代码：

-- PostgreSQL
SET search_path TO myschema, public;

-- Oracle (需要兼容层或工具)
ALTER SESSION SET CURRENT_SCHEMA = myschema;

-- MySQL (切换数据库)
USE myschema;

-- SQL Server (设置默认 schema)
EXEC sp_defaultdb @loginame = 'username', @defdb = 'myschema';
-- 或
ALTER USER username WITH DEFAULT_SCHEMA = myschema;