跨MySQL服务器调用数据：在TranscationDB中创建存储过程访问LookupDB

嘿，要在TranscationDB的存储过程里从另一台服务器的LookupDB取数据，MySQL本身没有直接的跨库远程调用语法，不过我们可以借助FEDERATED存储引擎来实现——它相当于在本地创建一个“远程表的映射”，让你能像操作本地表一样访问远程数据，然后在存储过程里用这个映射表就行。

下面是详细的实现步骤：

1. 确认两台MySQL服务器都开启了FEDERATED引擎 #

先在两台服务器上执行这条命令检查引擎状态：

SHOW ENGINES;

如果FEDERATED对应的Support列是YES就没问题；要是显示NO，需要修改MySQL配置文件（my.cnf或my.ini），在[mysqld]段添加一行federated，然后重启MySQL服务。

2. 在TranscationDB中创建远程表的映射 #

在Server2的TranscationDB里，创建一个和Server1上LookupDB目标表结构完全一致的FEDERATED表。举个例子，假设你要访问LookupDB里的user_lookup表，结构是user_id INT PRIMARY KEY, user_name VARCHAR(50)，创建语句如下：

CREATE TABLE user_lookup_fed (
    user_id INT PRIMARY KEY,
    user_name VARCHAR(50)
)
ENGINE=FEDERATED
DEFAULT CHARSET=utf8mb4
CONNECTION='mysql://远程账号:密码@Server1的IP:端口/LookupDB/user_lookup';

这里要注意：

• 远程账号必须拥有LookupDB对应表的SELECT权限，且允许从Server2的IP地址连接（可以在Server1上执行GRANT SELECT ON LookupDB.user_lookup TO '账号'@'Server2的IP' IDENTIFIED BY '密码';完成授权）。
• 这个映射表只是一个“指针”，本地不会存储任何数据，所有操作都会转发到远程的LookupDB表上。

3. 编写存储过程使用映射表 #

现在就可以在TranscationDB里编写存储过程，直接操作这个映射表来获取远程数据了。比如写一个根据user_id获取用户名的存储过程：

DELIMITER //
CREATE PROCEDURE FetchLookupUserName(IN input_user_id INT, OUT output_user_name VARCHAR(50))
BEGIN
    -- 从映射表查询远程数据，用法和本地表完全一致
    SELECT user_name INTO output_user_name
    FROM user_lookup_fed
    WHERE user_id = input_user_id;
END //
DELIMITER ;

如果需要批量获取数据，或者将远程数据导入本地表，也可以这样写：

DELIMITER //
CREATE PROCEDURE BatchFetchLookupData()
BEGIN
    -- 创建临时表存储远程数据
    CREATE TEMPORARY TABLE temp_lookup_data LIKE user_lookup_fed;
    
    -- 批量导入远程LookupDB的数据到本地临时表
    INSERT INTO temp_lookup_data
    SELECT * FROM user_lookup_fed WHERE user_id > 100;
    
    -- 这里可以对临时表做后续处理，比如关联本地业务表等
    -- ...
END //
DELIMITER ;

4. 测试存储过程 #

执行以下命令验证存储过程是否正常工作：

SET @username = '';
CALL FetchLookupUserName(1, @username);
SELECT @username;

一些关键注意事项 #

• 性能影响：FEDERATED的效率依赖网络延迟，频繁的小查询可能会拖慢速度，建议尽量采用批量操作减少远程交互。
• 事务限制：FEDERATED表不支持事务，所以存储过程中涉及事务的逻辑要避开对映射表的操作，或者做好异常处理。
• 结构一致性：如果远程LookupDB的目标表结构修改了，本地的映射表也要同步更新，否则会出现查询报错。

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