如何通过单条查询同时从MySQL和PostgreSQL(psql)跨库关联查询数据

有三种常用的实现方式，可根据你的权限、场景灵活选择：

方案1：PostgreSQL侧通过mysql_fdw扩展实现（推荐） #

直接在PostgreSQL中映射MySQL的表，无需额外工具，可直接执行你需要的跨库关联查询：

操作步骤 #

• 安装mysql_fdw扩展
  根据你使用的操作系统安装对应依赖包，以Debian/Ubuntu为例：
  apt install postgresql-<你的PG主版本号>-mysql-fdw
  安装完成后进入PostgreSQL命令行执行：

  CREATE EXTENSION mysql_fdw;
  

• 创建MySQL服务器映射

  CREATE SERVER mysql_server
  FOREIGN DATA WRAPPER mysql_fdw
  OPTIONS (host 'MySQL实例IP/域名', port '3306', dbname 'MySQL库名');
  

• 创建用户映射，关联MySQL的访问账号

  CREATE USER MAPPING FOR <PG登录用户名>
  SERVER mysql_server
  OPTIONS (username 'MySQL登录账号', password 'MySQL登录密码');
  

• 映射MySQL的两张业务表为PostgreSQL外部表

  -- 映射Users表
  CREATE FOREIGN TABLE mysql_users (
      Id INT,
      FirstName VARCHAR(255),
      LastName VARCHAR(255),
      email VARCHAR(255),
      LocationId INT
  ) SERVER mysql_server OPTIONS (table_name 'Users');
  
  -- 映射Locations表
  CREATE FOREIGN TABLE mysql_locations (
      Id INT,
      City VARCHAR(255),
      Address VARCHAR(255),
      Zip VARCHAR(20)
  ) SERVER mysql_server OPTIONS (table_name 'Locations');
  

执行查询并导出 #

注意：你示例SQL中的关联条件有误，地址表和用户表的关联字段应为loc.Id = u.LocationId
执行关联查询并导出为CSV文件：

COPY (
    SELECT u.FirstName, u.LastName, o.Quantity, loc.City
    FROM mysql_users u
    JOIN mysql_locations loc ON loc.Id = u.LocationId
    JOIN "Orders" o ON o.UserId = u.Id
) TO '/本地导出路径/result.csv' WITH (FORMAT csv, HEADER, ENCODING 'UTF8');

方案2：无需修改数据库配置，用Python脚本实现 #

如果没有修改数据库配置的权限，可以写简单脚本拉取两个库的数据合并后导出：

import pandas as pd
import pymysql
import psycopg2

# 连接MySQL拉取用户+地址关联数据
mysql_conn = pymysql.connect(
    host="MySQL地址", user="账号", password="密码", database="库名", charset="utf8"
)
user_loc_df = pd.read_sql(
    "SELECT u.Id, u.FirstName, u.LastName, l.City FROM Users u JOIN Locations l ON u.LocationId = l.Id",
    con=mysql_conn
)
mysql_conn.close()

# 连接PostgreSQL拉取订单数据
pg_conn = psycopg2.connect(
    host="PG地址", user="账号", password="密码", database="库名"
)
order_df = pd.read_sql(
    "SELECT UserId, Quantity FROM Orders",
    con=pg_conn
)
pg_conn.close()

# 合并数据并导出
merge_result = pd.merge(user_loc_df, order_df, left_on="Id", right_on="UserId")
merge_result.to_csv("/本地导出路径/result.csv", index=False, encoding="utf-8")

方案3：MySQL侧通过FEDERATED引擎实现 #

和方案1逻辑相反，在MySQL中开启FEDERATED存储引擎，映射PostgreSQL的Orders表到MySQL中，即可在MySQL侧执行跨库关联查询，该方案配置复杂度较高，性能略低于方案1，适合主要操作入口在MySQL的场景。

注意事项 #

• 跨库关联的字段需提前创建索引，避免大数据量下查询过慢
• 需保证两个数据库实例之间网络互通，访问账号有对应表的读权限

内容的提问来源于stack exchange，提问作者Mike Green