在不使用联邦表的情况下跨服务器进行Mysql查询。

I moved some tables of my database that had trouble with traffic to another server and don't wanna use federated tables for alot of reasons (performance is the main reason). So I have to create 2 different connections in my PHP class and re-write my queries in the code that have join between tables from different servers.

我将我的数据库中的一些表移到另一个服务器上，因为很多原因(性能是主要原因)，所以不希望使用联合表。因此，我必须在PHP类中创建两个不同的连接，并在不同服务器之间的表之间有连接的代码中重写查询。

For example, i have two tables: Users and Enterprise that are in different servers.

例如，我有两个表:位于不同服务器的用户和企业。

When it was on the same server, the query was:

当它在同一台服务器上时，查询是:

select name from Enterprise E 
inner join Users U on E.cod = U.cod
where E.cod = $my_code and U.codUsers in ($users);

So i changed to this:

于是我改变了想法:

$rst= select group_concat(U.cod) from Users as U 
where U.codUsers in ($users)

select name from Enterprise E
where E.cod = $mycode and E.cod in ($rst);

My Question is, how can i simillary do this when i have this type of query:

我的问题是，当我有这种类型的查询时，我怎么能明喻呢?

select e.name, e.datebegin, e.dateend from Enterprise E
leftjoin ( select h.callQuantity, h.history from thirdtable 
       inner join Users u on e.cod = u.cod
       where u.codHistory in (1,2,3)
           group by u.cod)

My question is clear? Sorry for my english

我很清楚的问题吗?对不起,我的英语

2 个解决方案

#1

First of all, federated tables are the better solution (they are made for exactly that kind of thing you are trying to do by hand). But you don't want them, so here is the next best thing:

首先，联合表是更好的解决方案(它们正是为您尝试手工完成的那种事情而制作的)。但你不想要它们，所以接下来最好的事情是:

For anything more complicated than your first example, you should simulate a remote table manually by inserting the table content instead of the table.

对于任何比第一个示例更复杂的情况，您应该通过插入表内容而不是表来手动模拟远程表。

I'll rewrite your first example in that manner, cause your 2nd example is messed up and I don't even know what you want to express there exactly.

我用这种方式重写你的第一个例子，因为你的第二个例子搞砸了，我甚至不知道你想要表达什么。

You had the following code when using 1 server:

使用1台服务器时，您有以下代码:

select name from Enterprise E 
inner join Users U on E.cod = U.cod
where E.cod = $my_code and U.codUsers in ($users);

You now can replace the table with the actual data of the table:

您现在可以用该表的实际数据替换该表:

select name from Enterprise E 
inner join 
   (      select 1 as cod, 4 as codUsers, 20 as codHistory
    union select 2 as cod, 8 as codUsers, 500 as codHistory
    union select 3 as cod, 29 as codUsers, 100 as codHistory
   ) as U
on E.cod = U.cod
where E.cod = $my_code and U.codUsers in ($users);

To do that, you have to build the table data as a string (I'm using pdo here):

为此，您必须将表数据构建为字符串(我在这里使用pdo):

foreach($db->query('select * from Users U where U.codUsers in ($users)') as $row) {
    if($data !== '') { $data .= 'union '; }
    $data .= 'select ' . $row['cod'] . ' as cod, ' 
             . $row['codUsers'] . ' as codUsers, ' 
             . $row['codHistory'] . ' as codHistory '; 
}

You have to fit it to the layout of your Users-table of course (and don't forget some ' for string-columns), and you can ommit columns you don't need.

当然，您必须将它与用户表的布局相匹配(不要忘记一些“字符串列”)，并且可以省略不需要的列。

You can now place that string anywhere you had your Users-table before and write your code as if it were on 1 server, so your first code would look like

现在，您可以将该字符串放置在以前拥有用户表的任何位置，并像在一台服务器上一样编写代码，因此您的第一个代码看起来应该是这样的

select name from Enterprise E 
inner join ($data) as U on E.cod = U.cod
where E.cod = $my_code and U.codUsers in ($users);

and your second code (although I remind you it is broken to begin with and wouldn't work on 1 server either) would look like

您的第二个代码(尽管我提醒您，它一开始就被破坏了，而且在一台服务器上也不能工作)应该是这样的

select e.name, e.datebegin, e.dateend from Enterprise E
leftjoin ( select h.callQuantity, h.history from thirdtable 
       inner join ($data) as u on e.cod = u.cod
       where u.codHistory in (1,2,3)
           group by u.cod)

You should make sure to just have a small number of users into your $data-string, then this will work fine. Otherwise, you really need federated tables.

您应该确保在$data-string中只包含少量用户，这样就可以正常工作了。否则，您确实需要联合表。

#2

Plan A:

计划:

Fetch the data that might be needed from one machine; put the resultset into an associative array.

从一台机器上获取可能需要的数据;将resultset放入关联数组中。

Do likewise for the second machine. (And third ...)

对第二台机器也是如此。(和第三…)

Play with the two arrays.

使用这两个数组。

Plan B (similar to your first case):

方案B(类似于您的第一个案例):

Fetch the data that might be needed from one machine; put the resultset into an associative array.

从一台机器上获取可能需要的数据;将resultset放入关联数组中。

Build an IN (...); add it to the query for the second machine.

建立一个(…);将它添加到第二个机器的查询中。

Fetch from second machine.

获取从第二台机器。

(etc)

(等)

Play with resultsets.

玩结果集。

Plan B can be 'optimized' if you know which machine will have the smallest resultset, and starting with it.

如果您知道哪台机器将拥有最小的resultset，并从它开始，那么Plan B可以“优化”。

PHP has a wealth of array functions, making many of the manipulations easy.

PHP具有丰富的数组函数，使许多操作变得容易。