MySQL 连接来自两个不同服务器的表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11114197/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Join tables from two different server
提问by Gulrej
I have two different server server1and server2, now I have db1in server1and db2in server2.
I am trying to join these two table in MySQL like this.
我有两个不同的服务器server1和server2，现在我已经db1在server1和db2中server2。我正在尝试像这样在 MySQL 中加入这两个表。
Select a.field1,b.field2
FROM [server1, 3306].[db1].table1 a
Inner Join [server2, 3312].[db2].table2 b
ON a.field1=b.field2
But I am getting error. Is is possible in MYSQL.
但我收到错误。在 MYSQL 中是可能的。
回答by Starx
Yes, it is possible in MySQL.
是的，在 MySQL 中是可能的。
There are similar questions asked previouslytoo. You have to use FEDERATED ENGINEto do this. The idea goes like this:
之前也有人问过类似的问题。您必须使用FEDERATED ENGINE来执行此操作。这个想法是这样的：
You have to have a federated table based on the table at another remote location to use the way you want. The structure of the table have to exactly same.
您必须有一个基于另一个远程位置的表的联合表才能按照您想要的方式使用。表的结构必须完全相同。
CREATE TABLE federated_table (
id INT(20) NOT NULL AUTO_INCREMENT,
name VARCHAR(32) NOT NULL DEFAULT '',
other INT(20) NOT NULL DEFAULT '0',
PRIMARY KEY (id),
INDEX name (name),
INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table';
回答by naveen_sfx
Replication will be alternate and suitable solution.
复制将是替代和合适的解决方案。
server1 - db1 -> replicate to server2. (now db1 and db2 will be in same server server2. join will be easy).
server1 - db1 -> 复制到 server2。（现在 db1 和 db2 将在同一个服务器 server2 中。加入会很容易）。
NOTE: If the server2 is enough capable of take the load of db1 in terms of store/process etc., then wen can do the replication. As @brilliand mentioned yes Federated will make the much manual work and slow in process.
注意：如果 server2 在存储/进程等方面足以承受 db1 的负载，那么 wen 可以进行复制。正如@brilliand 提到的，是的 Federated 将进行大量的手动工作并且过程缓慢。
回答by MatrixManAtYrService
It's kind of a hack, and it's not a join, but I use bash functions to make it feel like I'm doing cross-server queries:
这有点像黑客，它不是连接，但我使用 bash 函数来让它感觉就像我在做跨服务器查询：
The explicit version:
显式版本：
tb2lst(){
echo -n "("
tail -n +2 - | paste -sd, | tr -d "\n"
echo ")"
}
id_list=$(mysql -h'db_a.hostname' -ume -p'ass' -e "SELECT id FROM foo;" | tb2lst)
mysql -h'db_b.hostname' -ume -p'ass' -e "SELECT * FROM bar WHERE foo_id IN $id_list"
+--------|-----+
| foo_id | val |
+--------|-----+
| 1 | 3 |
| 2 | 4 |
+--------|-----+
I wrote some wrapper functions which I keep in my bashrc, so my perspective it's just one command:
我写了一些包装函数，保存在我的 bashrc 中，所以我认为它只是一个命令：
db_b "SELECT * FROM bar WHERE foo_id IN $(db_a "SELECT id FROM foo;" | tb2lst);"
+--------|-----+
| foo_id | val |
+--------|-----+
| 1 | 3 |
| 2 | 4 |
+--------|-----+
At least for my use case, this stitches the two queries together quickly enough that the output is equivalent to the join, and then I can pipe the output into whatever tool needs it.
至少对于我的用例来说，这将两个查询足够快地拼接在一起，使得输出等效于连接，然后我可以将输出通过管道传输到任何需要它的工具中。
