在实际业务开发中，企业往往会在不同时期选用不同的数据库产品，比如用户模块用MySQL，订单模块用Oracle，日志模块用PostgreSQL，当需要跨这些数据库做关联查询或者数据同步时，就需要用到SQL的跨数据库操作方案。下面我们先看一张常见的跨数据库操作方案对比表，方便大家快速了解不同方案的特点。
| 方案类型 | 适用场景 | 优点 | 缺点 |
|---|---|---|---|
| 数据库原生跨库链接 | 同类型或兼容的数据库跨库 | 配置简单，查询性能较好 | 异构数据库支持有限 |
| 联邦数据库 | 多异构数据源整合 | 统一SQL入口，屏蔽底层差异 | 配置复杂，部分高级特性支持不全 |
| 数据同步中间件 | 离线数据分析、数据仓库场景 | 解耦业务数据库，不影响源库性能 | 数据存在延迟，不适合实时查询 |
一、数据库原生跨库链接实现
很多主流数据库自身就提供了跨库链接的能力，比如MySQL的FEDERATED引擎、Oracle的DB Link、PostgreSQL的postgres_fdw扩展，这种方式适合同类型或者兼容性较好的数据库之间的跨库操作。
1. MySQL通过FEDERATED引擎跨库
MySQL的FEDERATED引擎可以创建指向远程MySQL表的本地映射表，之后就可以像操作本地表一样操作远程表。首先需要在本地MySQL开启FEDERATED引擎，然后创建映射表：
-- 检查FEDERATED引擎是否开启，若Support为YES则已开启
SHOW ENGINES;
-- 创建指向远程MySQL表的本地映射表
CREATE TABLE remote_user (
id INT(11) NOT NULL AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
age INT(11),
PRIMARY KEY (id)
) ENGINE=FEDERATED
CONNECTION='mysql://remote_user:remote_password@192.168.0.1:3306/remote_db/user';
-- 直接查询本地映射表，实际会查询远程数据库的用户表
SELECT * FROM remote_user WHERE age > 18;2. Oracle通过DB Link跨库
Oracle的DB Link可以链接到远程的Oracle或者其他支持ODBC的数据库，创建之后可以直接在SQL中通过远程表名@DB_LINK名的方式访问远程数据：
-- 创建指向远程Oracle数据库的DB Link CREATE DATABASE LINK remote_oracle_link CONNECT TO remote_user IDENTIFIED BY remote_password USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.2)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))'; -- 跨库查询远程订单表 SELECT o.order_id, o.amount, u.username FROM local_user u JOIN remote_order@remote_oracle_link o ON u.id = o.user_id WHERE o.amount > 100;
二、联邦数据库实现异构数据源整合
当需要处理完全异构的数据库，比如同时关联MySQL、Oracle、PostgreSQL的数据时，原生跨库链接就不够用了，这时候可以使用联邦数据库方案。联邦数据库是在本地创建一个虚拟的数据库层，把不同的远程数据源注册为外部数据源，之后所有的查询都通过本地联邦数据库的SQL入口执行，底层会自动把请求路由到对应的数据源处理。
以PostgreSQL的postgres_fdw结合其他fdw扩展为例，我们可以同时挂载MySQL和Oracle作为外部数据源：
-- 先安装mysql_fdw和oracle_fdw扩展（需要提前编译对应扩展） CREATE EXTENSION postgres_fdw; CREATE EXTENSION mysql_fdw; CREATE EXTENSION oracle_fdw; -- 创建指向MySQL的外部服务器 CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host '192.168.0.1', port '3306', dbname 'user_db'); -- 创建访问MySQL的用户映射 CREATE USER MAPPING FOR local_user SERVER mysql_server OPTIONS (username 'mysql_user', password 'mysql_password'); -- 把MySQL的用户表导入为本地外部表 IMPORT FOREIGN SCHEMA user_schema FROM SERVER mysql_server INTO local_mysql_schema; -- 创建指向Oracle的外部服务器 CREATE SERVER oracle_server FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//192.168.0.2:1521/orcl'); -- 创建访问Oracle的用户映射 CREATE USER MAPPING FOR local_user SERVER oracle_server OPTIONS (user 'oracle_user', password 'oracle_password'); -- 导入Oracle的订单表为本地外部表 IMPORT FOREIGN SCHEMA order_schema FROM SERVER oracle_server INTO local_oracle_schema; -- 直接做跨异构库的关联查询 SELECT m.username, o.order_id, o.amount FROM local_mysql_schema.user m JOIN local_oracle_schema.order o ON m.id = o.user_id WHERE o.amount > 200;
三、数据同步中间件方案
如果跨库操作不需要实时查询，只是需要把多个数据源的数据汇总起来做分析，那么使用数据同步中间件是更稳妥的方案。常见的同步中间件有DataX、Canal、Debezium等，它们可以定时或者实时把不同数据库的数据同步到统一的存储中，比如同步到ClickHouse或者数据仓库里，之后再对这些汇总数据做SQL查询。
以DataX为例，我们可以配置一个任务把MySQL的用户数据和Oracle的订单数据同步到本地的PostgreSQL分析库中：
{
"job": {
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"username": "mysql_user",
"password": "mysql_password",
"connection": [
{
"querySql": ["SELECT id,username,age FROM user WHERE update_time > '2024-01-01'"],
"jdbcUrl": ["jdbc:mysql://192.168.0.1:3306/user_db"]
}
]
}
},
"writer": {
"name": "postgresqlwriter",
"parameter": {
"username": "pg_user",
"password": "pg_password",
"column": ["id","username","age"],
"connection": [
{
"jdbcUrl": "jdbc:postgresql://127.0.0.1:5432/analysis_db",
"table": ["user"]
}
]
}
}
},
{
"reader": {
"name": "oraclereader",
"parameter": {
"username": "oracle_user",
"password": "oracle_password",
"connection": [
{
"querySql": ["SELECT order_id,user_id,amount FROM order_table WHERE update_time > '2024-01-01'"],
"jdbcUrl": ["jdbc:oracle:thin:@192.168.0.2:1521:orcl"]
}
]
}
},
"writer": {
"name": "postgresqlwriter",
"parameter": {
"username": "pg_user",
"password": "pg_password",
"column": ["order_id","user_id","amount"],
"connection": [
{
"jdbcUrl": "jdbc:postgresql://127.0.0.1:5432/analysis_db",
"table": ["order_table"]
}
]
}
}
}
]
}
}四、方案选择建议
在实际项目中选择跨数据库操作方案时，可以根据实际需求判断：如果是同类型数据库的少量实时跨库查询，优先选择数据库原生跨库链接，成本最低；如果是多异构数据源的实时查询整合，选联邦数据库方案，统一SQL入口更方便；如果是离线分析、数据仓库场景，数据同步中间件是更好的选择，不会影响源库的业务性能。另外需要注意，跨库操作要提前做好权限管控，避免越权访问远程数据，同时跨库查询的性能往往比本地查询低，要结合业务场景做必要的索引优化。