跨数据库连接同结构GPS表数据插入可行性咨询

嗨，直接用你写的INSERT INTO host-abc1.de_NameOfSchema1_GPS SELECT * FROM host-abc2.de_NameOfSchema2_GPS这种语句通常是不能直接执行的——因为大部分数据库系统默认不识别这种跨主机的表命名格式，得先配置好远程数据访问的机制才行。下面分几种常见数据库给你说具体怎么做：

1. MySQL 环境 #

如果用的是MySQL，你可以借助FEDERATED存储引擎把远程表映射成本地表，之后就能用熟悉的INSERT...SELECT语法了：

• 首先在host-abc1的数据库里创建一个FEDERATED表，指向host-abc2的GPS表：

CREATE TABLE NameOfSchema1_GPS_federated
ENGINE=FEDERATED
DEFAULT CHARSET=utf8mb4
CONNECTION='mysql://用户名:密码@host-abc2.de/数据库名/NameOfSchema2_GPS';

• 然后执行插入操作：

INSERT INTO NameOfSchema1_GPS SELECT * FROM NameOfSchema1_GPS_federated;

注意：FEDERATED引擎需要在MySQL配置中开启（默认可能没开），且只支持MySQL之间的远程访问。

2. PostgreSQL 环境 #

PostgreSQL可以用postgres_fdw外部数据包装器来实现跨主机访问：

• 先在host-abc1的数据库中启用postgres_fdw扩展：

CREATE EXTENSION postgres_fdw;

• 创建指向host-abc2的远程服务器：

CREATE SERVER host_abc2_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'host-abc2.de', port '5432', dbname '你的数据库名');

• 建立用户映射（用能访问host-abc2数据库的账号）：

CREATE USER MAPPING FOR 本地用户名
SERVER host_abc2_server
OPTIONS (user '远程用户名', password '远程密码');

• 导入远程库的Schema或者直接创建外部表：

IMPORT FOREIGN SCHEMA NameOfSchema2
FROM SERVER host_abc2_server
INTO NameOfSchema1;

• 最后执行插入：

INSERT INTO NameOfSchema1.GPS SELECT * FROM NameOfSchema1.GPS;

（这里的NameOfSchema1.GPS就是映射过来的远程表，你也可以自定义外部表名称）

3. SQL Server 环境 #

SQL Server可以通过**链接服务器（Linked Server）**来实现跨主机查询：

• 先创建链接服务器指向host-abc2.de（可以用SSMS图形界面或者T-SQL命令）：

EXEC sp_addlinkedserver
   @server='host-abc2.de',
   @srvproduct='SQL Server';

• 配置链接服务器的登录映射：

EXEC sp_addlinkedsrvlogin
   @rmtsrvname='host-abc2.de',
   @useself='FALSE',
   @rmtuser='远程用户名',
   @rmtpassword='远程密码';

• 之后就可以用四部分命名来执行插入了（和你最初的写法有点像，但格式更规范）：

INSERT INTO [host-abc1.de].[NameOfSchema1].[GPS]
SELECT * FROM [host-abc2.de].[NameOfSchema2].[GPS];

关键注意事项 #

• 表结构一致性：两个GPS表的列数、数据类型、列顺序必须完全匹配，不然INSERT会报错。如果不想依赖列顺序，最好显式指定列名，比如INSERT INTO NameOfSchema1.GPS (列1, 列2, ...) SELECT 列1, 列2, ... FROM 远程表。
• 权限问题：执行语句的数据库账号需要同时拥有host-abc1表的INSERT权限和host-abc2表的SELECT权限。
• 网络与性能：确保两个主机之间网络连通，数据库端口开放；如果数据量很大，建议分批插入，避免长时间锁表或超时。

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