跨MySQL数据库插入数据问题：不同Schema同结构表插入失败求助

我来帮你捋捋这个跨库插入的问题～你现在是要把本地库中table2的部分字段数据插入到远程eno*****.com服务器上asdf_stage Schema下的temp表，两个表结构一致，但用了类似INSERT INTO 'eno*****.com'.asdf_stage.temp SELECT ...的语法没生效，对吧？

下面给你几个具体的排查方向和解决方案：

• 先确认数据库类型的跨库引用规则
  不同数据库的跨库/跨实例访问语法差异很大，直接用域名加Schema加表名的写法几乎不会被支持：

  • 如果是MySQL，需要先配置FEDERATED引擎映射远程表，或者使用导出再导入的方式；
  • 如果是SQL Server，得先创建链接服务器（Linked Server），然后用[远程服务器名].[数据库名].[Schema].[表名]的格式引用；
  • 如果是PostgreSQL，要借助dblink扩展来实现远程数据交互；
  • 像Oracle的话，需要创建数据库链接（DB Link），用表名@远程链接名来访问。

• 检查权限与连接配置
  执行语句的数据库账号必须同时满足：

  1. 拥有本地原表的SELECT权限；
  2. 拥有远程库asdf_stage.temp表的INSERT权限；
  3. 本地服务器能正常访问远程eno*****.com的数据库端口（比如3306、1433这些，要确保防火墙没拦截）。

• 修正语句的语法细节
  你的SELECT语句里有未完成的CASE分支，必须补全END才能正常执行，而且要确保SELECT出来的字段顺序、数据类型和远程temp表完全匹配，哪怕表结构一致，别名也要对应正确。举个补全后的示例：

  -- 以PostgreSQL使用dblink为例，先确保已安装dblink扩展
  INSERT INTO asdf_stage.temp (id, description, assigned_group)
  SELECT 
    i.artf_id as id, 
    title as description, 
    CASE 
      WHEN i.assigned_group = 'asdfk' THEN 'dfg' 
      WHEN i.assigned_group = 'other_group' THEN 'other_val'
      ELSE 'default_val' -- 一定要有默认分支或者覆盖所有情况
    END as assigned_group
  FROM localTABLE i;
  

• 先测试远程查询是否正常
  别直接上来就插数据，先写个简单的远程查询语句测试连通性，比如如果是用dblink的PostgreSQL：

  SELECT * FROM dblink('host=eno*****.com dbname=your_db user=your_user password=your_pwd', 'SELECT * FROM asdf_stage.temp LIMIT 1')
  AS temp(id INT, description VARCHAR(255), assigned_group VARCHAR(50));
  

  如果这个查询能返回数据，说明连接和权限没问题，再调试插入语句；如果查询失败，先解决远程连接的问题。

你可以按照上面的步骤一步步排查，先把CASE语句补全，再确认对应数据库的跨库访问配置，应该就能解决问题啦～

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