DBAtlas · 数据库运维图谱

比较两个 schema/环境的差异（schema diff）

Oracle

-- Extract the schema DDL from both environments and take a text diff
-- (generate deterministic DDL with DBMS_METADATA)
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);
SELECT DBMS_METADATA.GET_DDL('TABLE', table_name) FROM user_tables ORDER BY table_name;
-- Spool the output to a file in both environments and compare with diff/fc.

-- Export the schema as a changelog with SQLcl's Liquibase integration:
-- SQL> lb generate-schema
-- Commercial: the Oracle SQL Developer 'Database Diff' tool is also used.

Oracle 没有内置的单命令 schema 比较。常用方法是用 DBMS_METADATA.GET_DDL（关闭 STORAGE/段属性）从两个环境生成确定性的 DDL，再做文本 diff。可视化/自动化比较可使用 SQL Developer 的 Database Diff 向导或 SQLcl。

PostgreSQL

# Dump only the schema from both databases and diff them
pg_dump --schema-only --no-owner --no-privileges sourcedb > source_schema.sql
pg_dump --schema-only --no-owner --no-privileges targetdb  > target_schema.sql
diff -u source_schema.sql target_schema.sql

# Tool that generates applicable ALTERs: apgdiff (Another PostgreSQL Diff Tool)
# apgdiff source_schema.sql target_schema.sql > migration.sql
# Alternatives: migra, Liquibase diffChangeLog

最简单的方法是在两个环境中获取 pg_dump --schema-only 输出并用 diff 比较。apgdiff 和 migra 工具可将差异直接转换为可执行的 ALTER 语句。添加 --no-owner --no-privileges 可获得确定性输出。

MySQL

# Schema/object comparison with MySQL Utilities (5.x):
mysqldbcompare --server1=root:password@host1 --server2=root:password@host2 \
  shop:shop --difftype=sql --run-all-tests

# Built-in method: take CREATE statements from both environments and diff them
mysqldump --no-data --skip-comments shop > source_schema.sql
# SHOW CREATE TABLE customer;  (to see a single table definition)

MySQL Utilities 中的 mysqldbcompare 工具可报告两台服务器之间的对象/schema 差异（difftype=sql 时输出可执行语句）。不借助工具的方法是从两个环境获取 mysqldump --no-data（仅 schema）或 SHOW CREATE TABLE 输出，再用 diff 比较。mysqldbcompare 现已弃用；对于 MariaDB/8.x，推荐使用 Liquibase 或 Skeema。

SQL Server

-- Schema comparison/deploy report with SqlPackage (what will change before applying):
sqlpackage /Action:DeployReport /SourceFile:source.dacpac \
  /TargetConnectionString:"Server=target;Database=shop;Integrated Security=true" \
  /OutputPath:diff_report.xml

-- For data differences (row level), the tablediff utility:
tablediff -sourceserver S1 -sourcedatabase shop -sourcetable customer \
  -destinationserver S2 -destinationdatabase shop -destinationtable customer

对于 schema 差异，SqlPackage /Action:DeployReport 以 XML 报告返回需应用到目标的变更；/Action:Script 生成可执行的差异脚本。Visual Studio / Azure Data Studio 的“架构比较”使用相同的引擎。行级数据差异可使用 tablediff 命令行工具。

MongoDB

// The schema is implicit; extract and compare the field structure of both environments.
// Read the $jsonSchema validator definition (collection rules):
db.getCollectionInfos({ name: "customer" })[0].options.validator

// External tools for inferred schema comparison by sampling:
// mongodb-schema (npm) or the Compass 'Schema' tab
// Take samples with mongoexport and diff the field/type distribution of both environments.

MongoDB 是无 schema 的；没有固定的 DDL。比较有两种方式：比较集合上定义的 $jsonSchema 验证规则（通过 getCollectionInfos），或用 mongodb-schema / Compass 从数据推断 schema，并比较字段和类型分布。索引方面，对 db.collection.getIndexes() 的输出做 diff。

Redis

# Not applicable: Redis is a schemaless key-value store; there are no
# table/column definitions (DDL) to compare.
# Closest equivalent: compare the key space (namespace) structure
# redis-cli --scan --pattern 'customer:*' | sort > source_keys.txt

不适用：Redis 没有 schema 概念（表/列/DDL），因此传统的 schema diff 不适用。如需结构比较，只能用 SCAN 列出键命名模式（namespace）进行比较。

Elasticsearch

# Fetch the index mapping from both clusters and compare
curl -s 'http://source:9200/customer/_mapping?pretty' > source_mapping.json
curl -s 'http://target:9200/customer/_mapping?pretty'  > target_mapping.json
diff -u source_mapping.json target_mapping.json

# To compare settings as well:
# GET customer/_settings   and   GET customer/_mapping

在 Elasticsearch 中，schema 的等价物是索引 mapping。标准方法是从两个集群获取 GET <index>/_mapping 输出（必要时加上 _settings），再用 diff 比较。由于 mapping 字段大多不可变，发现的差异可能需要新建索引 + _reindex。

在 DBAtlas 中打开 →